Python Basics: Querying SQLite with Parameters

SQLite stores a database in a local file without a separate database server. Python’s standard-library sqlite3 module can create tables, insert records, and query them using SQL.

Use Python 3 with sqlite3 support on Linux. Run the shell commands in order in the same shell. Start in a fresh temporary directory so the practice database is separate from your own data:

cd "$(mktemp -d)"

Create a table and insert rows

This creates practice.db in the current directory. Run this setup once; rerunning it against the same database fails because the table already exists.

Save as demo_sqlite_1.py:

import sqlite3

connection = sqlite3.connect("practice.db")
try:
    connection.execute(
        "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, pages INTEGER)"
    )
    connection.executemany(
        "INSERT INTO books (title, pages) VALUES (?, ?)",
        [("Python Notes", 120), ("SQL Notes", 80), ("Linux Notes", 160)],
    )
    connection.commit()
finally:
    connection.close()

Run:

python3 demo_sqlite_1.py

The setup prints nothing on success. executemany() runs the insert once for each pair of values. commit() saves the pending inserts before the connection closes.

Filter and order results

Open the same file and select books with at least 100 pages:

Save as demo_sqlite_2.py:

import sqlite3

connection = sqlite3.connect("practice.db")
try:
    rows = connection.execute(
        "SELECT title, pages FROM books WHERE pages >= ? ORDER BY pages, title",
        (100,),
    )
    for title, pages in rows:
        print(f"{title}: {pages}")
finally:
    connection.close()

Run:

python3 demo_sqlite_2.py

Output:

Python Notes: 120
Linux Notes: 160

WHERE filters rows; ORDER BY controls their order. Without ORDER BY, do not rely on the order returned by a query. (100,) is a one-item tuple; the comma matters.

Keep values separate from SQL

Each ? is a placeholder for a value supplied separately to execute() or executemany(). Use these placeholders for input values instead of inserting them with f-strings or string concatenation. This handles quoting and prevents values from becoming SQL syntax.

Placeholders cannot stand for table names, column names, or SQL keywords. If those must vary, choose from a fixed allowlist in your code.

Keep using the same directory: connecting to a missing filename creates a new database, which can make a wrong path look like an empty database.

Reference

Python sqlite3 documentation

Acknowledgements

This post was created with assistance from AI.