Python MySQL – Read & Update BLOB in MySQL Database

Summary: in this tutorial, you will learn how to work with MySQL BLOB data in Python including updating and reading BLOB data.

This tutorial picks up where the Calling Stored Procedures in Python tutorial left off.

The  authors table has a column named photo whose data type is BLOB. We will read data from an image and update it to the photo column.

Updating BLOB data in Python

The following program reads data from a file and updates it to the database:

from mysql.connector import MySQLConnection, Error
from config import read_config


def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
        return photo


def update_blob(author_id, filename):
    # Read file data
    data = read_file(filename)

    # Prepare update query and data
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"

    args = (data, author_id)

    config = read_config()

    try:
        # Establish a connection to the MySQL database
        with MySQLConnection(**config) as conn:
            # Create a cursor to execute SQL queries
            with conn.cursor() as cursor:
                # Execute the update query with the provided arguments
                cursor.execute(query, args)
                # Commit the changes to the database
                conn.commit()

    except Error as e:
        print(e)

if __name__ == '__main__':
    try:
        author_id = 3  
        filename = 'images/francis_tugwell.png' 
        update_blob(author_id, filename)
    except Error as e:
        print(e)Code language: PHP (php)

How it works.

First, define a function called read_file() that reads a file and returns the file’s content:

def read_file(filename):
    with open(filename, 'rb') as f:
        photo = f.read()
        return photoCode language: Python (python)

Second, create a new function called update_blob() that updates the photo for an author specified by author_id .

def update_blob(author_id, filename):
    # Read file data
    data = read_file(filename)

    # Prepare update query and data
    query = "UPDATE authors " \
            "SET photo = %s " \
            "WHERE id  = %s"

    args = (data, author_id)

    config = read_config()

    try:
        # Establish a connection to the MySQL database
        with MySQLConnection(**config) as conn:
            # Create a cursor to execute SQL queries
            with conn.cursor() as cursor:
                # Execute the update query with the provided arguments
                cursor.execute(query, args)
                # Commit the changes to the database
                conn.commit()

    except Error as e:
        print(e)Code language: Python (python)

How it works:

• First, call the read_file() function to read data from a file and return the binary data.
• Second, compose an UPDATE statement that updates photo column for an author specified by author_id . The args variable is a tuple that contains file data and author_id. We will pass this variable to the execute() method together with the query .
• Third, inside the  try...except block, connect to the database, create a cursor, and execute the query with args . To apply the permanent change to the database, call the commit() method of the MySQLConnection object.
• Finally, close the cursor and database connection in the  finally block.

Third, use the update_blob() function to read an image (images/francis_tugwell.png) from the images directory of the project directory:

if __name__ == '__main__':
    try:
        author_id = 3  
        filename = 'images/francis_tugwell.png' 
        update_blob(author_id, filename)
    except Error as e:
        print(e)Code language: Python (python)

Notice that you can download the following photo and place it in the images directory:

Reading BLOB data in Python

The following program retrieves BLOB data from the authors table and write it into a file:

from mysql.connector import MySQLConnection, Error
from config import read_config

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)

def read_blob(author_id, filename):
    # Select the 'photo' column of a specific author
    query = "SELECT photo FROM authors WHERE id = %s"

    # Read database configuration
    config = read_config()

    try:
        # Establish a connection to the MySQL database
        with MySQLConnection(**config) as conn:
            # Create a cursor to execute SQL queries
            with conn.cursor() as cursor:
                # Execute the query to retrieve the blob data
                cursor.execute(query, (author_id,))
                
                # Fetch the blob data from the result set
                photo = cursor.fetchone()[0]

                # Write the blob data into a file
                write_file(photo, filename)

    except Error as e:
        print(e)        

if __name__ == '__main__':
    try:
        author_id = 3
        filename = 'images/3.png'
        read_blob(author_id, filename)
    except Error as e:
        print(e)        Code language: PHP (php)

How it works.

First, define a write_file() function that writes binary data into a file:

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)Code language: Python (python)

Second, create a new function called read_blob() that retrieves BLOB data from the database:

def read_blob(author_id, filename):
    # Select the 'photo' column of a specific author
    query = "SELECT photo FROM authors WHERE id = %s"

    # Read database configuration
    config = read_config()

    try:
        # Establish a connection to the MySQL database
        with MySQLConnection(**config) as conn:
            # Create a cursor to execute SQL queries
            with conn.cursor() as cursor:
                # Execute the query to retrieve the blob data
                cursor.execute(query, (author_id,))
                
                # Fetch the blob data from the result set
                photo = cursor.fetchone()[0]

                # Write the blob data into a file
                write_file(photo, filename)

    except Error as e:
        print(e)        Code language: Python (python)

The  read_blob() function reads BLOB data from the  authors table and write it into a file specified by the  filename parameter.

Third, call the read_blob() function to read the photo of the author id 3 and write it to a file:

if __name__ == '__main__':
    try:
        author_id = 3
        filename = 'images/3.png'
        read_blob(author_id, filename)
    except Error as e:
        print(e)   Code language: Python (python)

If you find a picture in the project’s images directory, it indicates that you have successfully read the BLOB from the database.

In this tutorial, you have learned how to read and write BLOB data in MySQL from Python.