PostgreSQL is an advanced and open-source relational database that assists users in storing and managing data efficiently. Over the past few years, it has been noticed that developers prefer to use the PostgreSQL database with all the popular languages, including Java, Python, C++, etc.

You will find this write-up useful if you are a Python user looking to connect to a PostgreSQL database server using Python. So, let’s start!

How to Connect to Postgres Database Using Python?

To establish a connection with the Postgres database via python, we will utilize one of the most frequently used adapters named “psycopg2”. The psycopg2 is a Postgres database adapter/driver that is used to perform various operations on the Postgres database via python programming.

The psycopg2 module offers a connect() function that assists us in connecting with the suppliers' databases.

Users must follow the below listed stepwise guidelines for connecting to a Postgres database through python programming:

Step 1: Open Command Prompt

Firstly, search for the CMD from the Windows search bar:

Clicking on the “CMD” app will open the command prompt.

Step 2: Install psycopg2

Let’s execute the below command to install the “psycopg2” module from the CMD:

pip install psycopg2

From the above snippet, you can clearly observe that the “psycopg2” module has been installed successfully.

Step 3: Connect to Postgre Database Via Python

First, you must import the “psycopg2” at the start of your project/program. After that, utilize the connect() function of the “psycopg2” module to establish a connection with Postgres:

con = psycopg2.connect(
database="example",
user="postgres",
password="cp12345",
host="localhost",
port= '5432'
)

In the above snippet, we utilized the following connection parameters:

- database: specify the database name to which you want to connect.
- user: specify a user for authentication.
- password: specify the password to connect to the respective database.
- host: specify the localhost or IP address.
- port: specify the port number, “5432” is the default port number.

The error-free output shows that the connection has been established successfully.

Step 4: Create Cursor Object

The cursor() method assists us in executing the Postgres commands from Python. To do so, firstly, create a cursor object:

cursor_obj = con.cursor()

Once the cursor object is created, you can utilize any function of the Cursor class/object.

Step 5: Execute Postgres Query

To execute any Postgres query, command, function, etc., we will utilize the execute() function of the cursor class:

cursor_obj.execute("SELECT * FROM bike_details")

In the above snippet, we utilized the execute() function. Within the execute() function, we utilized the SELECT query to fetch the data of the bike_details table:

The output shows that the execute() function successfully executed the SELECT query.

Step 6: Fetch All Records

To fetch all the records retrieved by the SELECT query, we will utilize the fetchall() method of the cursor class as follows:

result = cursor_obj.fetchall()

In the above code snippet, we fetched all the rows of the bike_details table and stored the result set into the “result” variable.

Step 7: Print Result

Finally, we will utilize the print() function to print the result set retrieved by the SELECT statement:

This is how you can connect to the Postgres database and perform various operations using Python programming.

Conclusion

In order to connect to the Postgres database server via python, an adapter/module named “psycopg2”, is used. To do so, firstly, install the “psycopg2” module, import it into your python program, and utilize the connect() function of the “psycopg2” module to establish a connection with Postgres. Through practical demonstration, this blog post explained how to connect to the Postgres database using Python and execute Postgres commands from Python.