Summary: in this tutorial, you will learn how to handle PostgreSQL BLOB data in Python using the psycopg database adapter.
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.
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
connect()function. - Then, create a
cursorobject from theconnectionobject. - 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 theconnectionobject.
The following write_blob() function reads binary data from a file specified by the path_to_file parameter and inserts it into the part_drawings table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | #!/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 part_drawings table.
1 2 3 | 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.
The following 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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[0] + '.' + blob[1], 'wb').write(blob[2]) # 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 images/blob folder.
In this tutorial, you have learned how to handle PostgreSQL BLOB data in Python using psycopg database adapter.