Adding Displaying and updating MySQL Blob data in Python

BLOB : Binary Large Objects
While handling binary or unstructured data like image or multimedia files we use Blob Data type of MySQL table.
BLOB: binary large object
We will learn how to add binary data , update and display them by using sample image file as data.

We defined my_conn as connection object
In all the codes below replace userid, password and database_name with your MySQL login and database details.

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

We will learn how to add binary data , update and display them by using sample image file as data.

student table

Our student_profile table has three columns,

id int(2)
student varchar(10)
profile_photo blob

This can be linked with our student table and photos of the student can be displayed.

Adding image to a Blob column

We have 4 sample images inside the zip file ( download at the end of this page with sample codes ). Here we are adding one record only with id = 1 and student='Alex'. We used the 1.png file as photo of the student and added to table.
You can repeat same process for balance 3 records by changing the id , student name and sample photo and insert the records to table.

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

fob=open('D:/testing/blob/photos/1.png','rb')# change your path
fob=fob.read()

data=(1,'Alex',fob) # tuple with data 
id=my_conn.execute("INSERT INTO  student_profile(id,student,profile_photo) \
                  VALUES (%s,%s,%s)",data)
print("Row Added  = ",id.rowcount)

Displaying records

We will collect and display all records, here for the photos stored in Blob column we will create one image ( for each record ) and store it inside output folder. The image file name is based on id of the record ( 1.png, 2.png ... etc )

Check these lines on how the images are created based on the id of the record for file name and how data is used to create the file. Here row[0] is the id of the record and row[2] holds the binary data collected from Blob column of MySQL table.

fob=open('D:/testing/blob/output/'+ str(row[0])+'.png','wb')
    fob=fob.write(row[2]) # blob data

Full code to display records is here.

my_cursor=my_conn.execute("SELECT * FROM  student_profile")
my_result=my_cursor.fetchall()
for row  in my_result:
    print(row)
    fob=open('D:/testing/blob/output/'+ str(row[0])+'.png','wb')# change your path
    fob=fob.write(row[2]) # blob data

Updating record

We will update the photo for one record.

fob=open('D:/testing/blob/photos/4.png','rb')
fob=fob.read()

query="UPDATE student_profile SET profile_photo=%s WHERE ID=%s"
data=(fob,3)

my_cursor=my_conn.execute(query,data)
print("Rows updated = ",my_cursor.rowcount)

Using delete query

By using the id number of the row we will delete the record.

query="DELETE FROM  student_profile WHERE id=%s"
my_data=[id]
my_conn.execute(query,my_data)
print("Row Deleted  ")

By using Tkinter we can create one GUI interface to store user uploaded photo, in MySQL table and display,update , delete the student photos with other data as entered.
Tkinter window to display MySQL Blob Data type image( Single record)
Tkinter window to display MySQL Blob Data type image( All records)
Tkinter window upload and insert Blob Data type image
Tkinter update Blob Data type with user uploaded image
Tkinter delete record with Blob Data type

Download the zip file with code

Inside the zip file, there are four sample photos and one blank directory ( output ) where you can store the photos after collecting from MySQL table.

Tkinter window to add user entered data to MySQL table More on Error handling
Tkinter MySQL Record display MySQL Update MySQL Delete

Python functions for MySQL Collect records Python- Tutorials

Subscribe to our YouTube Channel here

Managing MySQL Blob using sqlalchemy