How to Connect to PostgreSQL from Python

Summary: In this tutorial, you’ll learn how to connect to a PostgreSQL database from Python using Psycopg 3.

Prerequisites #

Before you start, make sure you have:

Python installed
A virtual environment for your project
PostgreSQL running
A database to connect to, for example: pyinventory. Follow this tutorial if you have done so.
A PostgreSQL user such as postgres

Connecting to PostgreSQL from Python #

Step 1. Open your terminal and install python-dotenv if you don’t have:

python -m pip install python-dotenv

The python-dotenv allows you to read key-value pairs from a .env configuration file.

Step 2. Create a .env file that stores connection settings:

host=localhost
dbname=pyinventory
user=postgres
password=

The file includes the PostgreSQL host (host), database name (dbname), user (postgres), and password. Since we’re connecting to the local PostgreSQL, the password is blank.

Step 3. Create a file named config.py and use the following code:

from dotenv import dotenv_values

config = dotenv_values(".env")

How it works:

First, import the dotenv_values function from dotenv module.
Second, load the key-value pairs from .env file into a dictionary using the dotenv_values function.

Step 4. Create a file named connect.py:

import psycopg
from config import config

with psycopg.connect(**config) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version()")
        print(cur.fetchone())

How it works:

First, import the psycopg module:

import psycopg

Second, import config from the config module created above:

from config import config

Third, open a new database connection and return a Connection object:

with psycopg.connect(**config) as conn:

Fourth, create a cursor from the database connection for executing a query:

with conn.cursor() as cur:

Fifth, execute the query that returns the current PostgreSQL version:

cur.execute("SELECT version()")

Finally, retrieve the first row from the result and display it on the screen:

print(cur.fetchone())

Step 5. Run the script:

python connect.py

If the connection succeeds, you should see the PostgreSQL version.

Summary #

In this tutorial, you learned how to:

Store connection settings in a .env file, load them into Python using the python-dotenv module.
Connect to PostgreSQL using psycopg.connect().
Create a cursor and run a test query.

Was this helpful?