cursor.execute("DELETE FROM employees WHERE id = ?", (5,)) conn.commit()
cursor.execute("UPDATE users SET age = age + 1 WHERE age < 30") cursor.execute("DELETE FROM users WHERE age > 100")
By default, SQLite returns rows as tuples, forcing you to remember index positions (e.g., row[1] for name). You can fix this and make your queries return dictionary-like objects by changing the row_factory .
The wise old sage appeared once more, explaining that the WHERE clause was used to filter data based on conditions. In this case, Pythonia was retrieving only the rows where the quantity column was greater than 0. sqlite3 tutorial query python fixed
insert_user("john_doe", "john@example.com", 25) insert_user("jane_smith", "jane@example.com", 30)
This string‑formatting approach leads to (if input comes from a user) and syntax errors with special characters. A fixed query uses parameter substitution – a safe, maintainable method that also handles escaping automatically.
dept = "Engineering" cursor.execute("SELECT name FROM employees WHERE department = ?", (dept,)) # Note: (dept,) is a tuple with one element – commas are important! cursor
Using Python string formatting ( f-strings or %s ) to inject variables causes syntax errors when strings contain quotes (like O'Connor ), or exposes your application to SQL injection attacks. The Fix: Always use parameterized queries with placeholders ( ? ).
# Solution: Check if table exists before querying def safe_table_query(table_name): with sqlite3.connect('my_database.db') as conn: cursor = conn.cursor() # Check if table exists cursor.execute(""" SELECT name FROM sqlite_master WHERE type='table' AND name=? """, (table_name,))
Example with fetchone :
Always use placeholder syntax ( ? ) to let the sqlite3 driver safely escape inputs.
def get_users_by_age(min_age, max_age): cursor.execute(''' SELECT username, email, age FROM users WHERE age BETWEEN ? AND ? ORDER BY age DESC ''', (min_age, max_age)) return cursor.fetchall()