Connecting to Postgres with Python

In this tutorial, we've covered a comprehensive, step-by-step procedure for establishing a connection to a PostgreSQL database using Python. We will take advantage of psycopg library to connect to the Postgres database.

Let’s get started

Step 1 - Install the “psycopg” library in your project. Run this command in your terminal to install the library:

pip install psycopg

Step 2 - Import psycopg in your Python code

import psycopg

Step 3 - Setup a database connection. For that, create a connection object consisting of database connection parameters - username, database name, password, host and port.

# Load environment variables from .env file
# Install with: pip install python-dotenv
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Connect using environment variables
connection = psycopg.connect(
    dbname=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    host=os.getenv("DB_HOST"),
    port=os.getenv("DB_PORT")
)

Make sure to create a .env file in your project root with your database credentials:

DB_NAME=your_database
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=your_host
DB_PORT=your_port

Never commit the .env file to your repository. Add it to your .gitignore file to prevent accidental exposure of sensitive credentials.

Step 4 - Create a cursor object to run the SQL queries. A cursor is the main object that interacts with the database

cursor = connection.cursor()

Step 5 - Execute the desired SQL query in the cursor object.

cursor.execute("SELECT * FROM customers")
dataset = cursor.fetchall()

For data in dataset:
    print(data)

You can execute your desired query in the cursor object. Also, you can create multiple cursor objects simultaneously to execute multiple SQL queries concurrently.

Step 6 - Close the created cursor object and connection when their work is done.

cursor.close()
connection.close()

You can always create a new cursor object after closing an existing cursor object.

Here’s the full code sample:

# Importing libraries
import psycopg
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Building the database connection
connection = psycopg.connect(
    dbname=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    host=os.getenv("DB_HOST"),
    port=os.getenv("DB_PORT")
)
# Successfully connected to database

# Creating a cursor
cursor = connection.cursor()

# Executing SQL queries
cursor.execute("SELECT * FROM customers")
Datasets = cursor.fetchall()

for data in datasets:
    print(row)

# Closing the cursor
cursor.close()

You can simply copy and paste this code snippet to integrate in your project. Make sure to create a .env file with your database credentials and add it to your .gitignore file to keep your credentials secure.

Conclusion

In this guide, we discussed the step-by-step process to connect to the Postgres database with Python.

You can also read our well-structured Postgres guides.

For further exploration of valuable PostgreSQL-related content, we encourage you to explore our informative blog posts.