Read and insert bytea columns using psycopg2
PostgreSQL is an open-source database management system. It allows users to store data in various formats, including bytea (binary data) format. psycopg2 is a popular Python library that provides an interface for working with PostgreSQL databases. Users can read and insert bytea columns using psycopg2.
- Bytea: Bytea is a PostgreSQL data type that is used to store binary data, such as images, audio files, etc.
- Psycopg2: Psycopg2 is a Python library that provides an interface for working with PostgreSQL databases.
It allows you to execute SQL queries, fetch data, and perform other database-related operations.
Inserting Bytea Columns
Here we are going to insert Bytea columns in to our database.
Note: First you must create the table in your database.
image.jpg
Python3
import psycopg2
conn = psycopg2.connect(database = "mydb" , user = "myuser" ,
password = "mypassword" , host = "localhost" , port = "5432" )
cur = conn.cursor()
with open ( 'image.jpg' , 'rb' ) as f:
data = f.read()
cur.execute( "INSERT INTO mytable (image) VALUES (%s)" , (data,))
conn.commit()
cur.close()
conn.close()
print ( "Binary data inserted successfully!" )
|
Output:
Binary data inserted successfully!
Database after inserting the binary data
Here, we first connect to the PostgreSQL database using psycopg2. Then, we open a cursor to perform database operations. We read the binary data from a file using Python’s built-in open() function in binary mode. We insert the binary data into a bytea column using the cur.execute() method and commit the changes to the database. Finally, we close the cursor and connection.
Reading binary data from a bytea column
Python3
import psycopg2
conn = psycopg2.connect(database = "mydb" , user = "myuser" ,
password = "mypassword" , host = "localhost" , port = "5432" )
cur = conn.cursor()
cur.execute( "SELECT image FROM mytable WHERE id = %s" , ( 1 ,))
data = cur.fetchone()[ 0 ]
with open ( 'image_copy.jpg' , 'wb' ) as f:
f.write(data)
cur.close()
conn.close()
print ( "Binary data read successfully!" )
|
Output
Binary data read successfully!
Here, we first connect to the PostgreSQL database using psycopg2. Then, we open a cursor to perform database operations. We select the binary data from a bytea column using the cur.execute() method and fetch the first row of data using the cur.fetchone() method. We write the binary data to a file using Python’s built-in open() function in binary mode. Finally, we close the cursor and connection.
Last Updated :
16 Mar, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...