Connecting to a database safely

Use parameterised queries, keep credentials out of code, open connections in a context manager, reuse them through a pool, read big results in pieces, and load in bulk. Each of those avoids a specific problem.

Parameterised queries

Never build SQL by pasting values into a string.

# unsafe: if name is  x'; DROP TABLE users; --  you have a problem
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")

# safe: the driver sends the value separately from the SQL text
cur.execute("SELECT * FROM users WHERE name = %s", (name,))

The placeholder syntax depends on the driver (%s, ?, :name). The database treats the value as data, never as SQL, which blocks SQL injection. It also handles quoting and types for you.

Credentials

Read the host, user and password from environment variables or a secret manager, and never commit them to Git. Give the pipeline a database user with only the privileges it needs, and for extraction jobs, read-only access to a replica.

Close things properly

with engine.connect() as conn:
    result = conn.execute(text("SELECT ..."), {"d": run_date})

The with block closes the connection even if an error is raised. Leaked connections slowly use up the database's connection limit.

Pooling

Opening a connection is slow, so a pool (the one in SQLAlchemy's create_engine, for example) keeps a few open and reuses them. It matters most when code runs many small queries. Set the pool size with the database's max_connections in mind, particularly when many tasks run in parallel.

Large results

fetchall() loads everything into memory. Use fetchmany(10_000) in a loop, or a server-side cursor (stream_results=True in SQLAlchemy, or a named cursor in psycopg2), so the database streams rows to you.

Loading data in

Inserting row by row with INSERT is slow, because every statement is a network round trip. Use executemany with batches, or better, the bulk path of the database: COPY in Postgres (cursor.copy_expert or psycopg's copy), LOAD DATA in MySQL, or staged files in a warehouse. That can be 10 to 100 times faster. Wrap the load in a transaction, so a failure leaves nothing half-written.