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