July 31, 2023
Summary: in this tutorial, you will learn how to handle PostgreSQL BLOB data in Python.
Standard SQL defines BLOB as the binary large object for storing binary data in the database. With the BLOB data type, you can store the content of a picture, a document, etc. into the table.
PostgreSQL does not support BLOB but you can use the BYTEA data type for storing the binary data.
Let’s take a look at the
part_drawings table stores the pictures of parts in the
drawing_data column. We will show you how to insert binary data into this column and read it back.
Insert BLOB into a table
To insert BLOB data into a table, you use the following steps:
- First, read data from a file.
- Next, connect to the PostgreSQL database by creating a new connection object from the
- Then, create a
cursorobject from the
- After that, execute the INSERT statement with the input values. For BLOB data, you use the
Binaryobject of the psycopg module
- Finally, commit the changes permanently to the PostgreSQL database by calling the
commit()method of the
write_blob() function reads binary data from a file specified by the
path_to_file parameter and inserts it into the
#!/usr/bin/python import psycopg2 from config import config def write_blob(part_id, path_to_file, file_extension): """ insert a BLOB into a table """ conn = None try: # read data from a picture drawing = open(path_to_file, 'rb').read() # read database configuration params = config() # connect to the PostgresQL database conn = psycopg2.connect(**params) # create a new cursor object cur = conn.cursor() # execute the INSERT statement cur.execute("INSERT INTO part_drawings(part_id,file_extension,drawing_data) " + "VALUES(%s,%s,%s)", (part_id, file_extension, psycopg2.Binary(drawing))) # commit the changes to the database conn.commit() # close the communication with the PostgresQL database cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close()
The following snippet calls the
write_blob ()function twice to insert two new parts with the corresponding binary data from the picture files into the
if __name__ == '__main__': write_blob(1, 'images/simtray.jpg', 'jpg') write_blob(2, 'images/speaker.jpg', 'jpg')
Read BLOB in the table
The steps of reading BLOB from a table are similar to the steps of querying data from a table. After fetching binary data from the table, we can save to a file, output it to the web browser, etc.
read_blob() function selects BLOB data from the
part_drawings table based on a specified part id and saves the BLOB data to a file.
def read_blob(part_id, path_to_dir): """ read BLOB data from a table """ conn = None try: # read database configuration params = config() # connect to the PostgresQL database conn = psycopg2.connect(**params) # create a new cursor object cur = conn.cursor() # execute the SELECT statement cur.execute(""" SELECT part_name, file_extension, drawing_data FROM part_drawings INNER JOIN parts on parts.part_id = part_drawings.part_id WHERE parts.part_id = %s """, (part_id,)) blob = cur.fetchone() open(path_to_dir + blob + '.' + blob, 'wb').write(blob) # close the communication with the PostgresQL database cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close()
The following snippet reads the binary data of the parts with id value 1 and 2, and save the binary data to the
In this tutorial, you have learned how to handle PostgreSQL BLOB data in Python using psycopg database adapter.