Python PostgreSQL Tutorial Using Psycopg2

This Python PostgreSQL tutorial demonstrates how to develop Python database applications with the PostgreSQL database server.

In Python, we have serval module available to connect and work with PostgreSQL. the following are the list.

• Psycopg2
• pg8000
• py-postgresql
• PyGreSQL
• ocpgdb
• bpgsql
• SQLAlchemy. SQLAlchemy needs any of the above to be installed separately.

Note: Above all interfaces or modules are adhere to Python Database API Specification v2.0 (PEP 249). This API has been designed to encourage and maintain the similarity between the Python modules that are used to access databases. In other words, the syntax, method and the way of access database are the same in all the modules.

For this tutorial, we stick to the Psycopg2. Why?

• Psycopg2 is the most popular python driver for PostgreSQL.
• It is required for most Python and Postgres frameworks.
• Actively maintained and support for the major version of python i.e. Python 3 and Python 2.
• It is thread-safe (threads can share the connections). It was designed for heavily multi-threaded applications.

This tutorial mainly focuses on: –

• Installing Psycopg2 and use its API to access the PostgreSQL database.
• It then takes you through data insertion, data retrieval, data update, and data deletion.
• Next, it will cover transaction management, connection pooling, and error-handling techniques to develop robust python programs with PostgreSQL.

Let see each section now.

Install Psycopg2 using the pip command

You need to install Psycopg2 on your machine to use PostgreSQL from Python. This module is available on pypi.org.

Using pip command, you can install Psycopg2 on any operating system including Windows, MacOs, Linux, and Unix and Ubuntu. Use the following pip command to install Psycopg2.

pip install psycopg2

You can also install a specific version using the following command.

pip install psycopg2=2.7.5

If you are facing pip install error like “connection error:  [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)”. You can resolve this error by setting pypi.org and files.pythonhosted.org as trusted hosts. Please try following pip command to install Psycopg2.

python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2

Verify Psycopg2 installation

You should get the following messages after running above command.

• Collecting psycopg2
• Downloading psycopg2-2.7.5
• Installing collected packages: psycopg2
• Successfully installed psycopg2-2.7.5

Python PostgreSQL Database Connection

In this section, we will learn how to connect to PostgreSQL through python using psycopg2. You need to know the following detail of the PostgreSQL to perform the connection.

Arguments required to connect PostgreSQL database from Python

• The username you use to work with PostgreSQL, The default username for the PostgreSQL database is Postgres.
• Password – Password is given by the user at the time of installing the PostgreSQL.
• Host Name  – is the server name or Ip address on which PostgreSQL is running. if you are running on localhost, then you can use localhost, or it’s IP i.e., 127.0.0.0
• Database Name – Database name to which you want to connect. Here we are using Database named “postgres_db”.

Steps to connect PostgreSQL through python

• Use the connect() method of psycopg2 with required arguments to connect PostgreSQL.
• Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python.
• Close the Cursor object and PostgreSQL database connection after your work completes.
• Catch Exception if any that may occur during this process.

Python Example to Connect PostgreSQL Database

To connect the PostgreSQL database and perform SQL queries you must know the database name you want to connect. If you have not created any database, I advise you to create one before proceeding further.

import psycopg2

try:
    connection = psycopg2.connect(user = "sysadmin",
                                  password = "pynative@#29",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "postgres_db")

    cursor = connection.cursor()
    # Print PostgreSQL Connection properties
    print ( connection.get_dsn_parameters(),"\n")

    # Print PostgreSQL version
    cursor.execute("SELECT version();")
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

You should get the following output after connecting to PostgreSQL from Python

{'user': 'postgres', 'dbname': 'pynative_DB', 'host': '127.0.0.1', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '1', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} 

You are connected to -  ('PostgreSQL 10.3') 

PostgreSQL connection is closed

Understand the PostgreSQL Database connection Code in detail

import psycopg2

• This line imports the psycopg2 module in our program. Using the classes and method defined psycopg2 module we can communicate with PostgreSQL.

from psycopg2 import Error

• Using Error class of psycopg2, we can handle any database error and exception that may occur while working with PostgreSQL from Python. Using this approach we can make our application robust.
• This module helps us to understand the error in detail. It returns an error message and error code.

psycopg2.connect()

• Using the connect() method we can create a connection to a PostgreSQL database instance. This returns a PostgreSQL Connection Object. this connection is thread-safe and can be shared among many threads.
• The connect() method accepts various arguments that we discussed above.
• In our example, we passed following connection arguments to connect PostgreSQL.
  user = "postgres", password = "pass@#29", host = "127.0.0.1", port = "5432",database = "postgres_db"

cursor = connection.cursor()

