How to Connect to PostgreSQL from Python

Python has four serious options for talking to PostgreSQL: psycopg 3, psycopg2, asyncpg, and SQLAlchemy on top of one of those drivers. They differ in API style, async support, and performance characteristics. This guide shows working connection code for each, then covers pooling, SSL, and the errors you will actually hit.

Which driver should you use?

Short version: new projects should use psycopg 3 (pip install "psycopg[binary]"). It is the actively developed successor to psycopg2, supports both sync and async from one codebase, and uses server-side parameter binding by default. Use asyncpg when you are all-in on asyncio and want maximum throughput. Use psycopg2 when an existing dependency requires it. Use SQLAlchemy when you want an ORM or database-agnostic code, and pick the driver underneath it.

Connection details you need

Whatever driver you choose, you need the same five things: host, port (5432 by default), database name, user, and password. Most drivers also accept a single connection string:

postgresql://user:password@localhost:5432/mydb

Keep credentials out of source code. The examples below read from environment variables, which is the minimum bar; a secrets manager is better in production.

psycopg 3 (recommended)

Install:

pip install "psycopg[binary]"

The [binary] extra ships a precompiled C implementation. For production images where you want to compile against your own libpq, use psycopg[c]; the pure-Python fallback (plain psycopg) works everywhere but is slower.

Connect and query:

import os
import psycopg
 
conninfo = os.environ["DATABASE_URL"]  # postgresql://user:pass@host:5432/db
 
with psycopg.connect(conninfo) as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, email FROM users WHERE created_at > %s",
            ("2026-01-01",),
        )
        for row in cur.fetchall():
            print(row)
# leaving the with-block commits (or rolls back on exception) and closes

Two things to notice. First, parameters are passed as a separate tuple, never interpolated into the SQL string. This is what prevents SQL injection; psycopg 3 sends query and parameters separately to the server. Second, the with block on the connection commits on clean exit and rolls back on exceptions, which is a behavior change from psycopg2, where exiting the block only closed the cursor and you committed manually.

Dict rows instead of tuples:

from psycopg.rows import dict_row
 
with psycopg.connect(conninfo, row_factory=dict_row) as conn:
    row = conn.execute("SELECT id, email FROM users LIMIT 1").fetchone()
    print(row["email"])

conn.execute() is a psycopg 3 shortcut that creates the cursor for you.

Async, same API surface:

import asyncio
import psycopg
 
async def main():
    async with await psycopg.AsyncConnection.connect(conninfo) as conn:
        async with conn.cursor() as cur:
            await cur.execute("SELECT count(*) FROM users")
            print(await cur.fetchone())
 
asyncio.run(main())

psycopg2

Still everywhere, still works, but in maintenance mode: new features land in psycopg 3. If you are starting fresh, skip to psycopg 3. If you are maintaining existing code:

pip install psycopg2-binary

import os
import psycopg2
 
conn = psycopg2.connect(
    host=os.environ["PGHOST"],
    port=5432,
    dbname=os.environ["PGDATABASE"],
    user=os.environ["PGUSER"],
    password=os.environ["PGPASSWORD"],
)
 
try:
    with conn.cursor() as cur:
        cur.execute("SELECT id, email FROM users WHERE id = %s", (42,))
        print(cur.fetchone())
    conn.commit()
finally:
    conn.close()

The classic psycopg2 trap: with conn: does NOT close the connection. It wraps a transaction (commit/rollback) but leaves the connection open. You still need conn.close() or a try/finally. psycopg 3 fixed this; the same syntax there closes the connection too.

Note on packaging: psycopg2-binary is fine for development, but the maintainers recommend building psycopg2 from source against your system libpq for production to avoid potential binary incompatibilities with other compiled packages.

asyncpg

asyncpg skips the DB-API spec and talks PostgreSQL's binary protocol directly, which is why it benchmarks faster than the alternatives. The trade-offs: asyncio only, no sync mode, and a parameter style ($1, $2) that differs from every other Python driver.

pip install asyncpg

import asyncio
import os
import asyncpg
 
async def main():
    conn = await asyncpg.connect(os.environ["DATABASE_URL"])
    try:
        rows = await conn.fetch(
            "SELECT id, email FROM users WHERE created_at > $1",
            "2026-01-01",
        )
        for r in rows:
            print(r["id"], r["email"])
    finally:
        await conn.close()
 
asyncio.run(main())

