How to Retrieve Blob Datatype from Postgres with Python

Last Updated : 21 Nov, 2022

In this article, We will learn How to retrieve BLOB from a PostgreSQL database.

BLOB is a Binary large object (BLOB) is a data type that can store any binary data.
To Retrieve Blob Datatype from Postgres with Python we will use psycopg2.

Stepwise Implementation:

Connect to the PostgreSQL server.
Create a cursor with the help of cursor() method in Python.
Execute the Retrieve Query using the execute() method with BLOB VALUES.
And then Close the Cursor and commit the changes.

The below code is an example to Retrieve BLOB data in a PostgreSQL database.

Python3

import psycopg2
from config import config

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

# Retrieve BLOB data from the database.
cur.execute('SELECT * FROM BLOB_DataStore')
db = cur.fetchall()

BLOB = db[0][2]
open("FromDB"+db[0][1], 'wb').write(BLOB)

cur.close()
conn.commit()

Complete Function to Retrieve the BLOB data into the database

The code to Retrieve BLOB data in a PostgreSQL database with the Table name blob_datastore.

Python3

# Complete Function to Retrieve
# the BLOB data into the database.
import psycopg2
from config import config

# This Function will Creates File from binary data.
def Binary_To_File(BLOB, FileName, oldFileName):
    with open(f"{FileName}", 'wb') as file:
        file.write(BLOB)
    print(f"{oldFileName} File saved With Name name {FileName}")


def retrieve_BLOB(S_No, newFileName):
    """ Retrieve a BLOB From 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()

        # Retrieve BLOB data from the database.
        cur.execute('SELECT * FROM BLOB_DataStore')
        db = cur.fetchall()

        BLOB = db[S_No-1][2]
        
        # open("FromDB"+db[0][1], 'wb').write(BLOB)
        Binary_To_File(BLOB, newFileName, db[S_No-1][1])

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


retrieve_BLOB(1, 'OctaFromDB.jpg')

Output:

Retrieving Different Types of Files(BLOB Datatype)

The code to Retrieve BLOB data from PostgreSQL database With the Table name blob_datastore. The type of data that we will Retrieve:

MP4
PDF
DOCS
Image
Video
gif
HTML
MP3

Example:

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 fetch data from the database.
    cur.execute('SELECT * FROM BLOB_DataStore')

    # open(file,'wb').write() is used to
    # write the binary data to the file.
    for row in cur.fetchall():
        BLOB = row[2]
        open("new"+row[1], 'wb').write(BLOB)
        print(row[0], row[1], "BLOB Data is saved\
        in Current Directory")

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