Store Blob Datatype in Postgres with Python

Introduction

In this article we learn how to store the Blob datatype in Postgres with Python. We’ll start by looking at the most simple way we might handle something like an image in our web database application by storing the file that holds the data in a file on the server and only saving the file name and maybe the path in PostgreSQL. After learning that method, we’ll see how to store an image directly in Postgres. Finally, we will compare these two methods and look at the pros and cons.

Save image file name in Postgres

The most simple method to store an image for later display on a page is to store the image in a folder on the server and store the path to that image in a text column in PostgreSQL, the text column being set to only take enough bytes to store the file name and maybe the path. One primary benefit with this method is increased portability because the blob datatype is not available or works differently in other database systems. We’ll look at another benefit later.

Store blob datatype in Postgres

The second method we’ll learn is to store a blob datatype in PostgreSQL using Python and then display that image, song, video, PDF, or other object type that probably has a large amount of data. Part of this method will involve opening a file to get the data from that file so we can save that data in Postgres as a “blob”. Below we will go deeper into this methodology.

Prerequisites

Following are a few types of tech that will be useful for you in understanding these lessons. Onward!

Python coding

We used Python 3.8 for this tutorial, which is the latest as of the time when we wrote this. You are not obligated to use the latest, however, if you want to, you can download it free here. We prefer staying as up to date with the latest as possible.

Writing SQL

Experience with writing simple SQL queries. In this tutorial, we stick to the most simple queries, including use of INSERT and WHERE. For managing Postgres, we prefer the dBeaver free database management tool for ease, query building and testing, and more efficiency.

Naming conventions

Article about naming conventions shows why and how you may wish to prefix your variables, column (field) names, table names, etc. as you see in this article. For example, naming “tvariable” with a “t” prefix to designate it as “text” and “tbl_” before table names in order to easily distinguish those objects as tables. The tutorial goes a bit deeper, as well, talking about how to name variables based on a logical hierarchy system. This process increases readability for you and anyone who inherits your code.

Open() for Python

Before learning to script for the two methods mentioned above for storing blob data in PostgreSQL, we’ll look at the Python function called open(). It can be used to create a file (w), open for write (w), or open a file for reading (r). In Python, Open requires use of the “psycop2g” library.

Syntax of Open

file = open('[path/filename.extension]', '[r OR w]')

Analysis: For the first parameter, we give the open function a file path and file name. For the next parameter, “r” or “w”, which means read or write. The open function either fills a Python array type with the data from the file designated as the “path/filename.extension” OR, if we chose “w” (write), creates and/or writes any data we want to the specified file.

Save file name in Postgres

The first method we’ll learn is to store an image by storing only the file name in the database. As discussed above, a benefit to this approach is increased portability because the blob datatype may not be available or works different in some databases that are not PostgreSQL. Another issue is database size. Storing blobs in your database can cause bloat because blob data types tend to be much larger than other types. For example, a boolean datatype is only 1 byte while a text type might be as large as 255 bytes, where a blob type for a small image might be 28,000 bytes or more!

Example for storing file name

import psycopg2

def store_filename(id_product, t_path):
    s = ""
    s += "INSERT INTO tbl_products"
    s += "("
    s += "id_product"
    s += ", t_path"
    s += ") VALUES ("
    s += "(%id_product)"
    s += ", '(%t_path)'"
    s += ")"
    db_cursor.execute(s, [id_product, t_path])

Analysis

  • import psycopg2: Python’s psycopg2 library allows us to create connections and cursors to read/write from/to Postgres.
  • def store_filename: This function has the code for storing the filename (t_name_file) into Postgres. The function has 2 parameters; id_product and t_path. The SQL SELECT statement uses id_product in the WHERE clause to filter for a specific product. t_path is used to save what folder and filename to find the data in.
  • db_cursor.execute: Insert the product ID and path/filename into a row in our Postgres database.

Save the Blob datatype in Postgres

Another way to store large data for a web application is to store the data for that large object or file in a column in Postgres. Most popular database systems support the blob datatype. Earlier, we called the “store in folder on web server” approach as “simple,” however, some say that the method to store as a Blob datatype from Postgres with Python is the more elegant method. Let’s look at why.

Read file with Python

We’ll start with opening an image file for read and getting the data from that file so we can put the data into a blob column in Postgres.

Going a bit further with the syntax we showed you above for the open function, we’ll show you how to actually open an image file and get the data from that file.

    t_path_blob_data = "data/image.png"
    blob_data = open(t_path_blob_data, 'r')

Analysis

  • t_path_blobl_data: Where the image exists on our web server.
  • open: Here we tell the open function to READ (‘r’) the file at “t_path_blob_data”.

Example of Python blob storing

import psycopg2

def store_blob(id_product, blob_data):
    s = ""
    s += "INSERT INTO tbl_products"
    s += "("
    s += "id_product"
    s += ", blob_data"
    s += ") VALUES ("
    s += "(%id_product)"
    s += ", '(%blob_data)'"
    s += ")"
    db_cursor.execute(s, [id_product, blob_data])

Analysis

  • def store_blob: We placed our code for storing blob data into Postgres in this function. It has has two parameters; id_product and blob_data. If you study the SQL INSERT statement in the above example, you will see id_product is plugged into the part of the script’s INSERT clause that defines a column name. The blob_data parameter does the same.
  • execute: This function is used to create a new row in our Postgres table to store product id and the blob data we are saving.

Source code to store a blob in Postgres using Python

import flask # Python library for various functionalities
import psycopg2 # Python library for database connection

# connect to PostgreSQL
t_host = "PostgreSQL database host address" # this will be either "localhost", a domain name, or an IP address.
t_port = "5432" # default port for postgres server
t_dbname = "database name"
t_user = "database user name"
t_pw = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_user, password=t_pw)
db_cursor = db_conn.cursor()

@app.route("/main")

def read_blob_file(t_path_blob_data):
    # read the data from file on server into blob_data variable.
    blob_data = open(t_path_blob_data, 'r')
    # pass that variable on to def_main that called this function.
    return blob_data

def store_blob(id_product, blob_data):
    # write BLOB to Postgres.
    s = ""
    s += "INSERT INTO tbl_products"
    s += "("
    s += "id_product"
    s += ", blob_data"
    s += ") VALUES ("
    s += "(%id_product)"
    s += ", '(%blob_data)'"
    s += ")"
    # error trapping.
    try:
        db_cursor.execute(s, [id_product, blob_data])
        db_cursor.close()
    except (Exception, psycopg2.DatabaseError) as error:
        # error occurred.
        print(error)
    finally:
        if db_conn is not None:
            db_conn.close()

def main():
    # define which file has our image data.
    t_path_blob_data = "data/image.png"
    # get blob data from an image file.
    blob_data = read_blob_file(t_path_blob_data)
    # send product id and blob_data to the store_blob function.
    store_blob(142, blob_data)

Conclusion

In this tutorial we studied two of the most useful and oft-used methods to store images or other large files in Postgres. First we examined and learned how to use Python’s “open” function. We looked at two methods for accomplishing our goal: (1) how to store the path and name of the file in the database. (2) then how to store the blob datatype in Postgres with Python. Finally, we provided analysis, examples, and commented source code throughout the lesson.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.