SQL from Python: sqlite3, pandas & SQL Injection
Run SQL from Python with sqlite3 and pandas, pass values safely with parameters, understand SQL injection, and choose the right database and tools for a project.
Connecting from Python
Python's built-in sqlite3 module talks to SQLite files. Other databases have similar drivers (psycopg for PostgreSQL), and SQLAlchemy gives one interface for all of them. The pattern is always: connect, run statements, fetch results, commit changes, close.
For analysis, pd.read_sql(query, connection) runs a query and returns a DataFrame directly, so you can move straight on to pandas and scikit-learn. Do the heavy filtering and joining in SQL, close to the data, and bring back only what you need.
Never build SQL by gluing strings: SQL injection
Suppose a web form asks for a name and the code does:
If someone types x' OR '1'='1, the query becomes … WHERE name = 'x' OR '1'='1', which returns every customer. Worse inputs can delete tables. This is SQL injection, one of the most common and damaging security bugs on the web.
The fix is simple and always works: use parameters. Put placeholders in the SQL and pass the values separately; the driver sends them as data, never as code:
(SQLite uses ?; psycopg uses %s; SQLAlchemy uses :name.)
Try it: see an injection happen
Type a name, then try the malicious input. Compare the string-built query with the parameterised one.
Interactive: this part runs in your browser.
Code: sqlite3 and pandas together
Create a database, insert rows safely with parameters, query it with pandas, and see the injection fail when parameters are used.
import sqlite3
import pandas as pd
con = sqlite3.connect(":memory:") # use a file name like "shop.db" to keep it
con.execute("CREATE TABLE patients (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER)")
rows = [(1, "Sita", 34), (2, "Leo", 4), (3, "Mia", 27), (4, "Bikash", 61)]
con.executemany("INSERT INTO patients VALUES (?, ?, ?)", rows) # parameters, never f-strings
con.commit()
df = pd.read_sql("SELECT name, age FROM patients WHERE age >= ? ORDER BY age", con, params=(18,))
print(df)
attack = "x' OR '1'='1"
unsafe = f"SELECT COUNT(*) FROM patients WHERE name = '{attack}'"
print("string-built query returns", con.execute(unsafe).fetchone()[0], "rows <- injection!")
safe = con.execute("SELECT COUNT(*) FROM patients WHERE name = ?", (attack,)).fetchone()[0]
print("parameterised query returns", safe, "rows")
with con: # a transaction: commits, or rolls back on error
con.execute("UPDATE patients SET age = age + 1 WHERE id = ?", (2,))
print(con.execute("SELECT * FROM patients WHERE id = 2").fetchone())
con.close()Choosing a database and tools
- Learning, prototypes, single-user apps: SQLite. One file, nothing to install.
- A web app with many users: PostgreSQL (the Flask & Deployment course uses SQLite locally and explains moving to PostgreSQL).
- Large analytics: a cloud warehouse (BigQuery, Snowflake) or DuckDB on your laptop, which runs fast analytical SQL directly on CSV and Parquet files.
- Tools: a database GUI (DBeaver, TablePlus, or the database panel in VS Code) to browse tables; SQLAlchemy or an ORM in applications; dbt for teams that build data pipelines in SQL.
Summary
Key words
- sqlite3
- Python's built-in SQLite module
- connection / cursor
- the link to the database / the object that runs statements
- parameter
- a ? placeholder filled in safely by the library
- SQL injection
- an attack that smuggles SQL in through user input
- pd.read_sql
- runs a query and returns a pandas DataFrame
Common mistakes
- Building SQL with f-strings or + from user input: always use ? parameters.
- Forgetting
conn.commit()after changes. - Leaving connections open: use
withor close them.
Quiz
1. Which line is safe from SQL injection?
2. pd.read_sql(query, con) returns…
3. Why filter and join in SQL before loading into pandas?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- sqlite3: DB-API interface for SQLite ↗ the Python documentation, including placeholders
- OWASP: SQL Injection ↗ how injection works and how to prevent it