Stop 04 · SQL & Data Engineering BasicsTopics · 2/9

Working with Databases in Python

In short

Running queries by hand is fine once. Pipelines and training scripts need to pull data on their own, every day. That means talking to the database from Python, safely.

Connecting to and querying databases from Python (sqlite3, SQLAlchemy, psycopg2) — how data pipelines pull training data programmatically.

The purple blob talks on an old landline phone and writes notes on a small notepad.

Think of it like this

A phone call to the bank

You dial and get connected. You ask a clear question, the officer reads out the answer, you note it down, and you hang up. Python does the same: open a connection, execute a query, fetch the rows, and close. Keep the call open forever and the line stays busy for others.

Watch it happen

Python asking a database

Your script

Connection

Database

connect('shop.db')

1/5The script opens a connection. For Postgres this would include host, user and password.

The big ideas

What you need to know

Always use parameters

Pass values with ? or %s placeholders. Building SQL with f-strings invites SQL injection and quoting bugs.

sqlite3 is built in

Python ships with SQLite, a whole database in one file. Perfect for learning and small projects.

SQLAlchemy for the rest

create_engine with a connection URL works for Postgres, MySQL and more, and pandas reads from it directly.

pandas reads queries

pd.read_sql_query(sql, conn) turns a query result straight into a DataFrame.

See it in code

Walk through the code

python

1import sqlite32import pandas as pd3 4conn = sqlite3.connect("shop.db")5with conn:  # commits on success, rolls back on error6    conn.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, city TEXT)")7    conn.executemany("INSERT INTO users (name, city) VALUES (?, ?)",8                     [("Asha", "Pune"), ("Dev", "Delhi"), ("Charu", "Pune")])9 10city = "Pune"11rows = conn.execute("SELECT id, name FROM users WHERE city = ?", (city,)).fetchall()12print(rows)13 14df = pd.read_sql_query("SELECT city, COUNT(*) AS n FROM users GROUP BY city", conn)15print(df)16conn.close()

1/4Open a file-based database. The with block wraps the writes in one transaction.

In sqlite3, the with block handles the transaction but doesn't close the connection, so close() is still needed at the end.

Save yourself the pain

Common mistakes

Oops

Building queries like f"... WHERE name = '{name}'".

Do this instead

Use placeholders and pass values separately. A name like O'Brien, or a malicious input, can't break the query then.

Oops

Putting the database password directly in the script.

Do this instead

Read it from an environment variable or a secrets manager, and keep it out of Git.

Your mission

Load a CSV into a database

~25 min

Quick check

Why use conn.execute("... WHERE city = ?", (city,)) instead of an f-string?

Go deeper

1Connect to a local SQLite or Postgres database from a Python script.
2Practice loading a SQL query result directly into a pandas DataFrame.
3Learn parameterized queries to avoid SQL injection when building data tools.