A search box on a small web application looks up a drug by name. Its code builds the query by inserting the user's text into a string:
def find_drug(con, name):
return con.execute(f"SELECT drug_id, name FROM drug WHERE name = '{name}'").fetchall()
Given ATORVASTATIN, it returns one row. Given x' OR '1'='1, it returns all 666 drugs in the database, because the text closes the quoted string and adds a condition that is always true. Given x' UNION SELECT seqn, age FROM person --, it returns 9,254 rows, the participant number and age of every person in the survey, through a search box meant for drug names. The same function written with a placeholder returns nothing for either input, because the text is then treated as a name to look for and never as SQL.
This article covers running SQL from Python: the common interface that database drivers implement, how to send queries and fetch their results, how to pass values into queries safely, how to read results into pandas, and how transactions keep a group of changes together. The examples use Python's built-in sqlite3 module and the NHANES database of the earlier articles in this topic, which starts with Relational Databases; every result quoted was produced by running the code.
The DB-API
Python's database drivers follow a common specification, the Python Database API Specification 2.0, published as PEP 249 (Lemburg, 1999). It defines a connection object, obtained from the driver's connect function, which represents a session with one database and controls transactions; and a cursor object, obtained from the connection, which executes statements and holds their results. Because the drivers for SQLite (sqlite3, in the standard library), PostgreSQL (psycopg), MySQL (mysql-connector-python) and others implement the same interface, code moves between them with few changes. The module reports which version it follows: sqlite3.apilevel is '2.0'.
What differs is how a connection is made. SQLite is a library that reads a file, so a connection needs only a path; sqlite3.connect(":memory:") creates a database that lives in memory and disappears when closed. A server database needs a host, a database name and credentials:
import os
import psycopg
con = psycopg.connect(host="db.example.org", dbname="survey", user="analyst",
password=os.environ["SURVEY_DB_PASSWORD"])
Passwords belong in the environment, a configuration file outside the code or a secrets manager, never in the source, where they end up in version control and in every copy of a notebook.
Cursors and fetching
import sqlite3
con = sqlite3.connect("nhanes.db")
cur = con.cursor()
cur.execute("SELECT seqn, sex, age FROM person WHERE age >= 18 ORDER BY seqn")
[d[0] for d in cur.description] # ['seqn', 'sex', 'age']
cur.fetchone() # (93705, 'female', 66)
cur.fetchmany(2) # [(93706, 'male', 18), (93708, 'female', 66)]
rest = cur.fetchall() # the remaining 5,853 rows
cur.fetchone() # None: nothing left
execute runs the statement; the rows are then fetched from the cursor, which keeps its place. fetchone returns the next row as a tuple, or None when there are no more; fetchmany(n) returns a list of up to n rows; fetchall returns all remaining rows. A cursor can also be iterated directly, for row in cur: ..., which fetches a row at a time and is the memory-efficient way to process a large result. cur.description gives the result's column names (with six other fields that sqlite3 leaves as None). sqlite3 connections have a shortcut, con.execute, that creates a cursor, executes and returns the cursor in one call.
Rows are tuples by default. Setting con.row_factory = sqlite3.Row makes them objects that can be indexed by column name, row["age"], as well as by position, which makes code that reads several columns less fragile.
One attribute does not do what its name suggests for queries. PEP 249 defines cursor.rowcount as the number of rows the last statement "produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE or INSERT)", but allows -1 when the number "cannot be determined by the interface" (Lemburg, 1999). SQLite cannot know how many rows a query will return until they have all been produced, and the sqlite3 documentation states that rowcount "is -1 for other statements", meaning anything but INSERT, UPDATE, DELETE and REPLACE (Python Software Foundation, 2025). After the SELECT above it is -1. The number of rows returned is len(cur.fetchall()), or better a SELECT COUNT(*).
Parameters
Placeholders
Values that come from Python, such as a user's input, a loop variable or a threshold from a configuration file, are passed to a query as parameters: the SQL text contains a placeholder, and the values are supplied separately as the second argument of execute.
con.execute("SELECT COUNT(*) FROM person WHERE age >= ? AND sex = ?", (18, "female"))
con.execute("SELECT COUNT(*) FROM person WHERE age >= :lo AND sex = :sex",
{"lo": 18, "sex": "female"})
Both return 3,016. sqlite3 accepts question marks, matched to a sequence by position (its paramstyle is 'qmark'), and named placeholders, matched to a dictionary (Python Software Foundation, 2025). PEP 249 allows five styles, and drivers differ: psycopg and the MySQL connector use %s and %(name)s. Those look like Python's string formatting but are not; the driver binds the values, and the query must still be passed as one string with the values separately. SQLite itself also accepts ?NNN, @name and $name (SQLite, n.d.-a).
Two details catch people out. A single parameter must still be in a sequence: (18) is just the integer 18 in parentheses, and sqlite3 rejects it with "parameters are of unsupported type"; (18,) is a tuple. And a list of values for IN needs one placeholder per value, built from the list's length:
names = ["ATORVASTATIN", "SIMVASTATIN", "ROSUVASTATIN"]
marks = ", ".join("?" for _ in names) # '?, ?, ?'
con.execute(f"SELECT COUNT(*) FROM drug WHERE name IN ({marks})", names)
The f-string here inserts only question marks, never data, so it is safe.
Why not format the string: SQL injection
Placeholders are not a matter of style. The function at the top of this article builds its query with an f-string, and its three inputs, run against an in-memory copy of the database, give:
| Input | Formatted into the string | With a placeholder |
|---|---|---|
ATORVASTATIN |
1 row | 1 row |
x' OR '1'='1 |
666 rows (every drug) | 0 rows |
x' UNION SELECT seqn, age FROM person -- |
9,254 rows (every person's age) | 0 rows |
This is SQL injection: input that changes the structure of the statement it is inserted into. The second input closes the string literal early and appends OR '1'='1', a condition true for every row. The third appends a second query whose rows are added to the result, and the -- comments out the stray quote that the original code supplies. A search box meant for drug names has disclosed data from a table the application never meant to show, which is why injection is treated as a security flaw and not merely a bug. The sqlite3 documentation gives the same warning with its own example and the instruction "Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks" (Python Software Foundation, 2025).
A placeholder prevents injection because the statement and the values travel separately: the database parses the SQL text, with a hole where the value goes, before it sees the value, so nothing in the value can become SQL. With a placeholder, x' OR '1'='1 is simply a drug name that does not exist.
A partial protection is sometimes mistaken for a complete one. sqlite3's execute refuses to run more than one statement, so an input that tries to append a second, destructive statement, x'; DROP TABLE drug; --, fails with "You can only execute one statement at a time" and the table survives. The UNION attack above needs only one statement, so this restriction does not stop it. And the same text passed to executescript, which runs any number of statements, drops the table: on the copy of the database, the drug table was gone afterwards. Some other drivers allow several statements in one call by default. Placeholders are the protection; everything else is an accident of the interface.
Identifiers cannot be parameters
Placeholders stand for values, not for names of tables or columns. con.execute("SELECT COUNT(*) FROM ?", ("drug",)) fails with a syntax error, because a table name is part of the statement's structure, which is fixed before any value is bound. When a table or column name genuinely has to vary, it must be checked against a fixed list of allowed names before it is inserted:
ALLOWED = {"person", "exam", "lab", "drug"}
def count_rows(table):
if table not in ALLOWED:
raise ValueError(f"unknown table {table!r}")
return con.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0]
count_rows("lab") # 7,435
Reading results into pandas
For analysis, the usual destination of a query result is a DataFrame. pandas.read_sql takes a query and a connection and does the execute, fetch and conversion in one call (pandas development team, 2024):
import pandas as pd
df = pd.read_sql("SELECT seqn, age, education, income_ratio FROM person WHERE age >= ?",
con, params=(18,))
df.shape # (5856, 4)
params passes values to the driver's placeholders, in the driver's style, so the rule about string formatting applies here too. read_sql accepts a sqlite3 connection, an SQLAlchemy connection, or an ADBC connection; for other DB-API drivers, pandas' documentation says "only sqlite3 is supported" without SQLAlchemy (pandas development team, 2024).
The column types pandas chooses depend on the values that arrive, not on the table's declared types. seqn and age arrive as int64. education is declared INTEGER, but it is NULL for some adults, and NumPy integers cannot hold a missing value, so it arrives as float64, with 2.0 where the database has 2. For the 3,398 people under 18, every value of education is NULL, and the column arrives as object, holding None. The dtype_backend="numpy_nullable" option returns pandas' nullable integer type, Int64, which keeps integers as integers alongside missing values. The cleaning steps of Cleaning Data apply to data read from a database as much as to files.
For results too large for memory, chunksize=5000 makes read_sql return an iterator of DataFrames of up to 5,000 rows; the prescription table arrives as chunks of 5,000, 5,000 and 4,434 rows. The usual pattern is to aggregate each chunk and combine the results, or better, to push the aggregation into SQL and fetch only the summary (Aggregate Functions). In the opposite direction, DataFrame.to_sql(name, con) writes a DataFrame to a table, which is how Data Engineering loads raw files into SQLite before transforming them with SQL.
Transactions
A transaction groups statements so that they take effect together or not at all. The standard example is a transfer between two accounts, which must not stop after the withdrawal; in data work it is a cleaning step made of several updates, or a load that must not leave half a table behind. A transaction ends with COMMIT, which makes its changes permanent and visible to other connections, or ROLLBACK, which discards them.
DB-API connections start transactions implicitly. In sqlite3's default mode, which Python 3.12 named LEGACY_TRANSACTION_CONTROL, a transaction is opened automatically before an INSERT, UPDATE, DELETE or REPLACE and stays open until con.commit() or con.rollback() is called (Python Software Foundation, 2025). Changes not committed are invisible to other connections and are lost when the connection closes. On a file database, an update made through one connection and not yet committed is visible to that connection but not to a second connection to the same file; after rollback, the first connection no longer sees it either.
The connection works as a context manager that commits if the block finishes and rolls back if it raises an exception. On a copy of the database, a cleaning step that converts the coded values in the days column, 77777 (refused) and 99999 (don't know), to NULL in two updates, and fails between them:
try:
with con:
con.execute("UPDATE prescription SET days = NULL WHERE days = 77777") # 4 rows
con.execute("UPDATE prescription SET days = NULL WHERE days = 99999")
raise RuntimeError("something failed before the work was finished")
except RuntimeError:
pass
Before the block, 282 rows hold one of the codes; after the failed block, still 282, because the first update was rolled back with the rest. The same updates in a block that finishes leave none, and 407 rows with NULL days (the 282 converted and 125 that were missing already). Without the transaction, the failure would have left the four refused values converted and the 278 don't-know values not, a state that matches neither the old data nor the intended new data. The documentation adds that the context manager "neither implicitly opens a new transaction nor closes the connection" (Python Software Foundation, 2025); closing needs con.close() or contextlib.closing.
Since Python 3.12 the recommended way to control this is the autocommit argument of connect. autocommit=False gives the behavior PEP 249 specifies, in which a transaction is always open and must be committed explicitly; autocommit=True gives SQLite's own autocommit mode, in which each statement commits on its own (Python Software Foundation, 2025). Most of the time, the context manager around each logical unit of work is the simplest correct pattern under any of these settings.
SQLAlchemy
SQLAlchemy is a widely used Python library that sits above the DB-API drivers. Its lower layer, the Core, provides an engine, created from a URL that names the database and driver, which manages a pool of connections and smooths over differences between drivers. Its upper layer is an object-relational mapper that maps tables to Python classes, which matters more for applications than for analysis. For data work the engine is the part most often met, because pandas uses it to reach databases other than SQLite:
import sqlalchemy as sa
engine = sa.create_engine("sqlite:///nhanes.db") # postgresql+psycopg://user@host/db, ...
with engine.connect() as c:
adults = c.execute(sa.text("SELECT COUNT(*) FROM person WHERE age >= :lo"),
{"lo": 18}).scalar_one() # 5,856
by_sex = pd.read_sql(sa.text("SELECT sex, COUNT(*) AS n FROM person GROUP BY sex"), c)
sa.text marks a string as SQL and uses named placeholders whatever the driver's own style, one of the differences SQLAlchemy hides. Changing the URL is enough to run the same code against PostgreSQL or MySQL, provided the SQL itself is portable, which the dialect notes throughout this topic are meant to help with.
Summary of the pattern
- Connect, preferably in a
withblock or withcontextlib.closing, and keep credentials out of the code. - Write the query as a constant string with placeholders; pass every value as a parameter; never format data into SQL.
- Fetch with
fetchallfor small results, iterate or usechunksizefor large ones, and let SQL do the aggregation when only a summary is needed. - Read into pandas with
read_sql, and check the column types it produced. - Wrap changes in a transaction, so that a failure leaves the database as it was.
The results in this article are produced by tools/experiments/data-science/sql-queries-in-python.py in the site's repository, which runs the injection examples on an in-memory copy of the database. It was run with Python 3.13 (SQLite 3.49.1, pandas 2.2.3) and, for the SQLAlchemy section, with Python 3.11 (SQLite 3.38.4, pandas 3.1.1, SQLAlchemy 2.0.43), with the same results apart from the autocommit attribute, which Python 3.11 does not have.
References
- Lemburg, M.-A. (1999). PEP 249: Python Database API Specification v2.0. Python Enhancement Proposals. https://peps.python.org/pep-0249/ record
- pandas development team (2024). pandas.read_sql. pandas 2.2 API reference. https://pandas.pydata.org/pandas-docs/version/2.2/reference/api/pandas.read_sql.html record
- Python Software Foundation (2025). sqlite3: DB-API 2.0 interface for SQLite databases. Python 3.13 documentation. https://docs.python.org/3.13/library/sqlite3.html record
- SQLite (n.d.-a). SQL language expressions. https://sqlite.org/lang_expr.html record · 4 pages