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.