Using SQLite from a Program

Connecting, parameterized queries and transactions from Python — the same pattern applies in C, Java, Go or Kotlin.

SQLite is almost always used from inside a program. Python includes it in the standard library (the sqlite3 module); C programs use the official C API, Android has it built in, and every major language has a driver.

Rules that apply to every database driver

con.execute(f"SELECT * FROM log WHERE sensor = '{sensor}'") con.execute("SELECT * FROM log WHERE sensor = ?", (sensor,))

String formatting lets input like x' OR '1'='1 change the query (SQL injection). Parameters are sent separately from the SQL and are never interpreted as SQL.

Why is inserting 10,000 rows one transaction at a time slow in SQLite?

Each commit must be made durable on disk (fsync). Wrapping all inserts in one transaction does that once.