How to Retrieve Blob Datatype from Postgres with Python

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.

Retrieve Blob Datatype from Postgres

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:

Retrieve Blob Datatype from Postgres

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

Retrieve Blob Datatype from Postgres

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

Output:

Retrieve Blob Datatype from Postgres