Python psycopg2 – Read Image

Psycopg2 is the most widely used PostgreSQL database adapter for Python. The creation, update, modification, and deletion of PostgreSQL databases and tables, as well as the execution of queries and the retrieval of results, are all supported by it. This library also supports transaction management, server-side cursors, and other advanced features. It has been implemented in C language. 

PostgreSQL works with images in the form of binary data using the ‘BYTEA’ data type. However, Large volumes of data storage are not appropriate for BYTEA. It also doesn’t allow reading images directly but can work with the binary data representing the image.

Store and Retrieve Images in Python

To read the images in Python using PsycopImagesg2, you require the following things:

• Install PostgreSQL on Windows or Mac
• Install psycopg2 using terminal

pip install psycopg2

Reading Images from Local Directory

In this example, psycopg2 reads the images from the local folder in binary form and stores them on the PostgreSQL database. Let’s start by storing some sample images in the working directory in the images folder.

Input images

Step 1: Import the required libraries. i.e. psycopg for connecting to PostgreSQL and Python sys to handle file objects.

import psycopg2
import sys

Step 2: Create the user-defined function ‘connect_db()’ to connect to the PostgreSQL server and database. The connect() method is used to make the connection with the database. Provide the database name, user, password, and local host to connect to PostgreSQL.

psycopg2.connect(database='database_name', user='postgres_user',
password='password', host='localhost')

Step 3: Create a user-defined function ‘read_dir_img()’ to read the images from the directory in binary mode using the Python file object ‘inp’ variable. It implements the Python file open() method to read the inputted file path followed by the file read() method. Here the mode is set to ‘rb’, i.e., read binary.

inp = open("image_path", "mode")

Step 4: Set up a connection to the database and cursor object by calling connect_db().

Step 5: Execute the query using the cursor object’s execute() method to create a table for storing image data with columns id, label, and data.

cur.execute(
        "CREATE TABLE IF NOT EXISTS table_name(id SERIAL PRIMARY KEY, label TEXT, data BYTEA);")

Step 6: Get the image from the directory by calling the read_dir_image() function and convert it into binary data using the psycopg2 binary() method which accepts image data as a parameter.

bin = psycopg2.Binary(data)

Step 7: Insert the images into the table using the execute() method with insert query as the parameter.

