Storing a BLOB in a PostgreSQL Database using Python

This article focuses on, Storing BLOB in a PostgreSQL database.

• BLOB is a Binary large object (BLOB) is a data type that can store any binary data.
• To Store Blob data in a Postgres database Table, we will use psycopg2.
• The table for storing BLOB data in PostgreSQL is called a Large Object table and the data type is byte.
• We can store png, jpg, gif, pdf, CSV, mp3 & mp4 files.

Stepwise Implementation:

• Connect to the PostgreSQL server and to Connect with PostgreSQL Database we use connect() function.
•  Create a cursor with the help of the cursor() method.
• Execute the Insert Query using the execute() method with BLOB VALUES.
• To store any binary data in a PostgreSQL database First, we need to convert the File to Binary Large Object (BLOB) data type.
• Close the Cursor and commit the changes.

The below code is an example to store BLOB data in a PostgreSQL database. Where the table name is blob_datastore.

Python3

import psycopg2
from config import config

# This Function will open &
# convert the image or file data
# to binary data.
def convert_To_Binary(filename):
    with open(filename, 'rb') as file:
        data = file.read()
    return data


def insert_BLOB(S_No, FileName):
    """ insert a BLOB into a table """
    conn = None
    try:

        # connect to the PostgreSQL server
        # & creating a cursor object
        conn = psycopg2.connect(**config)

        # Creating a cursor with name cur.
        cur = conn.cursor()

        # Binary Data
        file_data = convert_To_Binary(FileName)

        # BLOB DataType
        BLOB = psycopg2.Binary(file_data)

        # SQL query to insert data into the database.
        cur.execute(
            "INSERT INTO blob_datastore(s_no,file_name,blob_data)\
            VALUES(%s,%s,%s)", (S_No, FileName, BLOB))

        # Close the connection
        cur.close()

    except(Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            # Commit the changes to the database
            conn.commit()


# Driver's Code
# Let's Run the insert_BLOB Function
insert_BLOB(1, 'newOcta.jpg')

Output:

Storing a BLOB in a PostgreSQL Database

Storing Different Types of Files(BLOB Datatype) 

Hence we have established a connection with the PostgreSQL database and stored different types of File’s in the PostgreSQL database. In this example, we will store a video file and a pdf in database.

Python3

import psycopg2
from config import config

conn = None
try:
    # connect to the PostgreSQL server
    conn = psycopg2.connect(**config)

    # Creating a cursor with name cur.
    cur = conn.cursor()

    # SQL query to insert data into the database.
    # open('File,'rb').read() is used to read the file.
    # where open(File,'rb').read() will return
    # the binary data of the file.
    # psycopg2.Binary(File_in_Bytes) is used to
    # convert the binary data to a BLOB data type.
    BLOB_vdo = psycopg2.Binary(
        open('files\cartoon.mp4', 'rb').read())
    BLOB_pdf = psycopg2.Binary(
        open('files\BlobNotes.pdf', 'rb').read())

    cur.execute('INSERT INTO blob_datastore(s_no,file_name,\
    blob_data) VALUES (%s,%s,%s);',
                (1, 'cartoon.mp4', BLOB_vdo))
    cur.execute('INSERT INTO blob_datastore(s_no,file_name,\
    blob_data) VALUES (%s,%s,%s);',
                (2, 'BlobNotes.pdf', BLOB_pdf))

    # close the cursor
    cur.close()

except(Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:

        # Commit the changes to the database
        conn.commit()

Output:

Storing a BLOB in a PostgreSQL Database