PostgreSQL Python: Handling BLOB Data

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.

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:

1. First, read data from a file.
2. Next, connect to the PostgreSQL database by creating a new connection object from the connect() function.
3. Then, create a cursor object from the connection object.
4. After that, execute the INSERT statement with the input values. For BLOB data, you use the Binary object of the psycopg module
5. Finally, commit the changes permanently to the PostgreSQL database by calling the commit() method of the connection object.

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.

#!/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()
Code language: Python (python)

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.

if __name__ == '__main__':
    write_blob(1, 'images/simtray.jpg', 'jpg')
    write_blob(2, 'images/speaker.jpg', 'jpg')
Code language: Python (python)

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.

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()
Code language: Python (python)

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.