• Using connection.cursor() we can create a cursor object which allows us to execute PostgreSQL command through Python source code.
• We can create as many cursors as we want from a single connection object. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors.
• Cursors are not thread-safe.

After this, we printed PostgreSQL Connection properties using a connection.get_dsn_parameters().

cursor.execute()

• Using cursor’s execute method we can execute a database operation or query.
• Execute method takes a SQL query as a parameter.
• We can retrieve query result using cursor methods such as fetchone(), fetchmany(), fetcthall().
• In our example, we are executing a SELECT version(); query to fetch the PostgreSQL version.

try-except-finally block

• We placed all our code in the try-except block to catch the database exceptions and error that may occur during this process.

cursor.close()  and connection.close()

• It is always good practice to close the cursor and connection object once your work gets complete to avoid database issues.

Python PostgreSQL Create Table

In this section, we will learn how to create a database table in PostgreSQL from Python code using Psycopg2. I have created an example in python to create a Mobile table in PostgreSQL.

Steps for creating a table in PostgreSQL in Python

• Prepare a create table query.
• Next, connect to PostgreSQL using a psycopg2.connect(). I have explained the PostgreSQL connection code at the start of this article.
• Execute the query using a cursor.execute()
• In the end, Close the database connection and cursor object.

Let see the example now.

import psycopg2
from psycopg2 import Error

try:
    connection = psycopg2.connect(user = "postgres",
                                  password = "pass@#29",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "postgres_db")

    cursor = connection.cursor()
    
    create_table_query = '''CREATE TABLE mobile
          (ID INT PRIMARY KEY     NOT NULL,
          MODEL           TEXT    NOT NULL,
          PRICE         REAL); '''
    
    cursor.execute(create_table_query)
    connection.commit()
    print("Table created successfully in PostgreSQL ")

except (Exception, psycopg2.DatabaseError) as error :
    print ("Error while creating PostgreSQL table", error)
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

You Should get the following output

Table created successfully in PostgreSQL
PostgreSQL connection is closed

Python PostgreSQL CRUD Operations

We know the table, and it’s column details now let’s move to the crud operations.

In this section, We will learn how to perform PostgreSQL CRUD operations from Python. Insert, Update and Delete query from python using Psycopg2 to manipulate PostgreSQL database.

Let see each section now.

• Select data from PostgreSQL Table from Python – In this article, we will learn how to execute a PostgreSQL SELECT query from a Python application to fetch data from the database table. We will also learn how to use fetchall(), fetchmany() and fetchone() method to fetch limited rows from the table.
• Insert data into the PostgreSQL Table from Python – In this section, We will learn how to execute Insert Query from python application to insert row or record to the PostgreSQL table.
• Update data of PostgreSQL table from Python – In this section, We will learn how to execute Update Query from python application to update row or record of a PostgreSQL table.
• Delete data from PostgreSQL table from Python – In this section, We will learn how to execute Delete Query from python application to Delete row or record from a PostgreSQL table.

Call PostgreSQL Function and Stored Procedure in Python

PostgreSQL function and the Stored procedure can perform different operations it can be data manipulation or data retrieval. We can execute such functions from Python.

In this section, we will learn how to execute the PostgreSQL function and Stored procedure in Python. Refer How to execute PostgreSQL function and stored procedure from Python.

Python PostgreSQL Transaction management

In this article, we will see how to manage PostgreSQL transactions from Python using psycopg2.

• We will see how to use commit and the rollback method of connection class to manage transactions from Python.
• We will also see how to change PostgreSQL transaction isolation level from Python.

Refer our complete guide on How to manage PostgreSQL transaction from Python.

Python PostgreSQL Connection Pooling

In this section, I will let you know what connection pool is and how to implement a PostgreSQL database connection pool using Psycopg2 in Python.

• Using Psycopg2, we can implement a connection pool for a simple application and also for multithreaded applications.
• Use the Connection pool to increase the speed and performance of database-centric applications.
• Using Psycopg2, we can implement a simple connection pool for single threaded applications and Threaded connection pool for a multithreaded environment.

Refer our complete guide on Python PostgreSQL Connection Pooling Using Psycopg2.

Python Database Programming Quiz and Exercise Project

Solve our Free Python Database programming Quiz to test your Python database concepts.

Solve our Free Python Database Exercise project to Practice and master the Python Database operations using PostgreSQL

• In this exercise project, We will implement the Hospital Information System. Which covers above all topics.
• In this Python database exercise, we will do database CRUD operations From Python. Exercise also covers transaction management and error-handling techniques.

Reference: Psycopg – PostgreSQL database adapter for Python

That’s it. Folks Let me know your comments in the section below.