PostgreSQL Python: Connect to PostgreSQL from Python with psycopg3

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-dotenvCode language: Python (python)

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=Code language: Python (python)

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")Code language: Python (python)

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())Code language: Python (python)

How it works:

First, import the psycopg module:

import psycopgCode language: Python (python)

Second, import config from the config module created above:

from config import configCode language: Python (python)

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

with psycopg.connect(**config) as conn:Code language: Python (python)

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

with conn.cursor() as cur:Code language: Python (python)

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

cur.execute("SELECT version()")Code language: Python (python)

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

print(cur.fetchone())Code language: Python (python)

Step 5. Run the script:

python connect.pyCode language: Python (python)

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.