SQLite Blob Data

SQLite Blob data type

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=? "


SQLite insert update selectOrder By
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