Python SQLite Tutorial: Learn Embedded Database from Scratch (2026)
SQLite is the unsung hero of local storage — it's in every smartphone, browser, and desktop application. Python ships with sqlite3 in the standard library, so there's nothing to install. I've used SQLite as a caching layer, a local analytics store, and the backing database for desktop apps. It handles gigabytes of data surprisingly well for a serverless database engine.
This tutorial covers the sqlite3 module in depth: creating in-memory and file-based databases, executing queries, using the row factory for named columns, handling concurrent access, and advanced features like user-defined functions and full-text search (FTS5).
Creating a Database and Connection Basics
sqlite3.connect('filename.db') creates or opens a database file. Use ':memory:' for an in-memory database that exists only during the session. Connection objects are context managers. The row_factory = sqlite3.Row setting makes rows accessible by column name, which I consider essential for readability.
import sqlite3
conn = sqlite3.connect('inventory.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
quantity INTEGER DEFAULT 0,
price REAL NOT NULL,
category TEXT
)''')
conn.commit()
print("Database ready")CRUD Operations with Parameterized Queries
Use ? placeholders with a tuple or dict of parameters to avoid SQL injection. lastrowid gives the ID of the last inserted row. fetchone() returns a single row, fetchall() returns all. The rowcount attribute tells you how many rows were affected by UPDATE or DELETE.
# Insert
cursor.execute(
"INSERT INTO items (name, quantity, price, category) VALUES (?, ?, ?, ?)",
('Widget', 100, 9.99, 'tools')
)
print(f"Inserted item ID: {cursor.lastrowid}")
conn.commit()
# Batch insert
items = [
('Bolt', 500, 0.05, 'hardware'),
('Nut', 500, 0.03, 'hardware'),
('Hammer', 50, 14.99, 'tools')
]
cursor.executemany(
"INSERT INTO items (name, quantity, price, category) VALUES (?, ?, ?, ?)",
items
)
conn.commit()
# Select
cursor.execute("SELECT * FROM items WHERE category = ? ORDER BY price", ('tools',))
for row in cursor.fetchall():
print(f"{row['name']}: ${row['price']} (qty: {row['quantity']})")
# Update
cursor.execute(
"UPDATE items SET quantity = quantity + ? WHERE name = ?",
(10, 'Widget')
)
print(f"Rows updated: {cursor.rowcount}")
conn.commit()Using Row Factory and Dict Cursor
The default cursor returns tuples, which are error-prone with positional indexing. Setting conn.row_factory = sqlite3.Row makes rows behave like dictionaries — accessible by name or index. For more control, I sometimes use a custom row factory or create a dict cursor wrapper.
# Row factory example
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM items LIMIT 3")
rows = cursor.fetchall()
for row in rows:
print(f"{row['name']} - ${row['price']}") # Named access
print(f" Keys: {row.keys()}") # Get column names
# Convert rows to dicts for JSON serialization
result = [dict(row) for row in rows]
print(result)Transactions and Error Handling
sqlite3 operates in autocommit mode by default for DDL statements. For DML, changes are in a transaction until commit() or rollback(). Use conn.isolation_level = None for manual transaction control, or None (autocommit) for simplicity. Always catch sqlite3.Error and rollback on failure.
try:
cursor.execute("BEGIN TRANSACTION")
cursor.execute(
"UPDATE items SET quantity = quantity - ? WHERE id = ?",
(1, 1)
)
cursor.execute(
"INSERT INTO audit_log (action, item_id) VALUES (?, ?)",
('sale', 1)
)
conn.commit()
print("Transaction committed")
except sqlite3.Error as e:
conn.rollback()
print(f"Transaction rolled back: {e}")
finally:
cursor.close()
conn.close()Full-Text Search with FTS5
SQLite's FTS5 extension provides full-text search capabilities without external dependencies. Create a virtual table with CREATE VIRTUAL TABLE ... USING fts5(). Search with MATCH operator and rank results with bm25(). FTS5 supports stemming, prefix queries, and custom tokenizers.
cursor.execute('''CREATE VIRTUAL TABLE IF NOT EXISTS items_fts
USING fts5(name, category, content='items', content_rowid='id')''')
# Populate FTS index
cursor.execute('''INSERT INTO items_fts(rowid, name, category)
SELECT id, name, category FROM items''')
conn.commit()
# Search
cursor.execute('''
SELECT i.*, rank
FROM items_fts f
JOIN items i ON f.rowid = i.id
WHERE items_fts MATCH ?
ORDER BY rank
LIMIT 10
''', ('hammer OR wrench',))
for row in cursor.fetchall():
print(f"Found: {row['name']} (${row['price']})")User-Defined Functions and Aggregates
sqlite3 lets you register Python functions as SQL functions using conn.create_function(). This is incredibly useful for custom business logic that would be awkward in pure SQL. You can also create aggregate functions with conn.create_aggregate() by defining a class with step() and finalize() methods.
import re
def strip_html(text):
return re.sub(r'<[^>]+>', '', text) if text else ''
conn.create_function('strip_html', 1, strip_html)
cursor.execute('''
SELECT id, strip_html(body) AS clean_body
FROM posts
WHERE strip_html(body) LIKE ?
''', ('%keyword%',))
for row in cursor.fetchall():
print(f"Post {row['id']}: {row['clean_body'][:50]}...")
# Custom aggregate
class TotalLength:
def step(self, value):
if not hasattr(self, 'total'):
self.total = 0
self.total += len(value) if value else 0
def finalize(self):
return getattr(self, 'total', 0)
conn.create_aggregate('total_length', 1, TotalLength)
cursor.execute("SELECT total_length(name) FROM items")
print(f"Total name length: {cursor.fetchone()[0]}")Frequently Asked Questions
Is SQLite suitable for production web applications?
SQLite handles concurrent reads well but only one writer at a time. For low-traffic sites or internal tools, SQLite is fine. For high-concurrency production apps, use PostgreSQL. SQLite is excellent for local/single-user apps, embedded devices, and testing.
How do I handle concurrent access to SQLite?
Use WAL (Write-Ahead Logging) mode: PRAGMA journal_mode=WAL. This allows concurrent readers while writing. For Python, use timeout parameter in connect() to wait for the lock. Retry on 'database is locked' errors with exponential backoff.
What is the maximum database size for SQLite?
SQLite supports databases up to 281 TB by default, but practical limits are lower due to filesystem constraints and performance. For databases over 10GB, consider if SQLite is still the right choice. Backup and vacuum regularly to prevent bloat.
How do I backup a SQLite database while it's in use?
Use the backup API: conn.backup(target, pages=100, progress=callback). This copies pages incrementally without locking the source database. Alternatively, use the .backup command in the sqlite3 CLI tool.
Originally published on Ayodhyyya. Last updated June 1, 2026.