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.
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!
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.
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.
It allows you to execute SQL queries, fetch data, and perform other database-related operations.
Contact Us