PostgreSQL Python: Handling Binary Data
Summary: in this tutorial, you will learn how to store binary data in the PostgreSQL database using Python.
This tutorial picks up from where theCall Stored Procedures Tutorial left off.
Standard SQL defines aBLOB as the binary large object for storing binary data in the database. Using theBLOB data type, you can store binary data such as images, documents, and so on in a table.
PostgreSQL does not support BLOB data type. Instead, it uses theBYTEA data type for storing binary data.
Let’s take a look at thepart_drawings table.
Thepart_drawings table stores the pictures of parts in thedrawing_data column. We will show you how to insert binary data into this column and read it back.
Insert binary data into a table
To insert binary 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 theINSERT statement with the input values. For binary data, use the
Binaryobject of thepsycopg2module - Finally, commit the changes permanently to the PostgreSQL database by calling the
commit()method of theconnectionobject.
The followingwrite_blob() function reads binary data from a file specified by thepath_to_file parameter and inserts it into thepart_drawings table.
import psycopg2from configimport load_configdef write_blob(part_id, path_to_file, file_extension): """ Insert a BLOB into a table """ # read database configuration params= load_config() # read data from a picture data= open(path_to_file,'rb').read() try: # connect to the PostgresQL database with psycopg2.connect(**params) as conn: # create a new cursor object with conn.cursor() as cur: # 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(data))) conn.commit() except (Exception, psycopg2.DatabaseError)as error: print(error)if __name__ == '__main__': write_blob(1,'images/input/simtray.png','png') write_blob(2,'images/input/speaker.png','png')Read binary data from a table
The steps of reading binary data from a table are similar to the steps of querying data from a table. After fetching binary data from the table, you can save it to a file, output it to the web browser, and so on.
The followingread_blob() function selects BLOB data from thepart_drawings table based on a specified part id and saves the binary data to a file.
import psycopg2from configimport load_configdef read_blob(part_id, path_to_dir): """ Read BLOB data from a table """ # read database configuration config= load_config() try: # connect to the PostgresQL database with psycopg2.connect(**config) as conn: with conn.cursor() as cur: # 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() # write blob data into file open(path_to_dir+ blob[0]+ '.' + blob[1],'wb').write(blob[2]) except (Exception, psycopg2.DatabaseError)as error: print(error)if __name__ == '__main__': read_blob(1,'images/output/') read_blob(2,'images/output/')The following snippet reads the binary data of the parts with id values 1 and 2 and saves the binary data to theimages/output directory.
Download the project source code
In this tutorial, you have learned how to write binary data to a table and read it back using Python.
Last updated on