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

# 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.