PostgreSQL is an object-relational database system that you can link with different programming languages. When creating a Python application, you can connect it to the PostgreSQL database. Besides, connecting Python to PostgreSQL is simplified, thanks to the psycopg2 PostgreSQL database adapter.

Once you install the adapter on your Python development environment, you can execute all PostgreSQL database tasks directly from the Python application. This guide covers different ways of using the psycopg2 database adapter when working with a Python script.

How to Use Psycopg2 to Connect Python to PostgreSQL

Psycopg2 is a Python module that you can install when working with Python. For this tutorial, we work on a Python virtual environment. To install psycopg2, use the pip command.

Let’s create a PostgreSQL database that we will work with from the Python script. Open your PostgreSQL console and create a database that you connect to from Python.

We have different use cases on how to use the psycopg2 module to connect Python to PostgreSQL.

• Connecting to the PostgreSQL Databases

The first thing that you must do when using the psycopg2 module is to create a connection to the PostgreSQL database from Python.

Start by importing the psycopg2 module to your Python script. Next, use the connect() to create an instance of the database that you want to connect to. Note that you must specify the database name, the user, and the password. In some cases, you can specify the host and the port.

Here’s an example:

With the simple database connection created, test it by executing the Python script to see if it returns the specified message after the connection object is returned.

• Creating a Table

To execute the PostgreSQL queries from your Python script, you must create a cursor object to use throughout the Python code. The following example utilizes the cursor() to execute a query to create a table in the database. Moreover, we enclose the code in a try-except-finally for easy debugging. Note that you should close the connection object and the cursor function at the end of the script.

Suppose we test our Python script. We confirm that we manage to create the table and get an error when trying to execute the script again since we already created the table.

The cursor object can be used with different methods. For instance, the following example inserts the values into the table which is created earlier. Still, we use the cursor object to query the database and select all elements that we inserted using the fetchall().

The output after executing the script confirms that we managed to query the table in our PostgreSQL database.

• Executemany

Another excellent function to use with the cursor object is the executemany(). The function is helpful when executing a query that affects the different parameter tuples in the provided sequence. For instance, we use it to recreate a given table by inserting multiple values from a specified sequence.

Suppose we use the fetchall() to query the values in the recreated table. The output gives all the values that are available in the recreated table.

• Fetchone

The function is used when you want to return the next row in a given query result set. If there is no next row, it returns None or displays the specified message to confirm that you reached the last row.

In the following example, we create a while loop to print all values from a given table. When we get to the last row, we break the loop and print a message confirming that there is no next row.

The output prints all values in the table and a custom message when the next row returns None.

• Fetchall

Unlike the fetchone that returns the next row in the query set, the fetchall returns all the remaining rows as a list of tuples. Suppose the specified query has no records. It returns an empty set.

Here’s the output of the fetched records which are returned as a list of tuples.

1. Parameterized Query

The psycopg2 also lets you use the parameterized queries where you create a placeholder for values instead of directly writing them in the query. That way, you enhance the security of your application.

The following example demonstrates how to use the parameterized queries to hold the values for an update PostgreSQL query.

The previous examples are the common ways that you can utilize the psycopg2. You can check for more functions from the documentation. But you now understand what the psycopg2 module is and how to use it on your Python project.

Conclusion

The psycopg2 is a Python module that facilitates the connection between Python and PostgreSQL. We’ve seen how to install the module, create a PostgreSQL database connection, and utilize the different objects and functions to work with a PostgreSQL database directly from Python. Use this foundation to skyrocket your next Python with the PostgreSQL database project.