Storing a BLOB in a PostgreSQL Database using Python

Last Updated : 21 Nov, 2022

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 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()