SQLite

What it is

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is embedded in Python via the built-in `sqlite3` module, making it easy to store and query relational data without requiring a separate database server.

Python's `sqlite3` module allows you to create SQLite databases, execute SQL queries, and manage transactions. It supports standard SQL syntax, parameterized queries, and in-memory databases for temporary storage.

Best known for: The most widely deployed database in the world — it is on every phone
Licence: Public domain
Watch for: Enable WAL mode for meaningfully better concurrent read performance

When to use it

The question documentation cannot answer for you — because it cannot recommend something else.

Reach for it when

Local application storage — desktop, mobile, embedded devices
You want SQL without running a server, connection strings or credentials
Read-heavy workloads, including surprisingly large ones

Look elsewhere when

Many processes or machines need concurrent write access — use PostgreSQL
You need fine-grained user permissions or network access built in

Installation

Built-in with Python (no installation required)

Getting started

The smallest useful thing you can do with it, and what each part means.

Creating a database and table

c

123456import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()
conn.close()

Creates an SQLite database file `example.db` and a `users` table if it doesn’t exist.

Inserting data

c

123456import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, age) VALUES (?,?)", ('Alice', 25))
conn.commit()
conn.close()

Inserts a row into the `users` table using a parameterized query to prevent SQL injection.

Querying data

c

12345678import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)
conn.close()

Fetches all rows from the `users` table and prints them.

Advanced usage

Where the library earns its place over a simpler alternative.

Updating data

c

cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Alice'))
conn.commit()

Updates the age of the user named Alice.

Deleting data

c

cursor.execute('DELETE FROM users WHERE name = ?', ('Alice',))
conn.commit()

Deletes the user named Alice from the table.

Using in-memory database

c

conn = sqlite3.connect(':memory:')

Creates a temporary database in RAM, useful for testing or temporary data storage.

Using transactions and context manager

c

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users (name, age) VALUES (?,?)', ('Bob', 30))

Uses a context manager to automatically commit transactions and close the connection safely.

Errors and fixes

The failures you are most likely to hit, and what actually resolves them.

sqlite3.OperationalError: Check SQL syntax, table existence, or database lock issues.
sqlite3.IntegrityError: Occurs when violating primary key or unique constraints. Ensure data integrity.
sqlite3.DatabaseError: General database error. Check connection, permissions, and file paths.

Best practices

Always use parameterized queries to prevent SQL injection.
Use context managers (`with` statement) for automatic connection handling.
Commit transactions to save changes, and rollback in case of errors.
Use indexes on columns that are frequently queried for better performance.
Avoid storing large binary data directly; use BLOBs carefully or external files.

Alternatives

Comparable options, and the reason you would pick one over the other.

Background

Why it exists, and what it was reacting to.

SQLite was created in 2000 by D. Richard Hipp. Its goal was to provide a lightweight, reliable, and portable SQL database engine that can be embedded in applications. It has become one of the most widely deployed databases in the world, powering applications from browsers to embedded devices.

Related libraries

Others in Databases & Caching for C.