SQL inside Python

The checker reads SQL strings passed to execute, read_sql, text(), .sql() or sql=, including f-strings, .format(), % and a query held in a variable in the same file. They get the same checks as .sql files, and findings point at the Python line and column.

An example#

A small example file, written for these docs:

import sqlite3

conn = sqlite3.connect("shop.db")

conn.execute("""
    SELECT id FROM customers
    WHERE id NOT IN (SELECT customer_id FROM orders)
""")

table = get_table_name()
conn.execute(f"SELECT COUNT(*) FROM {table} WHERE deleted = NULL")

def build_query(cols):
    return "SELECT " + ", ".join(cols) + " FROM orders"

conn.execute(build_query(["id", "total"]))

A real run on it:

$ truequery review etl/load_orders.py
etl/load_orders.py:7:11 [error] sql/not-in-nullable-subquery — a NULL in the subquery makes NOT IN return zero rows
    fix: rewrite as NOT EXISTS, or add WHERE col IS NOT NULL
etl/load_orders.py:11:51 [error] sql/null-equality-comparison — = NULL is never true (use IS NULL)
    fix: rewrite as IS NULL / IS NOT NULL

TrueQuery receipt
Checked      0 SQL files · SQL in 1 Python file (2 queries) · contracts for 0 of 2 tables
Found        2 errors
Not checked  SQL built at run time in 1 Python file: etl/load_orders.py
Not checked  your data: TrueQuery reads files and never connects to your database
No contract  customers, orders

The two queries it could read were checked. The last one is built by a function when the program runs, so the receipt lists the file instead of calling it clean.

What it can't read#

SQL your program builds at run time, in a loop, in another function or in another module, isn't in the file to read. It's listed as not checked, never as clean.

In a pre-commit hook, use truequery-python to include .py files.