Rows come back as Record objects supporting both index and key access. fetch, fetchrow, fetchval, and execute replace the cursor dance entirely.

SQLAlchemy

SQLAlchemy is not a driver; it sits on top of one. As of SQLAlchemy 2.0, the PostgreSQL dialects you care about are postgresql+psycopg (psycopg 3, sync and async), postgresql+psycopg2, and postgresql+asyncpg.

pip install sqlalchemy "psycopg[binary]"

Core (raw-ish SQL with pooling handled for you):

import os
from sqlalchemy import create_engine, text
 
engine = create_engine(
    os.environ["DATABASE_URL"].replace("postgresql://", "postgresql+psycopg://"),
    pool_size=5,
    max_overflow=10,
)
 
with engine.connect() as conn:
    result = conn.execute(text("SELECT id, email FROM users WHERE id = :id"), {"id": 42})
    print(result.fetchone())

The URL scheme matters: plain postgresql:// makes SQLAlchemy default to psycopg2. Be explicit with postgresql+psycopg:// if you want psycopg 3, or postgresql+asyncpg:// with create_async_engine for async.

Connection pooling

Opening a PostgreSQL connection costs a TCP handshake, authentication, and a backend process fork on the server. Doing that per request will dominate your latency, and PostgreSQL's max_connections (often 100) runs out fast. Any long-running service needs a pool.

With psycopg 3, the pool lives in a separate package:

pip install psycopg_pool

from psycopg_pool import ConnectionPool
 
pool = ConnectionPool(conninfo, min_size=2, max_size=10, open=True)
 
with pool.connection() as conn:
    print(conn.execute("SELECT now()").fetchone())

asyncpg ships its own:

pool = await asyncpg.create_pool(dsn, min_size=2, max_size=10)
async with pool.acquire() as conn:
    await conn.fetchval("SELECT now()")

SQLAlchemy engines pool by default (pool_size=5 plus max_overflow=10 unless configured otherwise).

If you run serverless functions or hundreds of app instances, an in-process pool is not enough, because every instance holds its own connections. That is when a server-side pooler like PgBouncer (or your cloud provider's equivalent, e.g. Supabase's pooler or RDS Proxy) goes in front of the database. One caveat: PgBouncer in transaction mode breaks session-level features like prepared statements; asyncpg users typically need statement_cache_size=0 in that setup.

SSL/TLS

Hosted PostgreSQL (RDS, Cloud SQL, Supabase, Neon) generally requires TLS. Append sslmode=require to the connection string:

postgresql://user:pass@host:5432/db?sslmode=require

require encrypts but does not verify the server certificate; verify-full also checks the certificate and hostname against a CA bundle you provide via sslrootcert. For anything where the network path is not fully trusted, verify-full is the correct setting. asyncpg accepts the same URL parameter or an ssl argument with a Python SSLContext.

Common errors and what they mean

• connection refused: PostgreSQL is not listening where you think. Check host/port, check listen_addresses in postgresql.conf (it defaults to localhost, so remote connections need it changed), and check firewalls.
• FATAL: no pg_hba.conf entry for host ...: the server is reachable but its access rules reject your host/user/database/TLS combination. Fix the matching line in pg_hba.conf or your provider's network allow-list.
• FATAL: password authentication failed: wrong credentials, or the user lacks a password while pg_hba.conf demands one. Watch out for special characters in passwords inside URLs; percent-encode them.
• FATAL: too many connections: you exhausted max_connections. Almost always means missing or misconfigured pooling, not a need to raise the limit.
• SSL connection has been closed unexpectedly mid-query: usually an idle connection killed by a load balancer or the server. Pools with max_idle/pre_ping-style checks recover from this; raw long-lived connections do not.

Verifying your connection and exploring the data

Once connected, the next step is usually inspecting what is actually in the database: which tables exist, what the schema looks like, whether your inserts landed. You can do that with psql and \dt, or use a GUI client; we compared the options in Best PostgreSQL GUI Clients. For getting data in, see Import CSV to PostgreSQL and Import JSON to PostgreSQL. Once you are writing real queries, the PostgreSQL window functions and JSON queries guides cover the parts of SQL that pay off most.

Version notes (as of June 2026): psycopg 3.x is the actively developed line (3.3 current), psycopg2 is at 2.9.x in maintenance mode, asyncpg is on 0.x but stable and widely used in production, SQLAlchemy 2.0 is the current major version.

Mako connects to PostgreSQL with AI-powered autocomplete for writing queries. Try it free at mako.ai.