cur.execute("INSERT INTO table_name(col_name1, col_name2) VALUES(value1, value2))

Step 8: Commit the changes and close the connection.

con.commit()
con.close()

Python3

# Import the required library
import psycopg2
import sys

# function to create connection with postgres database
def connect_db():
    # Connection variable set to null
    con = None

    try:
        # Connecting to database using the PostgreSQL adapter
        con = psycopg2.connect(database='postgres', user='postgres',
                               password='923441')

        # Creating the cursor object to run queries
        cur = con.cursor()

    # Calling rollback method if exception is raised
    except psycopg2.DatabaseError:
        if con:
            con.rollback()
        sys.exit(1)

    # returning the cursor and connection object
    return cur, con


# function to read image from the directory accepting integer parameter
def read_dir_img(num):
    # inp variable set to null
    inp = None

    try:
        # opening files from images folder for reading in binary mode
        inp = open("images/image"+str(num)+".png", "rb")
        image = inp.read()

        # returning read image
        return image

    # if exception raised
    except IOError:
        sys.exit(1)

    # closing input file object
    finally:
        inp.close()


# calling the connect_db function for connection & cursor object
cur, con = connect_db()
try:
    # Create table query
    cur.execute("CREATE TABLE IF NOT EXISTS img_table(
        id SERIAL PRIMARY KEY, label TEXT, data BYTEA)
        ")

    # reading directory images
    for i in range(1, 4):
        data = read_dir_img(i)

        # reading image data using psycopg library
        bin = psycopg2.Binary(data)

        # inserting images into database
        cur.execute("INSERT INTO img_table(label,data) VALUES (%s,%s)",
                    ("Image "+str(i), bin,))

except(Exception, psycopg2.Error) as e:
    # Print exception
    print(e)

finally:
    # Closing connection
    con.commit()
    con.close()

Output:

The data read from the directory and uploaded to img_table can be viewed in the pgAdmin4 application of PostgreSQL in the sequence: Server > PostgreSQL > Databases > postgres > Schemas > Tables > img_table (right click) > View/Edit Data > All Rows

Images inserted into the database

PostgreSQL database

Writing Image from Database

In this example, psycopg2 reads the binary images from the table img_table and stores the read image in the Output folder. 

Step 1: Create an output folder in the working directory.

Output Folder

Step 2:  Import the required libraries. i.e. psycopg for connecting to PostgreSQL and sys to handle file objects.

Step 3: Create the user-defined function connect_db() to connect to the PostgreSQL server and database using the connect() method.

Step 4: Create a user-defined db_img() function to store the images from the table. It accepts the image data and number as parameters to store the database image in the Output folder named as the number in the argument.

With the ‘out’ variable set to none, it implements the Python file open() method to read the input file path followed by the file write() method. Here ‘wb’ means write binary.

out = open('output/out'+str(num)+'.jpg', 'wb')

Step 5: Set up a connection to the database and cursor object by calling the connect_db(). 

Step 6: Iterate through all the rows of img_table using the Select query. Execute the query using the cursor object’s execute(query) method. 

cur.execute("SELECT data FROM img_table")

Step 7: By fetchone() method on the cursor object, get the image data and save it in the Output folder by calling the ‘db_img()’ function.

data = cur.fetchone()[0]

db_img(data, i)

Step 8: Close the connection.

Python3

# Import the required library
import psycopg2
import sys

# function to create connection with postgres database
def connect_db():
    # Connection variable set to null
    con = None

    try:
        # Connecting to database using the PostgreSQL adapter
        con = psycopg2.connect(database='postgres', user='postgres',
                               password='923441')

        # Creating the cursor object to run queries
        cur = con.cursor()

    # Calling rollback method if exception is raised
    except psycopg2.DatabaseError:
        if con:
            con.rollback()
        sys.exit(1)

    # returning the cursor and connection object
    return cur, con


# function to store the image which is read from the table
def db_img(data, num):
    # out variable set to null
    out = None

    try:
        # creating files in output folder for writing in binary mode
        out = open('output/out'+str(num)+'.jpg', 'wb')

        # writing image data
        out.write(data)

    # if exception raised
    except IOError:
        sys.exit(1)

    # closing output file object
    finally:
        out.close()


# calling the connect_db function for connection & cursor object
cur, con = connect_db()
try:
    # Cursor object holding all image data from table
    cur.execute("SELECT data FROM img_table")
    for i in range(1, 4):

        # fetchone method returns a tuple object of next row of query result set
        # image data is in first column after Select query execution, so [0] index
        data = cur.fetchone()[0]

        # the image data is written to file using db_img() for viewing
        db_img(data, i)
except(Exception, psycopg2.Error) as e:
    # Print exception
    print(e)

finally:
    # Closing connection
    con.close()

Output:

This will be the final folder structure after executing the above program. The images are stored in the output folder.

The images read from img_table are stored in the output folder

Updated Image from Database

In this example, the third row of the img_table is updated by reading a new picture from the Update folder using psycopg2, and the new image data is read and saved in the Output folder.

Store the new image in the update folder with the name ‘updateimage.png’.

Update image folder

Step 1: Import the required libraries. i.e. psycopg for connecting to PostgreSQL and sys to handle file objects.

Step 2: Create the user-defined function connect_db() to connect to the PostgreSQL server and database using the connect() method.

Step 3: Read the images from the directory in binary mode using the Python file object ‘inp’ variable

Step 4:  Set up a connection to the database and cursor object by calling the connect_db(). 

Step 5: Get the image from the directory by calling the read_dir_image() function and convert it into binary data using the psycopg2 binary() method which accepts image data as a parameter.

Step 6: Execute the query using the cursor object’s execute(query) method to update the third row of the img_table.

UPDATE img_table SET label=%s, data=%s WHERE id=3;

Step 7: The row gets updated in the img_table. Next, to read the new image from the table using execute(query) method, add the following Select query as the argument.

SELECT data FROM img_table WHERE id=3;

Step 8: Use the fetchone() method on the cursor object, to get the image data and the write() method to save the new image into the file.

Step 9: Commit the changes and close the connection.

Python3

# Import the required library
import psycopg2
import sys

# function to create connection with postgres database
def connect_db():
    # Connection variable set to null
    con = None

    try:
        # Connecting to database using the PostgreSQL adapter
        con = psycopg2.connect(database='postgres', user='postgres',
                               password='923441')

        # Creating the cursor object to run queries
        cur = con.cursor()

    # Calling rollback method if exception is raised
    except psycopg2.DatabaseError:
        if con:
            con.rollback()
        sys.exit(1)

    # returning the cursor and connection object
    return cur, con


# function to read image from the directory
def read_dir_img():
    # inp variable set to null
    inp = None

    try:
        # opening files from images folder for reading in binary mode
        inp = open("update/updateimage.png", "rb")
        image = inp.read()

        # returning read image
        return image

    # if exception raised
    except IOError:
        sys.exit(1)

    # closing input file object
    finally:
        inp.close()


# calling the connect_db function for connection & cursor object
cur, con = connect_db()
try:
    data = read_dir_img()

    # reading image data using psycopg library
    bin = psycopg2.Binary(data)

    # Update table query
    cur.execute("UPDATE img_table SET label=%s, data=%s WHERE id=3;",
                ("Update Image 3", bin))

    # Cursor object holding image data from table
    cur.execute("SELECT data FROM img_table WHERE id=3;")
    data = cur.fetchone()[0]

    # out variable set to null
    out = None

    # the updated image data is stored in a file
    out = open('output/updatedimage3.jpg', 'wb')

    # writing image data
    out.write(data)
    out.close()

except(Exception, psycopg2.Error) as e:
    # Print exception
    print(e)

finally:
    # Closing connection
    con.commit()
    con.close()

Output:

Rows of img_table after update

The updated image from img_table is saved to the output folder