Connecting to PostgreSQL from Python

Every Python project that talks to PostgreSQL ends up needing two slightly different things from it: a way to run raw statements — creating tables, one-off DDL — and a way to move whole Pandas DataFrames in and out. One connection object does not comfortably do both, which is why most codebases end up with two.

The driver: psycopg#

psycopg is the standard PostgreSQL driver for Python. A plain connection and an explicit cursor is the natural way to run DDL:

import psycopg
def get_connection():    return psycopg.connect(        host=DB_HOST, port=DB_PORT,        dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD,    )

Centralizing this in one function — rather than repeating the connection parameters in every script — means every caller imports the same thing and a credential change is one edit, not a search-and-replace. The same principle shows up in Working with ClickHouse from Python, just with a different client.

The engine: SQLAlchemy, for bulk DataFrame I/O#

DataFrame.to_sql and pandas.read_sql do not accept a raw psycopg connection — they need a SQLAlchemy engine:

from sqlalchemy import create_engine
engine = create_engine(    f"postgresql+psycopg://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}")

The connection string names the dialect and driver together (postgresql+psycopg) so SQLAlchemy knows which library to drive underneath its own interface. Build the engine once at module load and reuse it — like any database client, creating a fresh one per call throws away connection setup for no benefit.

Why keep both side by side#

A get_connection() function for DDL and one shared engine for DataFrame I/O, defined together in one module, is a small pattern worth repeating: it tells every other script in the project exactly where a database handle comes from, so nothing hardcodes credentials a second time, and switching databases later is a one-file change instead of a project-wide search.

Common mistakes#

A new connection per function call#

Opening a connection has real cost — a TCP handshake, authentication, sometimes TLS negotiation. Build one client (or engine) per process and pass it around, rather than reconnecting inside every function. For workloads that open and close connections constantly, a connection pool amortizes that cost further.

Using the raw connection where an engine is needed#

to_sql and read_sql expect a SQLAlchemy-compatible connectable. Passing a bare psycopg connection either fails outright or silently falls back to a slower, less-tested code path depending on the Pandas version — use the engine for DataFrame calls.

Hardcoding the connection string#

A literal postgresql+psycopg://user:pass@host/db in source code is a credential leak waiting to happen the moment the repository is shared or made public. Build it from configuration values, the same as the raw connection.

Further reading#

• Psycopg 3 documentation — the full driver API.
• SQLAlchemy: Engine Configuration — connection URL formats and engine options.
• SQLAlchemy PostgreSQL dialect — what postgresql+psycopg in the URL actually selects.
• PostgreSQL — the project's own documentation index.
• Connection pool — the general pattern for reusing connections under load.
• Working with ClickHouse from Python — the same "one shared client" pattern applied to a different database.

See also#