Interacting with SQLite in Python requires understanding the relationship between the Connection, the Cursor, transactions, and parameter substitution. In this topic, we examine how to execute queries safely, access results as structured mappings, and defend against critical SQL injection attacks.
1. The Core Lifecycle: Connection & Cursor
A standard SQLite session follows four stages:
1
Connect: Open a connection object to the database file.
2
Cursor: Obtain a cursor to execute SQL commands and traverse result sets.
3
Execute & Commit: Run SQL statements and commit data mutations.
4
Close: Release locks and close resources.
1import sqlite3
2
3# 1. Establish connection
4conn = sqlite3.connect("inventory.db")
5
6# 2. Instantiate cursor
7cursor = conn.cursor()
8
9# 3. Execute query
10cursor.execute("CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL);")
11
12# 4. Commit and close
13conn.commit()
14conn.close()
2. The Context Manager Pattern: Automatic Commit & Rollback
Python's sqlite3.Connection can act as a context manager. When used in a with conn: block:
If all statements finish without error, Python calls conn.commit() automatically.
If an exception occurs, Python calls conn.rollback() automatically, preserving data integrity.
1import sqlite3
2
3conn = sqlite3.connect("inventory.db")
4
5try:
6with conn:# Automatically manages transactions
7 conn.execute("INSERT INTO products (name, price) VALUES ('Monitor', 14999.0);")
8 conn.execute("INSERT INTO products (name, price) VALUES ('Keyboard', 2499.0);")
9# All changes committed automatically here!
10except sqlite3.DatabaseError as err:
11print(f"Transaction rolled back due to error: {err}")
12finally:
13 conn.close()
3. The Number One Security Vulnerability: SQL Injection
Important Warning
NEVER use Python f-strings, % formatting, or + string concatenation to build SQL statements with user input!
The Vulnerable Pattern:
1# FATAL SECURITY VULNERABILITY (SQL INJECTION):
2malicious_input ="admin' OR '1'='1"
3query =f"SELECT * FROM users WHERE username = '{malicious_input}';"
4# Results in: SELECT * FROM users WHERE username = 'admin' OR '1'='1'; (Bypasses authentication!)
The Secure Pythonic Solution: Parameterized Queries
Always pass parameters as a tuple using the ? placeholder (qmark style) or dictionary keys with :name:
1import sqlite3
2
3conn = sqlite3.connect("inventory.db")
4cursor = conn.cursor()
5
6# 1. Qmark style (Tuple of values)
7safe_name ="Mechanical Keyboard"
8safe_price =3200.0
9cursor.execute(
10"INSERT INTO products (name, price) VALUES (?, ?);",
11(safe_name, safe_price)
12)
13
14# 2. Named style (Dictionary of values)
15cursor.execute(
16"SELECT * FROM products WHERE price <= :max_price AND name LIKE :term;",
17{"max_price":5000.0,"term":"%Keyboard%"}
18)
The database engine compiles the query structure before inserting parameter values, making SQL injection impossible.
16# 3. fetchall(): Retrieves all remaining rows in result set
17remaining = cursor.fetchall()
18print(f"Remaining {len(remaining)} items.")
19
20conn.close()
5. Modern Row Factory: Accessing Columns by Name
By default, SQLite queries return raw tuples (row[0], row[1]). Setting conn.row_factory = sqlite3.Row allows accessing columns by name like a dictionary while retaining indexability:
1import sqlite3
2
3conn = sqlite3.connect("inventory.db")
4# Enable name-based column lookups
5conn.row_factory = sqlite3.Row
6
7cursor = conn.cursor()
8cursor.execute("SELECT id, name, price FROM products LIMIT 1;")
Modifications made via INSERT, UPDATE, or DELETE are staged in an uncommitted transaction. If you close the connection without calling conn.commit(), all your changes are silently rolled back!
2. Forgetting to Close Connections
Always close database connections using conn.close() or use context managers (with sqlite3.connect(...) as conn:) to ensure connection handles are released properly.
💻 Try It Yourself: Safe Database Context Manager
Scenario
Connect to an SQLite database file inventory.db using Python's with sqlite3.connect(...) as conn: context manager, create a products table, and verify automatic transaction commit.
Complete Solution
1import sqlite3
2
3with sqlite3.connect("inventory.db")as conn:
4 cursor = conn.cursor()
5 cursor.execute("""
6 CREATE TABLE IF NOT EXISTS products (
7 id INTEGER PRIMARY KEY AUTOINCREMENT,
8 name TEXT NOT NULL UNIQUE,
9 price REAL NOT NULL,
10 stock INTEGER DEFAULT 0
11 )
12 """)
13# Context manager automatically commits transaction upon exit!
14print("Products table verified and transaction committed.")
Expected Output
1Products table verified and transaction committed.
Interactive Quiz• Knowledge Check
Practice Quiz
Question 1
Why should you NEVER use Python f-strings or string concatenation to build SQL queries with user input?
Question 2
What symbol is used as the standard parameter placeholder in Python's sqlite3 module?
Question 3
What does setting conn.row_factory = sqlite3.Row accomplish?
Question 4
What happens when a with connection: context manager block encounters an unhandled exception?
Question 5
What does cursor.fetchone() return if no more records remain in the query result set?
🚀 What's Next?
In the next lesson, we will continue your intermediate Python journey with CRUD Operations (11: Database Basics with SQLite).