Open In App

Read and insert bytea columns using psycopg2

Improve
Improve
Like Article
Like
Save
Share
Report

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
  
# Connect to the PostgreSQL database
conn = psycopg2.connect(database="mydb", user="myuser",
                        password="mypassword", host="localhost", port="5432")
  
# Open a cursor to perform database operations
cur = conn.cursor()
  
# Read the binary data from a file
with open('image.jpg', 'rb') as f:
    data = f.read()
  
# Insert the binary data into the bytea column
cur.execute("INSERT INTO mytable (image) VALUES (%s)", (data,))
  
# Commit the changes to the database
conn.commit()
  
# Close the cursor and connection
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
  
# Connect to the PostgreSQL database
conn = psycopg2.connect(database="mydb", user="myuser",
                        password="mypassword", host="localhost", port="5432")
  
# Open a cursor to perform database operations
cur = conn.cursor()
  
# Select the binary data from the bytea column
cur.execute("SELECT image FROM mytable WHERE id = %s", (1,))
data = cur.fetchone()[0]
  
# Write the binary data to a file
with open('image_copy.jpg', 'wb') as f:
    f.write(data)
  
# Close the cursor and connection
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
Previous
Next
Share your thoughts in the comments
Similar Reads