PYnative

Python Programming

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

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

Updated on:  | 38 Comments

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.

Further Reading:

Table of contents

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`))Code language: Python (python)

This table contains the following two BLOB columns.

  • Photo: To store an employee picture.
  • Biodata file: To store employee details in file format.
mysql table with blob columns
Python MySQL table with blob columns

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")
Code language: Python (python)

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.

mysql table after inserting BLOB data from Python
MySQL table after inserting BLOB data from Python

Note: We inserted employee id, name, photo, and bio-data file. For image and bio-data, we passed the location where it is present.

As you can see, we converted our image and file into a binary format by reading the image and file in the rb mode before inserting it into a BLOB column.

Also, we used a parameterized query to insert dynamic data into a MySQL table.

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")
Code language: Python (python)

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.

image and file stored on disk after reading BLOB data from mysql
image and file stored on disk after reading BLOB data from mysql

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.

TweetF  sharein  shareP  Pin

About Vishal

I’m Vishal Hule, the Founder of PYnative.com. As a Python developer, I enjoy assisting students, developers, and learners. Follow me on Twitter.

Related Tutorial Topics:

Python Python Databases

All Coding Exercises:

C Exercises
C++ Exercises
Python Exercises
Java Exercises
C# Exercises

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 25+ questions
  • Each Quiz contains 25 MCQ
Exercises
Quizzes

Comments

  2. deepak kumar nahta says

    hi
    vishal, I send you the function code and error message
    please give me a solution if possible
    def write_file():
    # Convert binary data to the proper format and write it on Hard Disk
    with open("c:\dkphoto.jpg", 'wb') as file:
    file.write("c:\imagile.jpg")

    following error

    File “C:\Users\Dell\PycharmProjects\Pycham\db.py”, line 145, in
    write_file()
    File “C:\Users\Dell\PycharmProjects\Pycham\db.py”, line 29, in write_file
    with open(“c:\dkphoto.jpg”, ‘wb’) as file:
    PermissionError: [Errno 13] Permission denied: ‘c:\\dkphoto.jpg’

    Reply

  6. Sarathkumar says

    How to insert image file using post method in MySQL workbench , I have error pls tell me a suggestion to rectify sn error

    Reply

  7. Bahati says

    thinks for this knowledge §
    Now, how can i display image in label tkinter after save it on mysql database.
    The image who is captured by openCV.
    thanks again for your help.

    Reply

  8. Ngawang says

    Hello Vishal!
    I am getting this error while inserting an image.

    Inserting BLOB into python_employee table
    Failed to insert BLOB data into MySQL table 1406 (22001): Data too long for column ‘photo’ at row 1
    MySQL connection is closed

    Reply

    • Vishal says

      Hey Ngawang,

      It all depends on the column type used for the photo column. Depending on your needs. BLOB can only store up to 65,535 bytes. Use MEDIUMBLOB to store larger data.

      TINYBLOB: maximum length of 255 bytes
      BLOB: maximum length of 65,535 bytes
      MEDIUMBLOB: maximum length of 16,777,215 bytes
      LONGBLOB: maximum length of 4,294,967,295 bytes

      Reply

  9. Kanad says

    Hi Vishal,
    There is an error in the code in the “Retrieve Image and File stored as a BLOB from MySQL Table”;

    It should have been * instead of photo:

    sql_fetch_blob_query = """SELECT * from python_employee where id = %s"""
    Reply

  10. ismail says

    that’s not working
    all I get is Process finished with exit code 0
    but when I check my DB I don’t see any changes

    Reply

  13. CHERALA ALEKHYA . says

    Hi, for each record you are giving the path but I have thounsands of records. How should I give path dynamically for all of them?

    Reply

    • Vishal says

      Hi CHERALA ALEKHYA,

      you need to create a sperate function in which you can generate a dynamic path and pass that path to Insert function.

      Reply

  14. srinadh says

    I need help for python mysql where i want to insert a row from keyboard like prepared statement in java-mysql.

    Reply

  15. Alain says

    Hi,

    How did you pass your photo from front end html to back end? Is your photo here in this example a file?

    Thanks

    Reply

    • Vishal says

      Hi Alain,

      we have not passed file from front end. we created a simple example by passing file path of a png image. if you want to pass file from front-end You have to use web framework.

      Reply

  16. Jincy mariam Johnson says 

    def csv_file_datas(ids, file):
try:
conn = MySQLdb.connect(host=”localhost”, user=”root”, password=”root”, db=”pda”)
c = conn.cursor()
query = “””SELECT * from skillassessment_csvfiles where fileid_id = %s”””
c.execute(query,(ids))
records = c.fetchall()
for row in records:
new_file = row[2]
print “Storing file on disk \n”
with open(settings.MEDIA_ROOT + ‘temp_file/’ + file, ‘wb’) as file:
new_data = file.write(new_file)
conn.commit()
conn.close()
except Exception as e:
print e

    This is my code. Actually, this code is working fine in localhost.
    But in AWS shows an error that I mentioned earlier (” ‘long’ objects is not iterable” ).

    Reply

  17. Jincy mariam Johnson says 

    def csv_file_datas(ids, file):
try:
conn = MySQLdb.connect(host=”localhost”, user=”root”, password=”root”, db=”p_db”)
c = conn.cursor()
query = “””SELECT * from skillassessment_csvfiles where fileid_id = %s”””
c.execute(query,(ids))
records = c.fetchall()
for row in records:
new_file = row[2]
print “Storing file on disk \n”
with open(settings.MEDIA_ROOT + ‘temp_file/’ + file, ‘wb’) as file:
new_data = file.write(new_file)
conn.commit()
conn.close()
except Exception as e:
print e, ‘errorsssss’
    Reply

    • Vishal says

      Hey Jincy, can you please paste the code you trying. also, check the type you are using to read file from DB

      Reply

  20. Anatoliy says

    HI,
    I’m just comment your solution.
    You gets a warning about truncated data if you put an image larger than 64k.

    Reply

  21. Utkarsh Kore says

    I’m getting error while reading the file
    UnucodeDecodeError: ‘utf-8’ codec can’t decode byte 0xff in position 0: invalid start type

    Reply

Leave a Reply

your email address will NOT be published. all comments are moderated according to our comment policy.

Use <pre> tag for posting code. E.g. <pre> Your entire code </pre>

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

Explore Python

Coding Exercises

Legal Stuff

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our:

Copyright © 2018–2026 pynative.com