SQLite3 in Python

Python's sqlite3 module provides a zero-dependency, serverless relational database. SQLite stores data in a single file (or entirely in memory), making it perfect for desktop apps, prototyping, testing, and small-to-medium scale data storage.

Connecting and Creating Tables

Cursor — Executing SQL

The cursor is the object through which you execute SQL and fetch results:

Parameterized Queries — Never Use String Formatting

Always use ? placeholders, never string formatting. String formatting leads to SQL injection:

Fetching Results

Connection as Context Manager

Using with conn: automatically commits on success and rolls back on exception:

Row Factory — Named Column Access

By default, rows are tuples. Set row_factory = sqlite3.Row to access columns by name:

UPDATE and DELETE

Getting the Last Inserted ID

executemany — Bulk Operations

Transactions

SQLite is transactional. In Python's sqlite3 module, INSERT/UPDATE/DELETE are automatically wrapped in a transaction unless you use isolation_level=None (autocommit mode):

Quick Reference