Python for Data Engineering
Intermediate 15 min read Python for Data Engineering

SQL and Python Together

Move data between Python and SQL safely

The boundary between Python and SQL — sqlite3, psycopg, SQLAlchemy, pandas read_sql — is where data engineers bridge code and databases without tripping type or injection traps.

sqlite3psycopgSQLAlchemyread_sqlparameterized queries
Prerequisites: File I/O and Formats
Before we start

What you will learn

  • Connect Python to SQLite and Postgres
  • Always parameterize queries to prevent injection and quoting bugs
  • Use read_sql/to_sql and understand chunked bulk loads
Introduction

What is this?

Python talks to databases through drivers: `sqlite3` (built-in), `psycopg` (PostgreSQL) inspired the modern `psycopg3`, and SQLAlchemy provides an ORM/engine layer with `read_sql` bridging directly to pandas. The two non-negotiables are parameterized queries and cursor hygiene.

Motivation

Why do you need it?

  • Building SQL by string concatenation breaks on quotes, and is the classic injection path in pipelines.
  • Drivers handle types (dates, decimals, arrays) that naive string formatting corrupts.
  • The boundary matters: analytics teams read via pandas while writes must be transactional.
Think about it

Mental model

Imagine

A bank teller's deposit slip

Think of

Parameterized SQL

You hand the teller a form with fields filled in, not a sentence telling them what to do. Parameters pass values as values — never as executable instruction text from the user.

Mechanics

How it works

  • `sqlite3.connect(path)` then `cur.execute(sql, params)` runs parameterized SQL with `?` placeholders.
  • psycopg (or the newer `psycopg` v3 package) connects to Postgres with `%s` placeholders.
  • `pandas.read_sql(sql, con)` executes and returns a DataFrame; `df.to_sql(table, con, if_exists='replace')` bulk-writes.
  • Executemany batches inserts; for huge loads use COPY (Postgres) rather than row-by-row inserts.
  • Always wrap writes in a transaction (commit once) so partial writes can be rolled back.

Python ↔ SQL data flow.

Python
Driver
Parameterized SQL
Database
pandas
Hands-on

Examples

Safe lookup + pandas harvest

Safe lookup + pandas harvest
import pandas as pd
from sqlalchemy import create_engine
eng = create_engine("sqlite:///shop.db")
# parameterized, not f-string concatenation
df = pd.read_sql("SELECT * FROM orders WHERE region = ?", eng, params=("EU",))
df.to_sql("orders_eu", eng, if_exists="replace", index=False)

Values flow through placeholders; pandas bridges query results to frames and back.

Real world

In production

  • A reconciliation job reads a warehouse's totals via read_sql and compares to the Python-computed sum — mismatches page.
  • An extractor streams a Postgres table with server-side cursors (SELECT ... OFFSET or keyset pagination) into the lake.
  • A cleanup job executes DELETE with parameters inside a transaction and commits only on row-count verification.
Guidelines

Best practices

  • Parameterize every statement with values; never interpolate user or file data into SQL text.
  • Use a server-side cursor for huge reads (`SELECT ... FETCH NEXT`) instead of buffering everything.
  • Prefer COPY/bulk_load for inserting large volumes over per-row executemany.
  • Commit once per batch and test rollback behavior for partially written batches.
Watch out

Common mistakes

  • f-string SQL that breaks on apostrophes or enables injection.
  • SELECT * into pandas from a 10 TB table, expecting it to fit.
  • Missing commit — writes silently vanish when the script exits.
  • Inserting one row at a time in a loop over a million rows.
Root cause analysis

Common issues

Error
sqlite3.OperationalError with quotes in city names
Root cause

SQL built via string interpolation

Fix

Convert to parameterized queries with placeholders.

Error
read_sql returns strings for numeric columns
Root cause

Column stored as TEXT in the DB or coercion skipped

Fix

CAST in SQL or coerce with pd.to_numeric after reading.

When things break

Debugging tips

  • Print the parameters, not the concatenated SQL, when a query fails mysteriously.
  • For driver type errors, inspect the param types (datetime vs str, Decimal vs float).
  • Enable the driver's query logging to see exactly what the DB received.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

What does `pandas.read_sql("...", con, params=[...])` do that string concatenation cannot?

2

A Python process that writes rows and exits without commit persists them in Postgres.

Go deeper

Official documentation

sqlite3 — Python docs
docs
psycopg — Python PostgreSQL driver
docs