Managing MySQL Blob using sqlalchemy

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

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")
for row  in my_result:
    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.

query="UPDATE student_profile SET profile_photo=%s WHERE ID=%s"

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"
print("Row Deleted  ")

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

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    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-2024 All rights reserved worldwide Privacy Policy Disclaimer