Managing BLOB Data in MySQL with Python: Insertion and Retrieval

Inserting and retrieving Binary Large Object (BLOB) data from MySQL tables using Python is a crucial part of many modern database applications. However, this process can seem intimidating to new developers, which is why we have created this article to help you understand how to insert and retrieve BLOB data from MySQL using Python.

Inserting BLOB Data into MySQL Table Using Python

1. Creating a MySQL Table with a BLOB Column

Before we can insert BLOB data into a MySQL table using Python, we first need to create a table with a BLOB column. A BLOB data type allows us to store binary data into the MySQL database, including images, audio files, videos, and other multimedia files.

2. The following is an example of a MySQL table creation script that includes a BLOB column:

CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    avatar BLOB,
    PRIMARY KEY (id)
);

This creates a table with three columns. The first column is an auto-incrementing integer ID column, the second column is a string column that stores the user’s name, and the third column is a BLOB column that stores the user’s avatar.

3. Converting Digital Data into Binary Format and Inserting into Table

Now that we have a table with a BLOB column, we can insert BLOB data into this table using Python. The first step is to convert digital data into a binary format that can be stored in the BLOB column.

We use the Python binary built-in function ‘open’ to open the file as a binary file.

import mysql.connector

def insert_blob():
  try:
    conn = mysql.connector.connect(
      host='localhost',
      user='root',
      password='password',
      database='test_db'
    )

    cursor = conn.cursor(prepared=True)

    with open("image.png", "rb") as file_select:
      data = file_select.read()

    update_blob_query = "INSERT INTO users (name, avatar) VALUES(%s,%s)"

    record_insertion_tuple = ("test_user", data)

    cursor.execute(update_blob_query, record_insertion_tuple)

    conn.commit()

    print("BLOB data inserted successfully")

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

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

if __name__ == '__main__':
  insert_blob()

In the above code, we are connecting to the MySQL database using the ‘mysql.connector’ library, selecting an image in binary format using the ‘open’ method, creating a query to insert the binary data into the MySQL table with placeholders to prevent SQL injection, passing in the record insertion tuple, and finally committing the changes to the MySQL database.

4. Catching SQL Exceptions

It is crucial to catch SQL exceptions while handling BLOB data insertion because it protects our application from crashes caused by missing input data, malformed SQL statements, or an incorrect connection to the database. The code above catches the errors using a try-catch block and prints out the exception error message for easy debugging.

Retrieving BLOB Data from MySQL Table Using Python

1. Creating a SELECT Query to Fetch BLOB Column Values

It is essential to know how to retrieve BLOB data from MySQL tables using Python. In the example below, we select all user’s avatar from the MySQL database:

import mysql.connector
from PIL import Image

def retrieve_blob():
  try:

    conn = mysql.connector.connect(
      host='localhost',
      user='root',
      password='password',
      database='test_db'
    )

    cursor = conn.cursor(prepared=True)

    retrieve_blob_query = "SELECT name, avatar FROM users"

    cursor.execute(retrieve_blob_query)

    for (name, avatar) in cursor:

      avatar_file_path = "user_avatar_"+str(name)+".png"

      with open(avatar_file_path, 'wb') as avatar_file:
        avatar_file.write(avatar)

      print("BLOB data retrieved successfully and saved in a file")
  except mysql.connector.Error as error:
    print("Failed to retrieve blob data from MySQL table {}".format(error))
  finally:
      # Closing the cursor and database connection
      if (conn.is_connected()):
          cursor.close()
          conn.close()
          print("MySQL connection is closed")

if __name__ == '__main__':
  retrieve_blob()

In the above code, we are connecting to the MySQL database using the `mysql.connector` library, creating a SELECT query to select the name and avatar columns from the users table, and finally looping over each row and saving the avatar to disk.

2. Retrieving BLOB Data and Writing to Disk

In the retrieval script, we open a file with `open` method. Then, we take the binary image data retrieved from the database file and write it to disk.

In this example, we use the ‘Pillow’ library to save the binary data in PNG format, with the naming convention ‘user_avatar_ [name] .png’ to distinguish avatars.

Conclusion

Inserting and retrieving BLOB data from MySQL tables using Python is essential for many modern database applications. In this article, we have seen how to create a MySQL table with a BLOB column using Python, convert digital data into binary format, and insert it into the table.

We have also discussed the importance of catching SQL exceptions during data insertion and demonstrated how to retrieve BLOB data from a MySQL table using Python using a SELECT query. We hope this article provides significant guidance for developers looking to insert and retrieve BLOB data from MySQL tables using Python.

In conclusion, this article has shown how to insert and retrieve Binary Large Object (BLOB) data from MySQL tables using Python. To insert BLOB data into a MySQL table, a table with a BLOB column needs to be created, and digital data should be converted to binary format before insertion.

Additionally, it is crucial to catch SQL exceptions to avoid crashes caused by missing input data. To retrieve BLOB data, a SELECT query is used to fetch BLOB column values with a file opened with the “open” method.

The binary image data is then written to disk. The takeaway is that BLOB data is a crucial aspect of many modern database applications, and understanding how to handle it using Python is essential for efficient database management.