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
