Introduction to the 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 interacts with the PostgreSQL database through a module called the "Psycopg2 module". It is a popular PostgreSQL database adapter for Python. This module provides us with a set of functions and classes to help us with database connections, result processing, and query execution.

The main 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 the PostgreSQL database. We can pass parameters like database name, username, password and host to this function which will help us connect to the database of our choice

• Query Execution: The Psycopg2 module enables us to enter SQL queries against a connected PsycopgSQL database. The "execute()" method helps us execute SQL statements, such as SELECT to access data, and INSERT, UPDATE, and DELETE for data operations.

• Precompiled Statements: Optimizing SQL queries is a very useful feature of the Psycopg2 module. Simply preparing a SQL query once in advance and executing it multiple times with different parameters can lead to big improvements in 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 will automatically convert Python objects to the corresponding data types supported by PostgreSQL. vice versa. It supports various built-in 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.

How to use 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, we use try and accept blocks. Inside the try block, we use the "connect()" function to connect the Python code to the PostgreSQL database. This function uses all the information we stored in different variables

After connecting to the database, we definitely hope to be able to perform some useful operations on the database. We can use Python code to generate SQL queries! The following code snippet demonstrates 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.

It is also possible to insert data into existing tables! Earlier we created the table and then we entered the data into the table. The following code snippet will show us:

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.

In addition to inserting data into the actual database and displaying it on the server, we can also display the data in the Python terminal. But first, we need to install a module called "mysqlx". This module is also very helpful when working with SQL databases. The following 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 the rows taken from the "mysqlx" module. Then, by using a for loop, we iterate through each row of the table. This way we get all the data for each row.

The above is the detailed content of Introduction to the Psycopg2 module in Python. For more information, please follow other related articles on the PHP Chinese website!