How to Connect to PostgreSQL database using Python

Python is a popular language that allows you to perform many different tasks and build complex applications. Sometimes you may need to connect to PostgreSQL database from your python application and fetch data using SQL queries. Python provides many third-party libraries for it. In this article, we will look at how to connect to PostgreSQL database using Python.

How to Connect to PostgreSQL database using Python

Here are the steps to connect to PostgreSQL database using Python.

1. Install Python Library for PostgreSQL

psycopg2 and pygresql are two popular libraries to connect to your database and execute SQL queries. First, you need to install them. Open terminal or command prompt and run the following command to install either of these libraries.

pip install psycopg2
pip install pygresql

Both these libraries provide the same functions to connect to database and execute SQL queries.

2. Create Python Script

Next, run the following command to create empty python script.

$ sudo vi db_query.py

Add the following lines to it, to set its execution environment.

#!/usr/bin/env python

3. Import Library

Depending on the library you have installed, add the following line to import PostgreSQL connection library to your script.

import psycopg2 as db_connect

OR

import pygresql as db_connect

4. Connect to Database

Next, add the following lines to connect to your database. Replace the values of host_name, db_user, db_password, and db_name with your database’s host address, username, password and database name respectively. We will use the connect() method to establish database connection, and pass the database user credentials as arguments.

host_name="localhost"
db_user="test_user"
db_password="123"
db_name="project

connection = db_connect.connect(host=host_name,user=db_user,password=db_password,database=db_name)
 
cursor = connection.cursor()

The above lines will connect to your database and return a connection object that can be used to connect to database. It also provides a cursor object used to send queries, fetch result and traverse it.

5. Query Database

Next, write your SQL query. You can change the following query as per your requirement.

query = "select * from data limit 5"

Next, we use the cursor object to execute the query, using execute command.

results = cursor.execute(query).fetchall()
print(result)

The above commands will run your SQL query and fetch its result. We use fetchall() function to get all rows of data. You can even use fetchone() to get one row at a time. Once you have the result, you can print it using print command, or loop through it and do further data manipulation.

6. Close connection

Finally, when you are done, it is important to close the connection so that it doesn’t keep your database database server busy.

connection.close()

Save and close the file. Here is the entire code for your reference.

#!/usr/bin/env python

import psychopg2 as db_connect

host_name="localhost"
db_user="test_user"
db_password="123"
db_name="project

connection = db_connect.connect(host=host_name,user=db_user,password=db_password,database=db_name)
 
cursor = connection.cursor()

query = "select * from data limit 5"

results = cursor.execute(query).fetchall()
print(result)

connection.close()

Make the file executable with the following command.

$ sudo chmod +x db_query.py

You can run the script with the following command.

$ sudo python db_query.py

In this article, we have learnt how to connect to database and run SQL queries against them, from within python script. You may modify it as per your requirement. The key is to create a connection object using connect() function, use the cursor generated using cursor() function to run queries and fetch result. And finally close the database connection.

It is important to note that almost all database libraries use the same functions of connect(), cursor(), execute(), fetchall() to work with databases. So you can use the above code to query almost any relational database, just by changing the import statement at the beginning of .py file.

How to Connect to PostgreSQL database using Python