Python SQLite BLOB to Insert and Retrieve file and images

In this article, you will learn to insert and retrieve a file stored as a BLOB in the SQLite table using Python’s sqlite3 module.

• Use SQLite BLOB data type to store any binary data into the SQLite table using Python. Binary can be a file, image, video, or a media
• Read BLOB data from the SQLite table in Python.

Prerequisites

Please refer to creating an SQLite table from Python. For this lesson, I am using the ‘new_employee’ table present in my SQLite database.

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

CREATE TABLE new_employee ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, photo BLOB NOT NULL, resume BLOB NOT NULL);

The table contains two BLOB columns.

• A photo column contains an employee picture.
• A resume column includes a file which is a developer resume.

Before proceeding to the examples first understand what is BLOB.

What is BLOB

A BLOB (large binary object) is an SQLite data type that stores large objects, typically large files such as images, music, videos, documents, pdf, etc.

We need to convert our files and images into binary data (byte array in Python) to store it into SQLite database.

Insert Image and File as a BLOB data into SQLite Table

As of now, a table is empty. Let’s insert employee’s photos and resume files in it.

To insert BLOB data into SQLite table from Python, you need to follow the below steps: –

• First, establish the SQLite connection from Python.
• Second, create a cursor object using the connection object.
• Then, define the SQLite INSERT Query. You need to know the table and the column name in which you want to insert data.
• Next, create a function to convert digital data, i.e., images or any file, to binary data
• Execute the INSERT query in Python using the cursor.execute().
• After the successful execution of the SQLite insert operation, commit your changes to the database.
• Close the Cursor and SQLite database connection.
• Most important, Catch SQL exceptions, if any.

Example

import sqlite3

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

def insertBLOB(empId, name, photo, resumeFile):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")
        sqlite_insert_blob_query = """ INSERT INTO new_employee
                                  (id, name, photo, resume) VALUES (?, ?, ?, ?)"""

        empPhoto = convertToBinaryData(photo)
        resume = convertToBinaryData(resumeFile)
        # Convert data into tuple format
        data_tuple = (empId, name, empPhoto, resume)
        cursor.execute(sqlite_insert_blob_query, data_tuple)
        sqliteConnection.commit()
        print("Image and file inserted successfully as a BLOB into a table")
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert blob data into sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("the sqlite connection is closed")

insertBLOB(1, "Smith", "E:\pynative\Python\photos\smith.jpg", "E:\pynative\Python\photos\smith_resume.txt")
insertBLOB(2, "David", "E:\pynative\Python\photos\david.jpg", "E:\pynative\Python\photos\david_resume.txt")

Output:

Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed
Connected to SQLite
Image and file inserted successfully as a BLOB into a table
the sqlite connection is closed

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

Retrieve Image and File stored as a BLOB from SQLite Table

Assume you want to read the file or images stored in the SQLite table in BLOB format and write that file back to some location on the disk so you can view and read it in a proper format.

In this example, we are reading the employee photo and resume file that we stored in the SQLite table stored as a BLOB in the previous example.

To read BLOB data from SQLite Table using Python, you need to follow the below steps: –

• Establish the SQLite database connection in Python.
• Second, Define the SELECT query to fetch BLOB columns from the table.
• Execute the SELECT query in Python using a cursor.execute()
• Use the cursor.fetchall() to retrieve all the rows from the result set and iterate over it.
• Create a function to convert BLOB data in proper format and save it in a readable format.
• Close the Cursor and MySQL database connection.

import sqlite3

def writeTofile(data, filename):
    # Convert binary data to proper format and write it on Hard Disk
    with open(filename, 'wb') as file:
        file.write(data)
    print("Stored blob data into: ", filename, "\n")

def readBlobData(empId):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_fetch_blob_query = """SELECT * from new_employee where id = ?"""
        cursor.execute(sql_fetch_blob_query, (empId,))
        record = cursor.fetchall()
        for row in record:
            print("Id = ", row[0], "Name = ", row[1])
            name = row[1]
            photo = row[2]
            resumeFile = row[3]

            print("Storing employee image and resume on disk \n")
            photoPath = "E:\pynative\Python\photos\db_data\\" + name + ".jpg"
            resumePath = "E:\pynative\Python\photos\db_data\\" + name + "_resume.txt"
            writeTofile(photo, photoPath)
            writeTofile(resumeFile, resumePath)

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read blob data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("sqlite connection is closed")

readBlobData(1)
readBlobData(2)

As you can see images and files are stored on disk after reading BLOB data from SQLite.

Note: To copy binary data on the hard drive, we converted binary data(BLOB) to the proper format and wrote it on Hard Disk. In our example, we converted the photo blob column into png and resume blob column data into txt file.

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.