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