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
- DOCS
- Image
- Video
- gif
- HTML
- MP3
Retrieve Blob Datatype from Postgres
Example:
Python3
import psycopg2 from config import config   conn = Nonetry:     # 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
