Course topics

By WebNest Studio Engineering Team

Python Tutorial

Connecting Python to PostgreSQL

PostgreSQL is a powerful open-source database known for correctness, rich data types (JSONB, arrays, ranges) and advanced SQL. The modern Python driver is psycopg 3 (package psycopg); its predecessor psycopg2 is still found in many existing projects and has a very similar API.

This lesson shows how to connect, run queries with parameters, get rows as dictionaries, use RETURNING, manage transactions, handle specific PostgreSQL errors, and use a connection pool.

These examples need a running PostgreSQL server, so run them on your own computer.

Why it matters

PostgreSQL is the default choice for new projects in much of the industry, valued for its reliability and its features such as JSON columns and arrays. psycopg is the standard driver, and it is what sits underneath SQLAlchemy and Django when they talk to PostgreSQL.

Setup

  • Run PostgreSQL locally or with Docker: docker run -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:17.
  • Install the driver: pip install "psycopg[binary]", and pip install psycopg-pool for pooling.
  • Connect with a connection string (DSN) such as postgresql://user:password@localhost:5432/dbname, usually read from a DATABASE_URL environment variable.

psycopg 3 Essentials

with psycopg.connect(dsn) as conn: commits when the block succeeds, rolls back on an exception, and closes the connection. conn.execute() is a shortcut that creates a cursor. Placeholders are %s or %(name)s. Pass row_factory=dict_row to receive dictionaries. INSERT ... RETURNING id returns generated values in the same round-trip. with conn.transaction(): creates an explicit transaction block (or a savepoint when nested). Specific errors live in psycopg.errors, for example UniqueViolation and ForeignKeyViolation.

Examples

Connect, create, insert with RETURNING, and query as dictionaries

Python
# pip install "psycopg[binary]"
import os
import psycopg
from psycopg.rows import dict_row

DSN = os.environ.get("DATABASE_URL", "postgresql://shop_app:secret@localhost:5432/shop")

with psycopg.connect(DSN, row_factory=dict_row) as conn:      # commits and closes at the end
    with conn.cursor() as cur:
        cur.execute("""
            CREATE TABLE IF NOT EXISTS tasks (
                id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                title TEXT NOT NULL,
                done BOOLEAN NOT NULL DEFAULT false,
                created_at TIMESTAMPTZ NOT NULL DEFAULT now()
            )
        """)
        cur.execute("INSERT INTO tasks (title) VALUES (%s) RETURNING id", ("Write report",))
        print("new id:", cur.fetchone()["id"])

        cur.executemany("INSERT INTO tasks (title) VALUES (%s)", [("Review PR",), ("Deploy",)])
        cur.execute("UPDATE tasks SET done = true WHERE title = %(title)s", {"title": "Review PR"})

        cur.execute("SELECT id, title, done FROM tasks ORDER BY id")
        for row in cur:
            print(row)
Output
new id: 1
{'id': 1, 'title': 'Write report', 'done': False}
{'id': 2, 'title': 'Review PR', 'done': True}
{'id': 3, 'title': 'Deploy', 'done': False}

Transactions and catching a UniqueViolation

Python
import os
import psycopg
from psycopg import errors

DSN = os.environ["DATABASE_URL"]

with psycopg.connect(DSN) as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS users (email TEXT PRIMARY KEY, name TEXT NOT NULL)")
    try:
        with conn.transaction():                     # both inserts or neither
            conn.execute("INSERT INTO users VALUES (%s, %s)", ("asha@example.com", "Asha"))
            conn.execute("INSERT INTO users VALUES (%s, %s)", ("asha@example.com", "Asha again"))
    except errors.UniqueViolation as e:
        print("rolled back:", e.diag.message_primary)

    print("users stored:", conn.execute("SELECT count(*) FROM users").fetchone()[0])
Output
rolled back: duplicate key value violates unique constraint "users_pkey"
users stored: 0

PostgreSQL types: JSONB and arrays map to Python dicts and lists

Python
import os
import psycopg
from psycopg.types.json import Jsonb

with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    conn.execute("""CREATE TEMP TABLE products (
        name TEXT, tags TEXT[], specs JSONB)""")
    conn.execute("INSERT INTO products VALUES (%s, %s, %s)",
                 ("Laptop", ["electronics", "office"], Jsonb({"ram_gb": 16, "ssd_gb": 512})))
    name, tags, specs = conn.execute(
        "SELECT name, tags, specs FROM products WHERE %s = ANY(tags)", ("office",)).fetchone()
    print(name, tags, specs["ram_gb"], type(specs).__name__)
