How to Retrieve Blob Datatype from Postgres with Python

Namachila 5 hours ago

0 0 2 minutes read

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