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
conn = psycopg2.connect( * * config)
cur = conn.cursor()
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
import psycopg2
from config import config
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):
conn = None
try :
conn = psycopg2.connect( * * config)
cur = conn.cursor()
cur.execute( 'SELECT * FROM BLOB_DataStore' )
db = cur.fetchall()
BLOB = db[S_No - 1 ][ 2 ]
Binary_To_File(BLOB, newFileName, db[S_No - 1 ][ 1 ])
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print (error)
finally :
if conn is not None :
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 :
conn = psycopg2.connect( * * config)
cur = conn.cursor()
cur.execute( 'SELECT * FROM BLOB_DataStore' )
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")
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print (error)
finally :
if conn is not None :
conn.commit()
|
Output:
Retrieve Blob Datatype from Postgres
Share your thoughts in the comments
Please Login to comment...