DEV Community

niuniu
niuniu

Posted on

Quick Tip — Query a Postgres Database from Python in 6 Lines (No ORM, No Boilerplate)

Most Python+Postgres tutorials drag you through SQLAlchemy sessions, engines, and models before you can run a single SELECT. For scripts, cron jobs, and quick data checks, that's overkill.

Here's the pattern I use daily — 6 lines, real dict results, automatic cleanup:

import psycopg
from psycopg.rows import dict_row

with psycopg.connect("postgresql://localhost/mydb", row_factory=dict_row) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, email, created_at FROM users WHERE active = %s", (True,))
        for row in cur.fetchall():
            print(row["email"], row["created_at"])
Enter fullscreen mode Exit fullscreen mode

Why this works well

  • dict_row gives you {"id": 1, "email": "..."} instead of tuples — no more row[0] guessing
  • Both with blocks auto-commit on success, auto-rollback on exception, and always close the connection
  • %s placeholders are parameterized — no string formatting, no SQL injection
  • psycopg (v3) is the modern driver; psycopg2 is in maintenance mode

One gotcha

psycopg v3 needs the binary extra on some platforms:

pip install "psycopg[binary]"
Enter fullscreen mode Exit fullscreen mode

Without it you'll get a confusing ImportError: no pq wrapper available.

What's your go-to for quick SQL from Python — raw psycopg, SQLAlchemy, or something like records / databases? Curious what's winning in 2026.

Tip tested with MonkeyCode, a free open-source AI coding assistant: https://ly.cyberserval.tech/iIETXiF

Top comments (0)