Output
Laptop ['electronics', 'office'] 16 dict

A connection pool for web applications

Python
# pip install psycopg-pool
import os
from psycopg_pool import ConnectionPool

pool = ConnectionPool(os.environ["DATABASE_URL"], min_size=2, max_size=10, open=True)

def count_open_tasks():
    with pool.connection() as conn:          # borrowed from the pool, returned afterwards
        return conn.execute("SELECT count(*) FROM tasks WHERE NOT done").fetchone()[0]

print("open tasks:", count_open_tasks())
pool.close()
Output
open tasks: 2

Common Mistakes

  • Mixing up psycopg (version 3) and psycopg2 examples — imports and some APIs differ.
  • Using ? placeholders; psycopg expects %s or %(name)s.
  • Catching a database error and continuing to use the connection without rolling back (PostgreSQL then rejects every command in the failed transaction).
  • Passing a Python dict for a JSONB column without wrapping it in Jsonb().
  • Creating a new connection for every web request instead of using psycopg_pool.

Exercise

Using psycopg (version 3), insert a row into a users(id SERIAL PRIMARY KEY, name TEXT) table and get the generated id back with RETURNING, then read the name for that id. This needs a running PostgreSQL server, so replace the connection string with your own. When it works, the program prints Asha.

Python · starter code
import psycopg

with psycopg.connect("dbname=shop user=app password=secret host=localhost") as connection:
    with connection.cursor() as cursor:
        # TODO: insert "Asha" and fetch the id produced by RETURNING id

        # TODO: select the name for that id and print it
        pass
Show hints
  • Add RETURNING id to the INSERT, then call cursor.fetchone()[0].
  • The placeholder is %s. Leaving the with block commits the transaction.
Show solution
Python · solution
import psycopg

with psycopg.connect("dbname=shop user=app password=secret host=localhost") as connection:
    with connection.cursor() as cursor:
        cursor.execute("INSERT INTO users (name) VALUES (%s) RETURNING id", ("Asha",))
        new_id = cursor.fetchone()[0]

        cursor.execute("SELECT name FROM users WHERE id = %s", (new_id,))
        print(cursor.fetchone()[0])

Quick quiz

1. What does RETURNING id at the end of an INSERT do in PostgreSQL?

  • A. Rolls back the insert
  • B. Returns the id of the inserted row as a result
  • C. Renames the column
  • D. Checks whether the id exists
Show answer

B. It avoids a second query to find the generated key.

2. What happens when a psycopg 3 connection used in a with block exits without an error?

  • A. The transaction is committed and the connection is closed
  • B. The transaction is rolled back
  • C. Nothing
  • D. The database is dropped
Show answer

A. If the block raises an exception, the transaction is rolled back instead.

3. How does psycopg return a PostgreSQL JSONB value to Python?

  • A. As a string that must be parsed
  • B. It cannot be read
  • C. As bytes
  • D. As a Python dict or list
Show answer

D. PostgreSQL arrays likewise become Python lists.

Interview questions

How do you connect to PostgreSQL from Python?

Show answer

With the psycopg driver. psycopg.connect() takes a connection string or keyword arguments and returns a connection; statements are run through a cursor with %s placeholders. Using the connection and cursor as context managers commits or rolls back and releases them automatically. Web applications use a connection pool from psycopg_pool.

Why choose PostgreSQL over SQLite for a web application?

Show answer

SQLite is a single file with one writer at a time, which suits local tools, tests and small sites. PostgreSQL is a server built for many simultaneous users: it handles concurrent writes, has user accounts and permissions, supports replication and backups while running, and offers richer types and indexing. It is the appropriate choice once several users or several application servers share the data.

Key Points to Remember

  • psycopg 3 is the modern PostgreSQL driver; with psycopg.connect(...) commits and closes.
  • Use %s / %(name)s placeholders, and RETURNING to get generated ids.
  • row_factory=dict_row gives dictionaries; arrays become lists and JSONB becomes dicts.
  • conn.transaction() groups statements; psycopg.errors has specific exception classes.
  • Use a ConnectionPool in long-running applications.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering