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.
OR using wget to download the my_db.db database to your google drive ( check code below )

Python SQLite database connection and managing by using colab platform and mounting drive

Export or Import data from Excel to create SQLite database table using different libraries
Displaying binary data ( Images ) taken from SQLite database in Colab platform

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 )

Download database from plus2net using wget

The database will be available for the current session ( Colab Runtime ) only.
wget command to download sqlite database to google drive

!wget https://www.plus2net.com/python/download/my_db.db

Database Connection using SQL Magic Extension

Google colab platform to connect to SQLite database using SQL magic extension

%load_ext sql
# we can use the magic extension to connect to our SQLite DB
%sql sqlite:///my_db.db

# use %sql to write an inline SQL command
# use %%sql to write SQL commands in a cell
%%sql
SELECT name FROM sqlite_master WHERE type='table' ;

Output

Done.
name
category
subcategory
student

Structure of the student table

%sql PRAGMA table_info([student]);

Output

 * sqlite:///my_db.db
Done.
cid	name	type	notnull	dflt_value	pk
0	id	INTEGER	0	None	1
1	name	TEXT	0	None	0
2	class	TEXT	0	None	0
3	mark	INTEGER	0	None	0
4	gender	TEXT	0	None	0

%%sql
SELECT * FROM student LIMIT 0,5

Output

 * sqlite:///my_db.db
Done.
id	name	class	mark	gender
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female

result = %sql SELECT * FROM student LIMIT 0,5;
#result.DataFrame().loc[0, 'name'] # first name only 
result.DataFrame().loc[:,'name']   # all names

Connecting using SQLAlchemy

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")

or by giving same path

my_conn=create_engine("sqlite:////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)

Connecting using sqlite3

Database connection by using sqlite3 in Colab

# download my_db.db SQLite Database from plus2net.com
!wget https://www.plus2net.com/python/download/my_db.db

Connect to my_db.db database using sqlite3 and get 5 records from the student table. Set of 5 rows of data is displayed as list.

import sqlite3
my_conn = sqlite3.connect('my_db.db') # connect to db
query="SELECT *  FROM student LIMIT 0,5" # sql 
my_data=list(my_conn.execute(query)) # rows of data as list 
print(my_data)

Displaying Image from SQLite database in Google colab
From SQLite database table to Excel page and vice versa in Google colab platform

Creating and adding data ( with image ) code is available at Managing SQLite Blob column data.
Download .zip file with my_db.db, and .ipynb files

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

Download .zip file with .ipynb file to run above codes
( Upload to google colab )

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: