Python SQLite BLOB to Insert and Retrieve file and images

In this article, I will let you know how to insert and retrieve a file stored as a BLOB in the SQLite table using Python.

Goals of this lesson

In this lesson, you’ll learn how to use Python built-in module sqlite3 to insert and retrieve a file stored as a BLOB in the SQLite table. You’ll learn how to:

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

Prerequisite

Before executing the following SQLite BLOB operations, please make sure you know the SQLite table name, and in which you want to store BLOB data. To Store BLOB data in SQLite table, we need to create a table which can hold binary data, or you can also modify any existing table and add one extra column with BLOB as its data type.

If tables don’t exist in your SQLite database, then please refer to how to create a SQLite table from Python. For this demo, I am using “new_employee” table present in my SQLite database. You can use the following query to a create 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 new_developer table contains two BLOB columns.

• A photo column contains an employee picture.
• A resume column contains a file which is 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 which can be used to store large objects typically large files such as images, music, videos, documents, pdf, etc. We need to convert our files and images into binary data i.e., byte array in Python to store and retrieve from SQLite database

The SQLite table is empty as of now let’s insert a few employees photo and resume file in it.

Insert Image and File as a BLOB data into SQLite Table

Let’s insert employee photo and resume into a new_developer table. To insert BLOB data into SQLite table from Python, you need to follow these simple steps

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

Let see the example now.

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 image and file into it.

Retrieve Image and File stored as a BLOB from SQLite Table

Suppose we want to read the file or images stored in 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. Can we do it?

In this example, we are reading 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 these simple steps: –

• Establish the SQLite database connection in Python.
• Second, Define the SELECT query to fetch BLOB columns from the 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 convert a BLOB data in proper format and 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 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)

Output:

Connected to SQLite
Id =  1 Name =  Smith
Storing employee image and resume on disk 

Stored blob data into:  E:\pynative\Python\photos\db_data\Smith.jpg 

Stored blob data into:  E:\pynative\Python\photos\db_data\Smith_resume.txt 

sqlite connection is closed

Connected to SQLite
Id =  2 Name =  David
Storing employee image and resume on disk 

Stored blob data into:  E:\pynative\Python\photos\db_data\David.jpg 

Stored blob data into:  E:\pynative\Python\photos\db_data\David_resume.txt 

sqlite connection is closed

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

Next Steps:

To practice what you learned in this article, I have created a Python Database programming Quiz and Exercise Project.

That’s it. Folks Let me know your comments and questions in the section below.