dept = "Engineering" cursor.execute("SELECT name FROM employees WHERE department = ?", (dept,)) # Note: (dept,) is a tuple with one element – commas are important!
# Create a users table cursor.execute(''' CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, age INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''')
import sqlite3 connection = sqlite3.connect("app.db") cursor = connection.cursor() # ❌ WRONG: Vulnerable to syntax errors and SQL injection # user_input = "O'Connor" # cursor.execute(f"SELECT * FROM users WHERE last_name = 'user_input'") # FIXED: Safe, parameterized query user_input = "O'Connor" cursor.execute("SELECT * FROM users WHERE last_name = ?", (user_input,)) results = cursor.fetchall() print(results) connection.close() Use code with caution. 2. The Singleton Tuple Trap The Problem
Now go build something persistent—bug-free and fixed. Your Python + SQLite3 skills are ready for production. sqlite3 tutorial query python fixed
import sqlite3 try: with sqlite3.connect("app.db") as conn: cursor = conn.cursor() cursor.execute("INSERT INTO users (id, name) VALUES (?, ?)", (102, "Duplicate ID Test")) except sqlite3.IntegrityError as e: print(f"Database error encountered: e") Use code with caution.
This tutorial provides production-ready, secure SQLite3 code for Python applications.
cursor = conn.cursor()
user_id = 42 # Bug: Parentheses without a comma do not create a tuple cursor.execute("SELECT * FROM users WHERE id = ?", (user_id)) Use code with caution. Why it Fails
If you are accessing the database from multiple threads or have an unclosed connection in another script, you’ll see sqlite3.OperationalError: database is locked .
: Load the sqlite3 library and establish a connection. If the database file does not exist, SQLite creates it automatically. dept = "Engineering" 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 ( ? ).
Never use f-strings or % to insert variables into SQL. You risk . Always use ? placeholders.