Connecting Python to PostgreSQL with psycopg2

Almost every real-world Python backend connects to a database. Python PostgreSQL integration is most commonly done with psycopg2, the most widely-used PostgreSQL adapter for Python. This guide walks you through getting connected and running your first queries safely.

Installing psycopg2

pip install psycopg2-binary

The -binary package includes pre-compiled binaries, so you don’t need PostgreSQL development headers installed. For production, prefer psycopg2 (non-binary) compiled against your system’s libpq.

Connecting to PostgreSQL

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="myapp",
    user="postgres",
    password="yourpassword",
    port=5432
)

cursor = conn.cursor()

In production, store your connection string in an environment variable — never hardcode credentials.

Running Queries

SELECT — Reading Data

cursor.execute("SELECT id, name, email FROM users WHERE active = %s", (True,))
rows = cursor.fetchall()

for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

# Always close when done
cursor.close()
conn.close()

INSERT — Writing Data

cursor.execute(
    "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id",
    ("Kaikobud", "kai@kaiko.dev")
)
new_id = cursor.fetchone()[0]
conn.commit()  # Don't forget to commit!

Why %s Instead of f-strings?

Always use parameterized queries (%s placeholders). Never build SQL strings with f-strings or concatenation:

# DANGEROUS — SQL injection vulnerability
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# SAFE — parameterized query
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

Using a Context Manager

Wrap connections in a with block to handle commits and rollbacks automatically:

with psycopg2.connect(DATABASE_URL) as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE users SET active = %s WHERE id = %s", (False, 42))
    # conn.commit() is called automatically on exit

Conclusion

Connecting Python to PostgreSQL with psycopg2 is straightforward once you know the pattern. Always use parameterized queries to prevent SQL injection, always commit after writes, and use environment variables for credentials. These three habits cover 90% of database safety concerns.

Read next: Building a REST API with Python and Flask

External resource: psycopg2 Documentation