Connection to SQlite by using Colab

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.

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

Mount Google drive

Use this code in your Jupyter Notebook in Colab
from google.colab import drive
drive.mount('/content/drive')
Authorization for mounting the drive
After authorization copy the path of SQLite database file ( my_db.db )
Path of SQLite database file

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

Displaying binary data ( images ) in Google Colab

We can collect binary data stored in Blob column from SQLite database. To display such data we have to use io ( Core tools for working with streams) and PIL ( Python Image Library ).
from PIL import Image
import io 
Our binary data is available in r_set[5]
image = Image.open(io.BytesIO(r_set[5]))
import matplotlib.pyplot as plt
plt.imshow(image)
plt.show()
Full code to manage SQLite Blob column data in google platform is here
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn=create_engine("sqlite:////content/my_db.db"+'?check_same_thread=False')
from PIL import Image
import io 
my_data=(1) # ID of the row to display 
q="SELECT * FROM  student WHERE id=?" # query with place holders
try:
  my_cursor=my_conn.execute(q,my_data)
  r_set=my_cursor.fetchone()
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  student=str(r_set[0])+','+r_set[1]+','+r_set[2]+','+str(r_set[3])
  #print(student)
  image = Image.open(io.BytesIO(r_set[5]))
  import matplotlib.pyplot as plt
  plt.imshow(image)
  plt.show()
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

insert
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer