Diagnose affinity and STRICT type failures

Understand the likely causes

• Declared affinity, bound host values, SQL expressions, and the selected table mode can convert or retain values differently. A numeric-looking identifier may lose leading zeros when treated as a quantity.
• A domain rule such as nonnegative stock is separate from storage compatibility. STRICT permits documented lossless conversions; it does not make every business value valid or supply exact decimal money arithmetic.

Trace values from input to storage

Record the input meaning, host type, placeholder binding, declared column type, STRICT status, and relevant expression conversions. Inspect typeof() on a controlled reproduction rather than relying on display formatting. Text that looks numeric can still represent an identifier, and a number with two displayed decimals is not necessarily exact decimal storage. Use synthetic examples instead of copying private rows into public diagnostics.

Distinguish affinity, STRICT, and domain rejection

Ordinary affinity is a conversion preference rather than a complete type constraint. STRICT, available from3.37.0, uses a supported type set and stricter storage rules. CHECK and NOT NULL still define requiredness and value-domain constraints. An INTEGER PRIMARY KEY also has specific identity behavior that a generic numeric column does not share. Verify the actual schema before changing the caller. Avoid casting malformed text to zero as a repair.

Reproduce a rejected quantity and preserved label

The program inserts a valid numeric quantity token into a STRICT INTEGER column and preserves003 in STRICT ANY. A second row with pear as quantity is rejected and does not increase the count. This distinguishes a permitted lossless conversion from incompatible input. All work is in one private memory database, closed at the boundary. The output reports stored types alongside values rather than guessing from appearance.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.execute("CREATE TABLE reading(id INTEGER PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity>=0),raw ANY) STRICT")
    db.execute("INSERT INTO reading VALUES(?,?,?)", (1, "12", "003"))
    try:
        db.execute("INSERT INTO reading VALUES(?,?,?)", (2, "pear", "003"))
    except sqlite3.IntegrityError as error:
        print("rejected", type(error).__name__)
    else:
        raise AssertionError("expected datatype rejection")
    print("stored", db.execute("SELECT quantity,typeof(quantity),raw,typeof(raw) FROM reading WHERE id=1").fetchone())
    print("rows", db.execute("SELECT COUNT(*) FROM reading").fetchone()[0])

rejected IntegrityError
stored (12, 'integer', '003', 'text')
rows 1

Repair the evidenced input or schema boundary

Keep identifiers as text, bind actual numeric values for quantities, and validate format/range before mutation. For money, choose a documented integer scale or appropriate decimal design rather than assuming DECIMAL affinity enforces precision. If the schema itself is wrong, plan a reviewed migration with representative old values, rejected-row accounting, and backup/recovery. Do not widen every column to ANY or remove STRICT merely to silence one malformed input.

Keep it from recurring

• Document field roles, units, affinity/STRICT choices, nullability, length/range rules, and host binding types. Test migrations and every alternative writer against that contract, and preserve controlled rejection evidence without replacing invalid values with plausible defaults.