sqlite database using Colab by mounting drive and connection

As SQLite is a file based database so we can place one sample database in google drive and then mount the drive to our colab platform. We can connect to SQLite using colab pre installed libraries and use all database management functions.

No need to download or install any software to work with SQLite.

We can create database and then use it from the Colab platform but for easy testing, this sample SQLite database ( my_db.db ) file can be downloaded and placed in your google drive.

Download .zip file with my_db.db, and .ipynb files
Upload this sqlite database (file) to your google drive.

Video Tutorial on SQLite Colab

Mount Google drive

Use this code in your Jupyter Notebook in Colab

from google.colab import drive
drive.mount('/content/drive')

After authorization copy the path of SQLite database file ( my_db.db )

Connecting using SQLalchem

Use the copied path in below code to connect.

from sqlalchemy import create_engine
my_conn=create_engine("sqlite:////content/drive/MyDrive/my_db/my_db.db")

Database connection is ready, we will check by using this code to list the tables available in our sample database.

r_set=my_conn.execute('''select name from sqlite_master 
		where type = 'table' ''')
for row in r_set:
    print(row)

Output is here ( with sample tables )

('category',)
('subcategory',)
('student',)

Or to get all the records from the student ( sample ) table.

r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
    print(row)

Download .zip file with my_db.db, and .ipynb files

Code to create table , add records,
delete records & drop table for sample student table
insert

Numpy Pandas Python & MySQL Python- Tutorials

Connection to SQlite by using Colab

Video Tutorial on SQLite Colab

Mount Google drive

Connecting using SQLalchem