How To Use SQLite with Python: A Complete Guide

Beginner

You need a database for your Python project, but setting up MySQL or PostgreSQL feels like overkill. SQLite is a lightweight, serverless database that comes built into Python — no installation, no configuration, no separate server process. Just import sqlite3 and start storing data in a single file.

Python’s built-in sqlite3 module provides a full-featured interface to SQLite databases. It supports SQL queries, transactions, parameterized statements, and works identically on every platform. Your database is just a file on disk that you can copy, backup, or share like any other file.

In this tutorial, you will learn how to create databases and tables, insert and query data, use parameterized queries to prevent SQL injection, handle transactions, work with context managers, and build a complete project. By the end, you will be able to add persistent data storage to any Python project in minutes.

SQLite in Python: Quick Example

Here is a complete working example that creates a database, inserts data, and queries it — all in under 15 lines.

# quick_sqlite.py
import sqlite3

conn = sqlite3.connect("example.db")
cursor = conn.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com"))
conn.commit()

cursor.execute("SELECT * FROM users")
for row in cursor.fetchall():
    print(row)

conn.close()

Output:

(1, 'Alice', 'alice@example.com')
(2, 'Bob', 'bob@example.com')

The ? placeholders in the INSERT statement are parameterized queries — they prevent SQL injection by keeping data separate from SQL code. Never use f-strings or string concatenation to build SQL queries.

What Is SQLite and When Should You Use It?

SQLite is a self-contained, file-based relational database engine. Unlike MySQL or PostgreSQL, it does not require a separate server process. The entire database lives in a single file on your filesystem.

Feature	SQLite	PostgreSQL	MySQL
Server required	No	Yes	Yes
Setup complexity	Zero	Medium	Medium
Concurrent writers	Limited	Excellent	Good
Max database size	281 TB	Unlimited	Unlimited
Best for	Apps, prototypes, scripts	Web apps, analytics	Web apps, CMS

Use SQLite when you need persistent storage for a single-user application, prototype, CLI tool, data processing script, or any project where simplicity matters more than concurrent access. Switch to PostgreSQL when you need multiple simultaneous writers or a web application with many users.

Creating Databases and Tables

Connecting to a database file creates it automatically if it does not exist. Use CREATE TABLE IF NOT EXISTS to make your scripts idempotent.

# create_tables.py
import sqlite3

conn = sqlite3.connect("bookstore.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        price REAL DEFAULT 0.0,
        published_year INTEGER,
        in_stock INTEGER DEFAULT 1
    )
""")

cursor.execute("""
    CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL
    )
