Save a image file on a Postgres database – Python

In this article, we are going to see how to save image files on a postgresql database using Python.

Psycopg2 is a driver, that is used, for interacting, with Postgres data, using the Python scripting language. It is, used to perform, CRUD operations on Postgres data. 

Data handled in applications can be in any format. For example, Strings, Numbers, and so on. At times, we need to upload, Images to the tables. In this article, let us learn, uploading images, to Postgres with Python.

BLOB and BYTEA format

Usually, Image fields, are BLOB data types. BLOBs are Binary Large Objects, which are, used to store, enormous amounts of data. Images, audio, and video files are usually stored, as BLOBs in databases. SQL, MySQL, and other databases, support BLOB datatype. But, Postgres does not supports, the BLOB format. Instead, it has a BYTEA format, for handling binary data. BYTEA is not suited, for storing, large amounts of data. A data column, of datatype BYTEA, can store up to, 1Gb of data. 

Let us see, the steps involved, in uploading images, to the Postgres table –

• Let us consider, a database CartoonDB, having a table ‘Cartoon’. It has fields cartoonID, name, cartoonImg. The field cartoon will be an INTEGER, the name will be TEXT, and, cartoonImg will be of BYTEA datatype.
• To perform CRUD operations, on Postgres data, using Python code, we need the psycopg2 library. Hence, install it, using the command – ‘pip install psycopg2’ at the terminal. 
• The psycopg2 adapter, has ‘connect()’ method, that allows connection, to the database, by passing username, password, hostname, and port as parameters. We can pass the Database Name as well. It returns a valid connection object, on a successful connection.
• Using the connection object, we can create a cursor object, that acts as a pointer, to the database. We can use, the cursor object, to fire SQL queries, on the table data. Using it, we will Create, and, upload Image records, in the table. 

The code for the same is as mentioned below –

Python3

# Import the required library
import psycopg2

# Method to create a connection object
# It creates a pointer cursor to the database
# and returns it along with Connection object
def create_connection():
    # Connect to the database
    # using the psycopg2 adapter.
    # Pass your database name ,# username , password ,
    # hostname and port number
    conn = psycopg2.connect(dbname='DisneyDB',
                            user='postgres',
                            password='admin',
                            host='localhost',
                            port='5432')
    # Get the cursor object from the connection object
    curr = conn.cursor()
    return conn, curr

def create_table():
    try:
        # Get the cursor object from the connection object
        conn, curr = create_connection()
        try:
            # Fire the CREATE query
            curr.execute("CREATE TABLE IF NOT EXISTS \
            cartoon(cartoonID INTEGER, name TEXT,\
            cartoonImg BYTEA)")

        except(Exception, psycopg2.Error) as error:
            # Print exception
            print("Error while creating cartoon table", error)
        finally:
            # Close the connection object
            conn.commit()
            conn.close()
    finally:
        # Since we do not have to do anything here we will pass
        pass

def write_blob(cartoonID,file_path,name):
    try:
        # Read data from a image file
        drawing = open(file_path, 'rb').read()
        # Read database configuration
        conn, cursor = create_connection()
        try:
            # Execute the INSERT statement
            # Convert the image data to Binary
            cursor.execute("INSERT INTO cartoon\
            (cartoonID,name,cartoonImg) " +
                    "VALUES(%s,%s,%s)",
                    (cartoonID,name, psycopg2.Binary(drawing)))
            # Commit the changes to the database
            conn.commit()
        except (Exception, psycopg2.DatabaseError) as error:
            print("Error while inserting data in cartoon table", error)
        finally:
            # Close the connection object
            conn.close()
    finally:
        # Since we do not have to do
        # anything here we will pass
        pass

# Call the create table method
create_table()
# Prepare sample data, of images, from local drive
write_blob(1,"F:\\TeachPytho\\GFGPhotos\\casper.jpg","Casper")
write_blob(2,"F:\\TeachPytho\\GFGPhotos\\archie.jpg","Archie")
write_blob(3,"F:\\TeachPytho\\GFGPhotos\\tintin.png","Tintin")
write_blob(4,"F:\\TeachPytho\\GFGPhotos\\pikachu.jpg","Pikachu")
write_blob(5,"F:\\TeachPytho\\GFGPhotos\\kungfupanda.jpg","Kung Fu Panda")

 After executing the code, we can view the data in PgAdmin4 tool. The table data is as shown below –