SQL & Databases
Topic 13 · SQL in practice · ⏱ about 2 h

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.

Show:
In this topic
Beginner

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.

Beginner

Never build SQL by gluing strings: SQL injection

Suppose a web form asks for a name and the code does:

query = f"SELECT * FROM customers WHERE name = '{user_input}'" # DANGEROUS

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:

cur.execute("SELECT * FROM customers WHERE name = ?", (user_input,))

(SQLite uses ?; psycopg uses %s; SQLAlchemy uses :name.)

Try it

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.

Beginner

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.

Python
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()
Open in playground ↗
Beginner

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.
Beginner

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 with or close them.
Check yourself

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?

Go deeper

📚 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.

Ready