PostgreSQL CRUD example in Python with Psycopg2 (Part 1)

Introduction

This is the first part of a tutorial series providing a PostgreSQL crud example in python using the Psycopg2 adapter. Psycopg is a PostgreSQL adapter created for Python programming language and the wrapper for the official PostgreSQL client library. Psycopg is currently the most popular adapter for PostgreSQL and can be used to create, update, read and delete Postgres data.

Prerequisites

• Python 3 must be properly installed and working on the device; note that Python 3 is required as Python 2.7 has been deprecated.

• The Python driver for PostgreSQL must be properly installed and working in order for a PostgreSQL crud example in python using the Psycopg2 adapter to work.

• The PostgreSQL database cluster must be properly installed on the machine. To verify the installation and the current version, execute the psql -V command in the terminal.

Create a test database for Psycopg2

A database must be created for the CRUD examples to work. Connect to the psql command line interface for Postgres using the psql command and name the database demo on the Postgres server.

psql: error

If a user or database for Postgres hasn’t been setup yet, or the correct password was not entered, the following error occurs will occur while attempting to connect to psql:

In this case, try connecting to one of the built-in PostgreSQL databases, like template, with the username by passing it to the -U option as shown here:

Use the ‘CREATE DATABASE’ SQL keyword

Once inside of psql, use the following CREATE DATABASE syntax to create a database that can be use to perform the CRUD operations with the psycopg2 Python adapter:

The above command should return a CREATE DATABASE response resembling the following screenshot:

Now type the \q command to exit the psql interface and return to the terminal.

Installing psycopg2

There are multiple ways of installing the Psycopg2 adapter. The first way is to execute the pip3 install command for PIP. The second method is to download the wheel package and install it from the source using the following command:

Install Psycopg2 using PIP

However, the easiest way to install psycopg2 is use the pip command used to install the package from the Python package Index or the pypi.

Using the terminal in Linux or mac, activate the needed virtual environment tools used for the Python packages with the following command:

Now use the pip command to install the psycopg2:

NOTE: This will install the latest version of psycopg2. If a specific version is desired, add =2.7.5 after the word “psycopg2.”

Connect PostgreSQL using psycopg2

SQL queries can be performed by just connecting to the PostgreSQL database cluster using the Python 3.

A Python file can be created in any code editor or IDE that supports the Python extension. Alternatively, the virtual environment can be used just as easily to run Python 3.

Enter the following command in the virtual environment module:

This command will connect to the Python programming interface or the interactive Python console.

Now create a file with the .py Python file extension and put the following code in the file:

NOTE: It is good practice to close the cursor and connection objects once finished, to avoid possible memory leaks.

Once the file has been ran using the python3 command, the result should resemble the following:

Postgres ‘CREATE TABLE’ example

A table for the CRUD examples used in this tutorial series must be created. Use the following SQL statement in psql to create a book table on the database:

Alternatively, execute the statement in a psycopg2 cursor object in a Python script using the psycopg2 adapter.

PostgreSQL: Create a table using the psycopg2 adapter

Create the table by first importing the necessary libraries and connecting to PostgreSQL, as done previously, by executing the following code:

The above code uses the extensions and sql libraries from the psycopg2 adapter module.

Now, set the isolation level so that the SQL string can be executed using the following sql.SQL() method:

The results should resemble the following:

NOTE: Using sql.Identifier() to execute SQL statements in psycopg2 is typically considered safer and will help to prevent SQL injection attacks.

When the above code is executed in a Python script using the python3 command, a table called book in the demo database will be created. Execute the \dt command in psql to verify the table has been created.

Use to psycopg2 insert PostgreSQL record data

Now an example in psycopg2 will be created to insert data into a PostgreSQL table using the Python driver for Postgres. However, before proceeding, be certain to specify the user, the database and the table in PostgreSQL that will execute the Python command.

The following CRUD example uses a try-except indentation block to catch any psycopg2 errors:

The result displayed in Python terminal should resemble the following:

NOTE: If there is an error, it will set the connection object instance to None. Otherwise, the connection in the finally: indentation block will be closed.

Once completed, go to the psql console to monitor the PostgreSQL data. To confirm the data was actually inserted into the table, use the database credentials given inside the configuration of the Python file.

Now use the following SELECT statement example to get data from a PostgreSQL table:

Conclusion

This was part one of a tutorial series providing a PostgreSQL crud example in python for inserting data in PostgreSQL using the psycopg2 Python driver. The article explained that a database must be created for the CRUD examples to work and how to create a test database for Psycopg2. The tutorial also covered how to use the ‘CREATE DATABASE’ SQL keyword, install psycopg2 and connect to PostgreSQL using psycopg2. Finally, the tutorial provided a Postgres ‘CREATE TABLE’ example, how to create a table using the psycopg2 adapter and how to use psycopg2 to insert PostgreSQL record data. Remember that it is imperative that the Python driver for PostgreSQL be properly installed in order to be able to connect to the PostgreSQL database and create a crud example.

Just the Code