Python sqlite Blob data type storing updating deleting and displaying images

We can store binary data using Blob column of SQLite database. We are using our student table.
How to create database and student table

Create table with Blob column

Along with other data types we are using one column photo to store picture ( binary data ).

from sqlalchemy import create_engine
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text, 
                      photo blob
                      );''')
print("Student Table created successfully");

Adding record with Blob data

We have stored our image in one location and by using file read we can read the binary data ( image ).
rb : Read Binary

fob=open('G:\\My Drive\\testing\\my_db\\sqlite-blob1.jpg','rb')
blob_data=fob.read()

Along with other data we are storing binary data in our student table. We used try except error handling to display error messages.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

fob=open('G:\\My Drive\\testing\\my_db\\sqlite-blob1.jpg','rb') #file object 
blob_data=fob.read() # Binary data is ready 

my_data=[(None, 'Tes Qry', 'Six', 78, 'male',blob_data)] # Data to store
q="INSERT INTO student values(?,?,?,?,?,?)" # query with place holders 
try:
  r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
  error=str(e.__dict__['orig'])
  print(error)
else:
  print("Number of records added  : ",r_set.rowcount)

Creating ( or displaying ) Blob data from table

By using fetchone() we collected one row of data from the table.
In last two lines we created the file object and stored the binary data from the student table inside the given path.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
q='''SELECT * FROM student WHERE id=37'''
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()

fob=open('G:\\My Drive\\testing\\my_db\\sqlite-blob2.jpg','wb')
fob.write(data_row[5]) # create the image using above path

While storing ( inserting to table ) the image in SQLite database we used file name sqlite-blob1.jpg. At the same location another file sqlite-blob2.jpg will be created to store the same. Change the file location if it is required to store the data in different location.

Displaying output image in Python console

After retrieving the binary data ( Blob column data ) we can display the same by using io ( Core tools for working with streams) and PIL ( Python Image Library ).

from PIL import Image
import io

In above code we use the data_row[5] which holds the binary data ( photo ).

image = Image.open(io.BytesIO(data_row[5]))
image.show()

Using matplotlib we can display the image in Google Colab console.
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()

Updating Blob Data

By using another image file sqltie-blob3.jpg we will create the file object and update the same in table using the update query.

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

fob=open('G:\\My Drive\\testing\\my_db\\sqlite-blob3.jpg','rb')
blob_data=fob.read()

my_data=(blob_data,37) # tuple to pass values to execute method
q="UPDATE student SET photo=? WHERE id=? "
try:
    r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
    error=str(e.__dict__['orig'])
    print(error)
else:
    print("Number  of records Updated : ",r_set.rowcount)

Deleting Blob Data

We can delete the row containing the Blob data.
We can only remove the Blob column data by updating to empty string without removing the row.

Full code from above ( Updating Blob data ) can be used with this.

my_data=(37) # tuple to pass values to execute method
q="UPDATE student SET photo='' WHERE id=? "

We can update the column to Null value.

my_data=(37) # tuple to pass values to execute method
q="UPDATE student set photo=NULL WHERE id=? "

Managing SQLite Blob data using Tkinter

Tkinter window provides user interface to upload or display the images. We can use filedialog() to browse local file system and select the file to store inside SQLite database. Similarly the user selected row can be collected from the database and image can be displayed inside the Tkinter window.
Tkinter to Manage Blob data

Copy this code from Github and run in your Google colab platform to manage SQLite database with blob column
https://github.com/plus2net/Python-basics/blob/main/SQLite_blob.ipynb

Example 2 : Create SQLite database with Blob column and then download

In place of using any existing Database ( above code ) we will create one database by using sample images with text and then download the same for our further use.

We are using four sample images in .png format.

Copy this code from Github and run in your Google colab platform to Create and download the SQLite database with blob column storing image data
https://github.com/plus2net/Python-basics/blob/main/SQLite_blob_2_student_image.ipynb

Creating SQLite database table in Colab platform with data and image to create PDF ID cards

How the above script works ?

Creating an SQLite Database and Storing Images in a BLOB Column by using Colab platform

We will demonstrates how to generate an SQLite database, store text data, and store images in a BLOB column. The example will guide you through downloading images, creating the database, and displaying the stored images.

1. Download Images

The code begins by downloading four images from a specified URL. These images will be stored in the SQLite database.

!wget https://www.plus2net.com/python/download/1.png
!wget https://www.plus2net.com/python/download/2.png
!wget https://www.plus2net.com/python/download/3.png
!wget https://www.plus2net.com/python/download/4.png

2. Import Required Libraries and Connect to Database

The necessary libraries are imported, and a connection to the SQLite database is established. If the database does not exist, it will be created.

import sqlite3 # Connection library
my_conn = sqlite3.connect('student_blob_2.db') # connect to db

3. Delete Existing Table (Optional)

If required, the existing student_b table is deleted to ensure a fresh start.

try:
  my_conn.execute('DROP table student_b')  # Delete the table if required.
except sqlite3.Error as my_error:
    print("error: ", my_error)
else:
  print("Student Table Deleted")

4. Create the Table

A new table named student_b is created with columns for id, name, class, gender, and photo (BLOB to store images).

try:
  my_conn.execute('''
  CREATE TABLE IF NOT EXISTS student_b(id integer primary key,
                      name text,
                      class text,
                      gender text,
                      photo blob
                      );''')
except sqlite3.Error as my_error:
    print("error: ", my_error)
else:
  print("Student Table created successfully")

5. Import Required Libraries for Image Handling

Additional libraries are imported to handle image operations and display images.

from PIL import Image
import io
import matplotlib.pyplot as plt

6. Check Table Structure

The structure of the student_b table is checked to confirm the schema.

query = "pragma table_info([student_b])"
my_data = list(my_conn.execute(query)) # structure
print(my_data) # Listing structure of table

7. Define Student Data

A list of student details, including image file names, is defined. Each student entry includes an id, name, class, gender, and image filename.

my_students = [[1, 'Alex J', 'Four', 'Female', '1.png'],
               [2, 'Ramana K', 'Three', 'Male', '2.png'],
               [3, 'Jack', 'Five', 'Female', '3.png'],
               [4, 'Ronne', 'Six', 'Female', '4.png']]

8. Insert Student Data into the Table

Each student’s data is inserted into the student_b table. The images are read in binary mode and stored as BLOBs.

for ids in my_students:
  fob = open(ids[4], 'rb') # file object
  blob_data = fob.read() # Binary data is ready

  my_data = (None, ids[1], ids[2], ids[3], blob_data) # Data to store
  q = "INSERT INTO student_b values(?,?,?,?,?)" # query with place holders
  try:
    r_set = my_conn.execute(q, my_data)
  except sqlite3.Error as my_error:
    print("error: ", my_error)
  else:
    print("Number of records added  : ", r_set.rowcount)

9. Display Stored Records

The records stored in the student_b table are retrieved and displayed. The images are converted back from BLOBs to image objects and displayed using matplotlib.

r_set = my_conn.execute('SELECT * from student_b ')
for row in r_set:
  image = Image.open(io.BytesIO(row[4]))
  plt.figure(figsize=(1, 1))
  plt.axis(False)
  plt.imshow(image)
  print(row[0], row[1], row[2], row[3], '')
  plt.show()
  print()

10. Download the Database File

The SQLite database file is downloaded to your local system.

from google.colab import files
files.download('student_blob_2.db')

Conclusion

This code demonstrates how to create an SQLite database, define a table schema, and insert data, including images stored as BLOBs. The ability to store images in a database can be particularly useful for applications such as generating ID cards, storing user profiles, and more. By following above examples, you can extend the functionality to include more students or other entities, making your database more robust and versatile.
SQLite insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite Blob Data