""")

conn.commit()
print("Tables created successfully")

# Verify tables exist
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print("Tables:", [t[0] for t in tables])
conn.close()

Output:

Tables created successfully
Tables: ['books', 'categories']

The AUTOINCREMENT keyword makes SQLite assign a unique incrementing ID to each new row. NOT NULL prevents inserting rows with missing required fields.

Inserting Data Safely

Always use parameterized queries (the ? placeholder pattern) when inserting data. This prevents SQL injection and handles special characters in your data automatically.

# insert_data.py
import sqlite3

conn = sqlite3.connect("bookstore.db")
cursor = conn.cursor()

# Single insert with parameterized query
cursor.execute(
    "INSERT INTO books (title, author, price, published_year) VALUES (?, ?, ?, ?)",
    ("Python Crash Course", "Eric Matthes", 35.99, 2023)
)

# Bulk insert with executemany
books = [
    ("Fluent Python", "Luciano Ramalho", 55.99, 2022),
    ("Automate the Boring Stuff", "Al Sweigart", 29.99, 2019),
    ("Clean Code", "Robert C. Martin", 39.99, 2008),
]
cursor.executemany(
    "INSERT INTO books (title, author, price, published_year) VALUES (?, ?, ?, ?)",
    books
)

conn.commit()
print(f"Inserted {cursor.rowcount + 1} books")
conn.close()

Output:

Inserted 4 books

The executemany() method is significantly faster than calling execute() in a loop because it batches the operations into a single transaction.

Querying Data

SQLite supports full SQL query syntax. Use fetchall() for all results, fetchone() for a single row, or iterate directly over the cursor.

# query_data.py
import sqlite3

conn = sqlite3.connect("bookstore.db")
conn.row_factory = sqlite3.Row  # Enable column name access
cursor = conn.cursor()

# Select all books
cursor.execute("SELECT * FROM books ORDER BY price DESC")
print("All books by price:")
for row in cursor.fetchall():
    print(f"  {row['title']} by {row['author']} - ${row['price']}")

# Filtered query with parameter
cursor.execute("SELECT * FROM books WHERE price < ? ORDER BY title", (40.0,))
print("\nBooks under $40:")
for row in cursor.fetchall():
    print(f"  {row['title']} (${row['price']})")

# Aggregate query
cursor.execute("SELECT COUNT(*) as count, AVG(price) as avg_price FROM books")
stats = cursor.fetchone()
print(f"\nTotal: {stats['count']} books, Average price: ${stats['avg_price']:.2f}")
conn.close()

Output:

All books by price:
  Fluent Python by Luciano Ramalho - $55.99
  Clean Code by Robert C. Martin - $39.99
  Python Crash Course by Eric Matthes - $35.99
  Automate the Boring Stuff by Al Sweigart - $29.99

Books under $40:
  Automate the Boring Stuff ($29.99)
  Clean Code ($39.99)
  Python Crash Course ($35.99)

Total: 4 books, Average price: $40.49

Setting conn.row_factory = sqlite3.Row lets you access columns by name instead of index. This makes your code more readable and resilient to schema changes.

Using Context Managers

Context managers ensure your database connection is properly closed even if an error occurs. They also handle transaction commits automatically on success and rollbacks on failure.

# context_manager.py
import sqlite3

# The connection as context manager handles commits/rollbacks
with sqlite3.connect("bookstore.db") as conn:
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute(
        "INSERT INTO books (title, author, price, published_year) VALUES (?, ?, ?, ?)",
        ("Learning Python", "Mark Lutz", 59.99, 2013)
    )
    # Commit happens automatically when exiting the with block

    cursor.execute("SELECT COUNT(*) as count FROM books")
    print(f"Total books: {cursor.fetchone()['count']}")
# Connection is committed and closed here

Output:

Total books: 5

The with statement commits the transaction when the block exits normally and rolls it back if an exception occurs. This is the recommended pattern for all database operations.

Real-Life Example: Building a Contact Book CLI

# contact_book.py
import sqlite3

class ContactBook:
    def __init__(self, db_path="contacts.db"):
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS contacts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                phone TEXT,
                email TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        self.conn.commit()

    def add(self, name, phone=None, email=None):
        self.conn.execute(
            "INSERT INTO contacts (name, phone, email) VALUES (?, ?, ?)",
            (name, phone, email)
        )
        self.conn.commit()
        print(f"Added: {name}")

    def search(self, query):
        cursor = self.conn.execute(
            "SELECT * FROM contacts WHERE name LIKE ? OR email LIKE ?",
            (f"%{query}%", f"%{query}%")
        )
        return cursor.fetchall()

    def list_all(self):
        cursor = self.conn.execute("SELECT * FROM contacts ORDER BY name")
        return cursor.fetchall()

    def delete(self, contact_id):
        self.conn.execute("DELETE FROM contacts WHERE id = ?", (contact_id,))
        self.conn.commit()

    def close(self):
        self.conn.close()

# Demo usage
book = ContactBook(":memory:")
book.add("Alice Smith", "555-0101", "alice@example.com")
book.add("Bob Jones", "555-0102", "bob@example.com")
book.add("Charlie Brown", "555-0103", "charlie@example.com")

print("\nAll contacts:")
for c in book.list_all():
    print(f"  {c['name']} - {c['phone']} - {c['email']}")

print("\nSearch 'ali':")
for c in book.search("ali"):
    print(f"  {c['name']} - {c['email']}")

book.close()

Output:

Added: Alice Smith
Added: Bob Jones
Added: Charlie Brown

All contacts:
  Alice Smith - 555-0101 - alice@example.com
  Bob Jones - 555-0102 - bob@example.com
  Charlie Brown - 555-0103 - charlie@example.com

Search 'ali':
  Alice Smith - alice@example.com

Using ":memory:" creates an in-memory database for testing. Replace it with a filename like "contacts.db" for persistent storage. The class encapsulates all database operations, making it easy to extend with update, export, or import features.

Frequently Asked Questions

Is SQLite thread-safe in Python?

By default, a SQLite connection should only be used in the thread that created it. Set check_same_thread=False if you need to share a connection across threads, but be aware that concurrent writes can cause issues. For multi-threaded applications, give each thread its own connection.

How much data can SQLite handle?

SQLite can handle databases up to 281 terabytes. In practice, it works well for databases up to a few gigabytes. Performance depends more on query patterns than raw size -- proper indexing makes a bigger difference than database engine choice for most workloads.

How do I back up a SQLite database?

The simplest backup is to copy the database file when no writes are happening. For live databases, use Python's conn.backup() method which creates a consistent snapshot even during active writes. Schedule backups with cron or the schedule library.

How do I change the schema of an existing table?

SQLite has limited ALTER TABLE support. You can add columns with ALTER TABLE books ADD COLUMN rating REAL, but you cannot remove or rename columns in older SQLite versions. For complex changes, create a new table, copy data over, drop the old table, and rename the new one.

Should I use SQLite directly or through an ORM?

For simple scripts and small projects, the built-in sqlite3 module is perfect. For larger projects with complex queries and relationships, consider SQLAlchemy which provides an ORM layer. Start with raw SQL to understand the fundamentals, then add an ORM if the project grows.

Conclusion

SQLite with Python gives you a full relational database with zero configuration. We covered creating databases and tables, safe data insertion with parameterized queries, querying with filtering and aggregation, context managers for clean resource handling, and building a complete contact book application.

For the full SQLite documentation, visit docs.python.org/3/library/sqlite3. For SQLite SQL syntax, see sqlite.org/lang.

Parameterized Queries (Never Concatenate)

SQL injection is the #1 security mistake in database code. Never build queries with f-strings or string concatenation. Use parameterized queries — sqlite3 substitutes safely:

import sqlite3

con = sqlite3.connect("app.db")
cur = con.cursor()

# WRONG — injection vulnerability
user_input = "alice'; DROP TABLE users; --"
cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# RIGHT — placeholder + tuple
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
print(cur.fetchall())

# Named parameters work too
cur.execute(
    "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
    {"name": "Bob", "email": "bob@x.com", "age": 30},
)
con.commit()

The ? style is portable across DB-API drivers (psycopg2, mysql, etc.). The named :name style works in sqlite3 and reads better for queries with many parameters.

Context Managers for Transactions

Forgetting commit() after writes is the #2 mistake. The connection object is a context manager that auto-commits on exit, auto-rollbacks on exception:

import sqlite3

con = sqlite3.connect("app.db")

# Auto-commit on clean exit, auto-rollback on error
with con:
    con.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Carol", 25))
    con.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
    con.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
    # If any line raises, all three are rolled back atomically

# Standalone connection cleanup
con.close()

This is the safest write pattern — either the whole transaction commits, or nothing changes. Use it for any multi-statement update.

Row Factory for Named Columns

By default, fetchall() returns tuples — fine for two columns, painful for ten. Set row_factory = sqlite3.Row to get dict-like access:

con = sqlite3.connect("app.db")
con.row_factory = sqlite3.Row     # rows now act like namedtuples

cur = con.execute("SELECT id, name, email, created_at FROM users LIMIT 5")
for row in cur:
    # Access by column name OR index
    print(row["name"], row["email"])
    print(row[1], row[2])

    # Convert to dict if needed
    print(dict(row))

SQLite-Specific Features

SQLite has a few features other databases lack — worth knowing:

# INSERT OR REPLACE — upsert in one statement
cur.execute(
    "INSERT OR REPLACE INTO users (id, name) VALUES (?, ?)",
    (1, "Alice"),
)

# JSON support (built into modern SQLite)
cur.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, data TEXT)")
cur.execute(
    "INSERT INTO events (data) VALUES (json(?))",
    ('{"user_id": 42, "action": "login"}',),
)
cur.execute("SELECT json_extract(data, '$.user_id') FROM events WHERE id = 1")

# Full-text search via FTS5
cur.executescript('''
    CREATE VIRTUAL TABLE docs USING fts5(title, body);
    INSERT INTO docs VALUES ('Python', 'A programming language');
    INSERT INTO docs VALUES ('SQLite', 'An embedded database');
''')
cur.execute("SELECT title FROM docs WHERE docs MATCH ?", ("programming",))

Performance: Indexes and EXPLAIN QUERY PLAN

SQLite is fast — but only with the right indexes. Every column you filter or join on should be indexed:

# Create indexes
cur.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")
cur.execute("CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id, created_at)")

# Diagnose slow queries
cur.execute("EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?", ("a@b.com",))
print(cur.fetchall())
# Look for "SEARCH USING INDEX" — good
# Look for "SCAN" — slow, missing index

WAL Mode for Concurrent Reads

The default SQLite journaling mode (rollback) blocks readers during writes. Switch to WAL (Write-Ahead Logging) for better concurrency:

con = sqlite3.connect("app.db")
con.execute("PRAGMA journal_mode = WAL")
con.execute("PRAGMA synchronous = NORMAL")    # slightly less durable, much faster
con.execute("PRAGMA foreign_keys = ON")        # enforce FK constraints

# WAL is per-database — run once, persists across connections

WAL mode lets readers and writers operate without blocking each other (within reasonable limits). This is the single biggest performance unlock for read-heavy SQLite workloads.

Common Pitfalls

• Forgetting commit() without context manager. If you don't use with con:, you must call con.commit() after writes — or they're lost when the connection closes.
• Using one connection across threads. sqlite3 connections aren't thread-safe by default. Open one connection per thread, or use check_same_thread=False with a lock.
• String concatenation in queries. SQL injection is a real risk even in single-user apps — never build queries with user input. Parameterize everything.
• No foreign-key enforcement. SQLite doesn't enforce FK constraints by default. Set PRAGMA foreign_keys = ON on every connection.
• Path issues. A relative path to connect() resolves against the cwd, not the script's directory. Use an absolute path or pathlib.Path(__file__).parent / "app.db".

FAQ

Q: SQLite or PostgreSQL?
A: SQLite for embedded, single-process, prototype, or low-write apps. Postgres for multi-process, concurrent writes, or anything needing real network access. SQLite is shockingly fast for read-heavy workloads.

Q: How big can a SQLite database get?
A: 281 TB theoretical maximum, but practically anything over 100 GB is pushing it. Multi-GB databases work fine; multi-TB is when you should evaluate PostgreSQL or DuckDB.

Q: How do I back up a SQLite database?
A: While the DB is in use, run VACUUM INTO 'backup.db' for a consistent snapshot. Or use the built-in backup API: src.backup(dst) in the sqlite3 module.

Q: Can multiple processes write to the same SQLite file?
A: Yes, with WAL mode, but writes serialize. For high write concurrency, switch to Postgres. For occasional concurrent writes (a few per second), SQLite is fine.

Q: SQLAlchemy or raw sqlite3?
A: Raw sqlite3 for small scripts where SQL is the simplest expression of intent. SQLAlchemy when you have many tables, migrations, complex relationships, or want portability across databases.

Wrapping Up

SQLite is the most-deployed database in the world (every smartphone has hundreds of SQLite databases). For Python, it's built in — no install, no service to run, no network. Master parameterized queries, transactions via with con:, row factories for ergonomics, indexes for speed, and WAL mode for concurrency. Those five skills cover 90% of SQLite work and prevent 99% of the foot-guns.

Related Articles

• How To Use SQLAlchemy 2.0 ORM with Python
• How To Connect Python to PostgreSQL with psycopg
• How To Read and Write JSON Files in Python 3 with Examples