Sqlite3 Tutorial Query Python Fixed Patched -

import sqlite3 DB_NAME = "tasks.db"

def add_task(title): with sqlite3.connect(DB_NAME) as conn: cursor = conn.cursor() cursor.execute("INSERT INTO tasks (title) VALUES (?)", (title,)) print(f"Task '{title}' added.") sqlite3 tutorial query python fixed

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice",)) # Only one value, two placeholders Match exactly. Use (name, age) for two placeholders. Error 3: sqlite3.IntegrityError: UNIQUE constraint failed Cause: Duplicate value in a column defined as UNIQUE . Fix: Either remove duplicate or use INSERT OR IGNORE or INSERT OR REPLACE . import sqlite3 DB_NAME = "tasks

Alternatively, use conn.row_factory = sqlite3.Row for a more memory-efficient version. SQLite3 in Python starts a transaction automatically before DML ( INSERT , UPDATE , DELETE ). If you don’t commit, everything rolls back when the connection closes. Fix: Either remove duplicate or use INSERT OR

with sqlite3.connect("my_database.db") as conn: cursor = conn.cursor() # your queries here # Automatically commits and closes If you use the raw conn.commit() and conn.close() , ensure they are in a finally block or use try/finally . Creating a Table cursor.execute(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, age INTEGER ) """) conn.commit() INSERT Query cursor.execute(""" INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30) """) conn.commit() The fix: Without conn.commit() , the row vanishes when your script ends. SELECT Query cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row) UPDATE and DELETE cursor.execute("UPDATE users SET age = 31 WHERE name = 'Alice'") cursor.execute("DELETE FROM users WHERE email = 'alice@example.com'") conn.commit() 5. Parameterized Queries – The ONLY Safe Way Never use f-strings or % to insert variables into SQL. You risk SQL injection . Always use ? placeholders. FIXED Example (Safe): def add_user(name, email, age): with sqlite3.connect("my_database.db") as conn: cursor = conn.cursor() cursor.execute(""" INSERT INTO users (name, email, age) VALUES (?, ?, ?) """, (name, email, age)) # No need for explicit commit here (context manager does it) The Mistake That Needs Fixing: # DANGEROUS - DO NOT DO THIS cursor.execute(f"INSERT INTO users VALUES ('{name}')") If name is "'; DROP TABLE users; --" , you lose everything. 6. Common Query Errors & Their Fixes (The "Fixed" Section) Error 1: sqlite3.OperationalError: no such table Cause: Table doesn’t exist yet. Fix: Use CREATE TABLE IF NOT EXISTS before any query. Error 2: sqlite3.ProgrammingError: Incorrect number of bindings Cause: Number of ? placeholders doesn’t match tuple length. Example of wrong:

def delete_task(task_id): with sqlite3.connect(DB_NAME) as conn: cursor = conn.cursor() cursor.execute("DELETE FROM tasks WHERE id = ?", (task_id,)) print(f"Task {task_id} deleted.") if cursor.rowcount else print("Not found.")

def dict_factory(cursor, row): return {col[0]: row[idx] for idx, col in enumerate(cursor.description)} conn = sqlite3.connect("my_database.db") conn.row_factory = dict_factory cursor = conn.cursor() cursor.execute("SELECT * FROM users") for user in cursor.fetchall(): print(user["name"], user["email"])