# SQL inside Python

Docs for TrueQuery 0.1.6. How the checker finds SQL strings in Python files, and what it can't read from the file.

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:

```python
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:

```output
$ 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`](https://analytically.ai/truequery/docs/pre-commit.md) to include `.py` files.