Insert / Retrieve file and images as a Blob in MySQL using Python

In this lesson, you will learn how to insert or save any digital information such as a file, image, video, or song as blob data into a MySQL table from Python. We will also learn how to fetch the file, image, video, or song stored in MySQL using Python.

Goals of this article

• Insert binary data into a MySQL table using Python
• Read BLOB data files from the MySQL table in Python

Note: We are using the MySQL Connector Python module to connect MySQL.

Prerequisites

To Store BLOB data in a MySQL table, we need to create a table containing binary data. Alternatively, if you have a table, then modify it by adding one extra column with BLOB as its data type.

You can use the following query to create a table with a BLOB column.

CREATE TABLE `Python_Employee` ( `id` INT NOT NULL , `name` TEXT NOT NULL , `photo` BLOB NOT NULL , `biodata` BLOB NOT NULL , PRIMARY KEY (`id`))

As of now, The python_employee the table is empty. Let’s insert employees’ photos and bio-data files in it. Before executing the following programs, please make sure you have the Username and password to connect MySQL.

What is BLOB

A BLOB (large binary object) is a MySQL data type used to store binary data. We can convert our files and images into binary data in Python and keep them in the MySQL table using BLOB.

Note: To insert a file or image into the MySQL table, we need to create a BLOB column as a type. MySQL has the following four BLOB types. Each holds a variable amount of data.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

Above BLOB types differ only in the maximum length of the values they can hold. To read more on BLOB, you can visit this MySQL BLOB document.

Insert Image and File as a BLOB data into MySQL Table

Let’s insert employee photo and bio-data into a python_employee table. To insert BLOB data into MySQL Table from Python, you need to follow these simple steps: –

• Install MySQL Connector Python using Pip.
• Second, Establish MySQL database connection in Python.
• Create a function that can convert images and file into binary data.
• Then, Define the Insert query to enter binary data into the database table. All you need to know is the table’s column details.
• Execute the INSERT query using a cursor.execute(). It returns the number of rows affected.
• After the successful execution of the query, commit your changes to the database.
• Close the Cursor and MySQL database connection.
• Most important, Catch SQL exceptions, if any.
• At last, verify the result by selecting data from the MySQL table.

Let see the example now.

import mysql.connector

def convertToBinaryData(filename):
    # Convert digital data to binary format
    with open(filename, 'rb') as file:
        binaryData = file.read()
    return binaryData


def insertBLOB(emp_id, name, photo, biodataFile):
    print("Inserting BLOB into python_employee table")
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='python_db',
                                             user='pynative',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_insert_blob_query = """ INSERT INTO python_employee
                          (id, name, photo, biodata) VALUES (%s,%s,%s,%s)"""

        empPicture = convertToBinaryData(photo)
        file = convertToBinaryData(biodataFile)

        # Convert data into tuple format
        insert_blob_tuple = (emp_id, name, empPicture, file)
        result = cursor.execute(sql_insert_blob_query, insert_blob_tuple)
        connection.commit()
        print("Image and file inserted successfully as a BLOB into python_employee table", result)

    except mysql.connector.Error as error:
        print("Failed inserting BLOB data into MySQL table {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

insertBLOB(1, "Eric", "D:\Python\Articles\my_SQL\images\eric_photo.png",
           "D:\Python\Articles\my_SQL\images\eric_bioData.txt")
insertBLOB(2, "Scott", "D:\Python\Articles\my_SQL\images\scott_photo.png",
           "D:\Python\Articles\my_SQL\images\scott_bioData.txt")

Output:

Inserting BLOB into python_employee table
Image and file inserted successfully as a BLOB into python_employee table None
MySQL connection is closed
Inserting BLOB into python_employee table

Let’s have a look at python_employee table after inserting the image and file into it.

Retrieve Image and File stored as a BLOB from MySQL Table using Python

Suppose we want to read the file or images stored in the MySQL table in binary format and write that file back to some arbitrary location on the hard drive. Let see how we can do that.

• Read employee image, and file from MySQL table stored as a BLOB.
• Write this BLOB binary data on a disk. We can pass the file format we want it to display to write this binary data on a hard disk.

To read BLOB data from MySQL Table using Python, you need to follow these simple steps: –

• Install MySQL Connector Python using pip.
• Second, Establish MySQL database connection in Python.
• Then, Define the SELECT query to fetch BLOB column values from the database table.
• Execute the SELECT query using cursor.execute()
• Use cursor.fetchall() to retrieve all the rows from the result set and iterate over it.
• Create a function to write BLOB or binary data that we retrieved from each row on disk in a correct format.
• Close the Cursor and MySQL database connection.

import mysql.connector


def write_file(data, filename):
    # Convert binary data to proper format and write it on Hard Disk
    with open(filename, 'wb') as file:
        file.write(data)


def readBLOB(emp_id, photo, bioData):
    print("Reading BLOB data from python_employee table")

    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='python_db',
                                             user='pynative',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_fetch_blob_query = """SELECT * from python_employee where id = %s"""

        cursor.execute(sql_fetch_blob_query, (emp_id,))
        record = cursor.fetchall()
        for row in record:
            print("Id = ", row[0], )
            print("Name = ", row[1])
            image = row[2]
            file = row[3]
            print("Storing employee image and bio-data on disk \n")
            write_file(image, photo)
            write_file(file, bioData)

    except mysql.connector.Error as error:
        print("Failed to read BLOB data from MySQL table {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")


readBLOB(1, "D:\Python\Articles\my_SQL\query_output\eric_photo.png",
         "D:\Python\Articles\my_SQL\query_output\eric_bioData.txt")
readBLOB(2, "D:\Python\Articles\my_SQL\query_output\scott_photo.png",
         "D:\Python\Articles\my_SQL\query_output\scott_bioData.txt")

Output:

Reading BLOB data from python_employee table
Id = 1
Name = Eric
Storing employee image and bio-data on disk
MySQL connection is closed


Reading BLOB data from python_employee table
Id = 2
Name = Scott
Storing employee image and bio-data on disk
MySQL connection is closed

Retrieved image and file from MySQL table and stored on disk.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.