Introduction to Psycopg2 module in Python

Psycopg is the most popular PostgreSQL adapter used in  Python.  Its works on the principle of the whole implementation of Python DB API 2.0 along with the thread safety (the same connection is shared by multiple threads). It is designed to perform heavily multi-threaded applications that usually create and destroy lots of cursors and make a large number of simultaneous INSERTS or UPDATES. Psycopg features client-side and server-side cursors, asynchronous communication, and notification. Psycopg 2 is both Unicode and Python 3 friendly.

Installation:

The current psycopg2 implementation supports:

• Python versions from 3.6 to 3.10
• PostgreSQL server versions from 7.4 to 14
• PostgreSQL client library version from 9.1
• pgAdmin 4

For most of the available Operating Systems, the quickest way to install this package is through the wheel package available inthe PyPI library. We must make sure that we use the latest version of pip, which can be updated using the following command in the terminal.

$ pip install -U pip
$ pip install psycopg2-binary

This will install the pre-compiled binary version of the module which doesn't require the built or runtime prerequisites. Then we can import the psycopg2 package in the usual manner:

Basic module usage:

The basic use of Psycopg is in implementing the DB API 2.0 protocol to all the database adapters. Here is the basic interactive session of the basic commands.

Example 1: Program to establish a connection between python program and a PostgreSQL database.

 
 

👁 Image

 

Example 2: Creating a table using python

 

👁 Image
👁 Image

Example 3: Inserting data into the table:

👁 Image
👁 Image

Passing parameters to SQL queries

Python variables are converted to SQL values with Psycopg, Python determines the function used to convert the object into a string representation suitable for PostgreSQL.Passing parameters to an SQL statement happens in functions such as cursor.execute() by using %s as the placeholder into the SQL statement.

Example 4: Fetching the data from the database and displaying it into the terminal.

👁 Image

Example 5: Updating the data in the database.

👁 Image
👁 Image

Example 6: Deleting data from the database.

👁 Image
👁 Image

The main entry points of Psycopg are:

• The connect() function creates a new database session and returns a new instance of connection.
• The class connection encloses a database session. It allows to :
  • create new cursor instance
  • terminate transaction using commit() or rollback() methods.
• The cursor allows interaction with the database:
  • send commands to the database using execute() and executemany() methods.
  • retrieve data from the database using methods such as fetchone(), fetchmany(), fetchall(), or by iteration.