Introduction to Psycopg2 Module in Python

We know that Python is a programming language used for accomplishing various tasks in fields such as Data Analysis, AI, Machine Learning and so on. And obviously, there are different modules with special functions which help us to do the job.

Similarly, Python code is made to interact with a PostgreSQL database using a module known as the “Psycopg2 module”. It is a popular PostgreSQL database adapter for Python. This module provides us with a set of functions and classes that help us with database connectivity, result handling as well as query execution.

Key Features of the Psycopg2 Module in Python

• Database Connection: The Psycopg2 module in Python comes with a “connect()” function. This function helps establish a connection to a PostgreSQL database. Arguments such as database name, username, password, and hosts are passed onto the function, helping us to connect to the database of our choice.

• Query Execution: The Psycopg2 module enables us to type in SQL queries against the connected PsycopgSQL database. The “execute()” method helps us to execute SQL statements such as SELECT to access data, INSERT, UPDATE, and DELETE for data manipulation.

• Prepared Statements: Optimisation of SQL queries is a very useful feature of the Psycopg2 module. Preparing a SQL query once and then executing it multiple times with different parameters brings a lot of improvement in terms of performance.

• Transaction management: Psycopg2 provides us with a function which helps to manage transactions. Initiating a transaction, committing changes within a transaction and to rollback everything, is easier with this module. Transactions ensure the integrity and consistency of data by grouping several database operations into a single unit.

• Error Handling: Psycopg2 handles errors and exceptions related to databases, and provides us with detailed error messages and information which helps us to debug issues with the database connection or the query execution.

• Result Handling: After executing a query, the Psycopg2 module provides us with methods to fetch the result set, iterate over the rows and access the returned data. We can get individual columns or access rows as dictionaries for easier data manipulation.

• Data Type Conversion: Psycopg2 automatically converts Python objects to corresponding data types supported by PostgreSQL. Conversion happens the other way around as well. It supports various in-built PostgreSQL data types such as integers, strings, dates, JSON, etc.

Installation of the Postgre2 Module in Python

Here, we will use the pip command to install the Psycopg2 module. We have to make sure that the latest version of pip is being used. In the terminal, we have to type in the following:

pip install -U pip
pip install psycopg2-binary

These commands will install the binary version of Pycopg2 which doesn't require any built or runtime prerequisites.

Usage of the Module

The Psycopg2 module has a lot of applications, such as establishing a connection between Python code and a PostgreSQL database. Here is the code that does just that:

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

try:
   conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
   print("Database connected successfully")
except:
   print("Database not connected successfully")

Here, we can observe that the database name, database user, password, host and port have been stored in different variables. Then, to make the code as robust as possible, try and accept blocks are used. Inside the try block, we use the “connect()” function to connect the Python code to the PostgreSQL database. The function takes up all of that we stored in different variables.

After connecting to the database, we surely want to do something useful with the database. We can use Python code to generate SQL queries! The code snippet below will demonstrate this:

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("""
CREATE TABLE Employee
(
   ID INT  PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   EMAI TEXT NOT NULL
)
""")
conn.commit()
print("Table Created successfully")

Here, we create a cursor using the "cursor()" function and then store it in the cur variable. Then we the format of a multi-line string and we type the SQL query which will go into the database. Then we use the commit() function to apply these changes to the database.

Inserting data into the existing table can be done too! Previously we created the table, and then we will input data into the table. The following code snippet will show us just that:

Example

import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("""
   INSERT INTO Employee (ID, NAME, EMAIL) VALUES
   (1, 'Virat Kohli','viratk@gmail.com'),
   (2,' Lionel Messi','leomessi87@gmail.com')
 """)
conn.commit()
conn.close()

Here, we use the execute() function to execute the SQL statements to insert data into the existing table.

Apart from inserting the data into the actual database and displaying it there on the server, we can display the data in the Python terminal as well. But first, we need to install a module “mysqlx”. This module is also very helpful when working with SQL databases. Here is the code:

Example

from mysqlx import Rows
import psycopg2

DB_NAME = "tkgafrwp"
DB_USER = "tkgafrwp"
DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"
DB_HOST = "tyke.db.elephantsql.com"
DB_PORT = "5692"

conn = psycopg2.connect(database=DB_NAME,
                user=DB_USER,
                password=DB_PASS,
                host=DB_HOST,
                port=DB_PORT)
print("Database connected successfully")

cur = conn.cursor()
cur.execute("SELECT * FROM Employee")
rows = cur.fetchall()
for data in rows:
   print("ID :" + str(data[0]))
   print("NAME :" + data[1])
   print("EMAIL :" + data[2])

print('Data fetched successfully and shown on the terminal!')
conn.close()

Here, we have rows which we have obtained from the “mysqlx” module. Then, by using a for loop, we iterate over the rows of the table. In this way, we obtain all the data from each row.