Wiki Python for data

SQL from inside Python (sqlite3)

An in-memory database to test SQL rules without a server, and ? parameters so you never build SQL from concatenated text.

sqlite3 ships with Python. A :memory: database is born and dies with the program: it is the cheapest way to test a SQL rule in a unit test.

PythonOutput checked by running Python 3

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL)")
con.executemany("INSERT INTO orders VALUES (?, ?, ?)",
                [(1, "Ana", 230.0), (2, "Ana", 900.0), (3, "Bruno", None)])
for row in con.execute("SELECT customer, COUNT(amount), SUM(amount) FROM orders GROUP BY customer ORDER BY customer"):
    print(row)

Output (checked)

('Ana', 2, 1130.0)
('Bruno', 0, None)

Parameter, never concatenated text

The ? sends the value separately from the SQL: quotes in the data do not become code. Building SQL with an f-string is the door to SQL injection.

PythonOutput checked by running Python 3

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE customers (name TEXT)")
con.execute("INSERT INTO customers VALUES (?)", ("O'Brien",))
name = "x' OR '1'='1"
print(con.execute("SELECT COUNT(*) FROM customers WHERE name = ?", (name,)).fetchone())
print(con.execute(f"SELECT COUNT(*) FROM customers WHERE name = '{name}'").fetchone())

Output (checked)

(0,)
(1,)

The second query, built with an f-string, counted a row that does not have that name: the typed text became part of the SQL (OR '1'='1' is always true).

The same goes for Spark SQL: spark.sql("SELECT * FROM t WHERE id = :id", args={"id": 7}) (Spark 3.4+) instead of format.
The SQL examples in this wiki also run on SQLite, but in your browser, through sql.js.

Sources

Shared knowledge from Genesis Innovation. Not a claim about the person behind this site.

PreviousDates, times and time zones NextLazy evaluation: transformations and actions