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()

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.

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.

Output:

Rows of img_table after update

The updated image from img_table is saved to the output folder