- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Handling PostgreSQL BLOB data in Python
PostgreSQL is an open-source, object-relational database management system that offers diverse data types to save data.The BLOB (Binary large object) data kind is one instance of this. It is used to keep large binary records, such as audio, video, and photograph files.
We should first set up the psycopg2 package, which offers a Python interface for PostgreSQL, so as to work with PostgreSQL in Python. The pip package manager is used to install it.
Syntax
pip install psycopg2-binary
After the installation of the psycopg2 library, we need to connect to our PostgreSQL database with Python.
The following operations are accomplished to handle BLOB records in PostgreSQL
Creating a table with a BLOB column
In PostgreSQL, the byte data type ought to be used on the way to build a table with a BLOB column. We will keep binary information up to one GB using the byte data type.
Inserting BLOB data into the table
We ought to read the binary information from a document and insert it into the "image" column so that you can insert BLOB data into the table. The SQL statement INSERT may be used to insert data right into a table that has a BLOB column.
Reading the BLOB statistics from the table
We have to use a select statement to retrieve the binary records from the "image" column so that it will read the BLOB records from the table.
Example
Here's an example code that demonstrates how to handle PostgreSQL BLOB (binary large object) data in Python using the psycopg2 library.
The "id" column is of type SERIAL, which generates a unique integer value for each row automatically. The "image" column is of type BYTEA, which stores the BLOB data.
Algorithm
Import the required libraries: psycopg2 and io.
Establish a connection to your PostgreSQL database using the psycopg2 library.
Create a cursor object from the connection.
Execute a SELECT statement that retrieves the BLOB data from the database.
Use the fetchone() method to retrieve the first row of the result set.
Get the BLOB data from the result set and store it in a variable.
Create a BytesIO object with the io library and pass the BLOB data to it.
Use the BytesIO object to read the data.
Once you have the data in memory, you can perform any necessary processing on it.
import psycopg2
conn = None
try:
# connect to the PostgreSQL server
'''Establishing Database connection. Fill up your local Database’s user and password.'''
conn = psycopg2.connect(
host='localhost',
dbname='mydb',
user='postgres',
password='user',
port=5432
)
cur = conn.cursor()
# Creating a table with a BLOB column
cur.execute(
"CREATE TABLE blob_datastore (s_no serial, file_name VARCHAR ( 50 ), blob_data bytea)")
# SQL query to insert data into the database.
insert_script = '''
INSERT INTO blob_datastore(s_no,file_name,blob_data) VALUES (%s,%s,%s);
'''
# psycopg2.Binary(File_in_Bytes) is used to convert the binary data.
BLOB_1 = psycopg2.Binary(
open(f"files\toast_flip.mp4", 'rb').read())
BLOB_2 = psycopg2.Binary(
open(f'files\ex.jpg', 'rb').read())
BLOB_3 = psycopg2.Binary(open(f'files\a-gif.gif', 'rb').read())
BLOB_4 = psycopg2.Binary(open(f'files\UNIT IV.pdf'', 'rb').read())
insert_values = [(1, 'toast_flip.mp4', BLOB_1),
(2, 'ex.jpg', BLOB_2),
(3, 'a-gif.gif', BLOB_3),
(4, 'UNIT UV.pdf', BLOB_4)]
for insert_value in insert_values:
cur.execute(insert_script, insert_value)
print(insert_value[0], insert_value[1],
"[Binary Data]", "row Inserted Successfully")
# SQL query to fetch data.
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")
cur.close()
except(Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.commit()
Output
1 toast_flip.mp4 [Binary Data] row Inserted Successfully 2 ex.jpg [Binary Data] row Inserted Successfully 3 a-gif.gif [Binary Data] row Inserted Successfully 4 UNIT IV.pdf [Binary Data] row Inserted Successfully 1 toast_flip.mp4 BLOB Data is saved in Current Directory 2 ex.jpg BLOB Data is saved in Current Directory 3 a-gif.gif BLOB Data is saved in Current Directory 4 UNIT UV.pdf BLOB Data is saved in Current Directory
Conclusion
Developers can easily handle BLOB data in PostgreSQL using Python with the help of the psycopg2 library. Using this, users can create tables with a BLOB column, insert binary data in the table thus created and retrieve the binary data from the table using the psycop2 library.
494 Views
