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
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(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)
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 »