SQLite Queries

Query Parameters

Query with No Parameters

with connect_db() as db:
    sql = "SELECT * FROM notes"
    notes = db.execute(sql).fetchall()

Queries with Parameters

with connect_db() as db:
    sql = "SELECT * FROM notes WHERE user_id=?"
    params = (id,)  # single parameter (note the comma)
    notes = db.execute(sql, params).fetchall()

with connect_db() as db:
    sql = "INSERT INTO notes (title, body) VALUES (?, ?)"
    params = (title, body)  # multiple parameters
    db.execute(sql, params)

Example Queries

`SELECT` to Fetch All Rows with `fetchall()`

with connect_db() as db:
    sql = "SELECT * FROM notes WHERE pinned=1"
    notes = db.execute(sql).fetchall()

`SELECT` to Fetch a Single Row with `fetchone()`

with connect_db() as db:
    sql = "SELECT * FROM notes WHERE id = ?"
    params = (id,)
    note = db.execute(sql, params).fetchone()

`INSERT` to Add a New Row

with connect_db() as db:
    sql = "INSERT INTO notes (title, body) VALUES (?, ?)"
    params = (title, body)
    db.execute(sql, params)

… and, optionally, get the id of the new row

with connect_db() as db:
    sql = "INSERT INTO notes (title, body) VALUES (?, ?)"
    params = (title, body)
    result = db.execute(sql, params)
    new_id = result.lastrowid

`UPDATE` to Update a Row

with connect_db() as db:
    sql = "UPDATE notes SET title = ? WHERE id = ?"
    params = (title, id)
    db.execute(sql, params)

`DELETE` to Delete a Row

with connect_db() as db:
    sql = "DELETE FROM notes WHERE id = ?"
    params = (id,)
    db.execute(sql, params)

⚠️ Important Security Note

To avoid SQL injection attacks (where a user attempts to add malicious SQL into your database via a form input):

✅ Always add data into queries using ? placeholders (parameterised queries):

# This is the correct way...
sql = "SELECT * FROM notes WHERE id=?"
params = (id,)
db.execute(sql, params)

❌ NEVER add user input directly into query strings via f"...{var}":

# This is BAD!
sql = f"SELECT * FROM notes WHERE id={id}"
db.execute(sql)