This tutorial shows you how to work with MySQL BLOB data in Python, with examples of updating and reading BLOB data.
The authors table has a column named photo whose data type is BLOB. We will read data from a picture file and update to the photo column.
Updating BLOB data in Python
First, we develop a function named read_file() that reads a file and returns the file’s content:
|
1
2
3
4
|
def read_file(filename):
with open(filename, 'rb') as f:
photo = f.read()
return photo
|
Second, we create a new function named update_blob() that updates photo for an author specified by author_id .
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
def update_blob(author_id, filename):
# read file
data = read_file(filename)
# prepare update query and data
query = "UPDATE authors " \
"SET photo = %s " \
"WHERE id = %s"
args = (data, author_id)
db_config = read_db_config()
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
|
Let’s examine the code in detail:
- First, we call the
read_file()function to read data from a file and return it. - Second, we compose an UPDATE statement that updates photo column for an author specified by
author_id. Theargsvariable is a tuple that contains file data andauthor_id. We will pass this variable to theexecute()method together with thequery. - Third, inside the
try exceptblock, we connect to the database, instantiate a cursor, and execute the query withargs. To make the change effective, we callcommit()method of the MySQLConnection object. - Fourth, we close the cursor and database connection in the
finallyblock.
Notice that we imported MySQLConnection and Error objects from the MySQL Connector/Python package and read_db_config() function from the python_mysql_dbconfig module that we developed in the previous tutorial.
Let’s test the update_blob() function.
|
1
2
3
4
5
|
def main():
update_blob(144, "pictures\garth_stein.jpg")
if __name__ == '__main__':
main()
|
Notice that you can use the following photo and put it into the pictures folder for testing.
Inside the main function, we call the update_blob() function to update the photo column for the author with id 144. To verify the result, we select data from the authors table.
|
1
2
|
SELECT * FROM authors
WHERE id = 144;
|
It works as expected.
Reading BLOB data in Python
In this example, we select BLOB data from the authors table and write it into a file.
First, we develop a write_file() function that write a binary data into a file as follows:
|
1
2
3
|
def write_file(data, filename):
with open(filename, 'wb') as f:
f.write(data)
|
Second, we create a new function named read_blob() as below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
def read_blob(author_id, filename):
# select photo column of a specific author
query = "SELECT photo FROM authors WHERE id = %s"
# read database configuration
db_config = read_db_config()
try:
# query blob data form the authors table
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, (author_id,))
photo = cursor.fetchone()[0]
# write blob data into a file
write_file(photo, filename)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
|
The read_blob() function reads BLOB data from the authors table and write it into a file specified by the filename parameter.
The code is straightforward:
- First, we compose a SELECT statement that retrieves photo of a specific author.
- Second, we get the database configuration by calling the
read_db_config()function. - Third, inside the
try exceptblock, we connect to the database, instantiate cursor, and execute the query. Once we got the BLOB data, we use thewrite_file()function to write it into a file specified by thefilename. - Fourth, in the finally block, we close the cursor and database connection.
Now, let’s test the read_blob() function.
|
1
2
3
4
5
|
def main():
read_blob(144,"output\garth_stein.jpg")
if __name__ == '__main__':
main()
|
If you open the output folder in the project and see a picture there, it means that you have successfully read the blob from the database.