This blog shows you how to work with MySQL BLOB data in Python, with samples 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 change a function named read_file() that reads a file and returns the file’s content:
Second, we create a new function named update_blob() that updates photo for an author stated by author_id
Let’s inspect the code in detail:
Primary, we call the read_file() function to read data from a file and return it.
Second, we make up an UPDATE statement that updates photo column for an author stated by author_id . The args variable is a tuple that covers file data and author_id . We will pass this variable to the execute () method composed with the query .
Third, inside the try except block, we connect to the database, instantiate a cursor, and execute the query with arguments. To make the change actual, we call commit() method of the MySQLConnection object.
Fourth, we close the cursor and database connection in the finally block.
Notice that we introduced MySQLConnection and Error objects from the MySQL Connector/Python package and read_db_config() function from the python_mysql_dbconfig module.
Let’s test the update_blob() function.
Notice that you can use the subsequent photo and put it into the pictures folder for testing.
It works as likely.
Reading BLOB data in Python
In this sample, we select BLOB data from the authors table and write it into a file.
First, we change a write_file() function that write a binary data into a file as follows:
Additional, we create a new function named read_blob() as below:
The read_blob() function reads BLOB data from the authors table and write it into a file stated by the filename parameter.
The code is straightforward:
Primary, we make up a SELECT statement that retrieves photo of a specific author.
Next, we get the database configuration by calling the read_db_config() function.
Third, inside the try except block, we connect to the database, instantiate cursor, and execute the query. Once we got the BLOB data, we use the write_file() function to write it into a file stated by the filename .
Fourth, in the finally block, we close the cursor and database connection.
Now, let’s test the read_blob() function.
If you open the output folder in the project and see a picture there, it means that you have effectively read the blob from the database.
In this blog, we have made known you how to update and read BLOB data in MySQL from Python using MySQL Connector/API.