SQLite in Python with the sqlite3 Module

March 13, 2026 · 4 min read · Updated March 13, 2026 · beginner

python stdlib databases sqlite

SQLite is a lightweight, serverless database engine that lives in a single file. It’s perfect for small applications, prototyping, and situations where you need a database without the overhead of setting up a separate server. Python’s sqlite3 module gives you everything you need to work with SQLite databases directly from your Python code.

Why SQLite?

Unlike PostgreSQL or MySQL, SQLite doesn’t require a running database server. The entire database lives in one file on disk. This makes it ideal for embedded applications, mobile apps, caching, and any situation where you want the power of SQL without the infrastructure complexity.

Python comes with sqlite3 built in, so there’s nothing to install. You just import and start using it:

import sqlite3

# Connect to a database (creates it if it doesn't exist)
conn = sqlite3.connect('my_database.db')

Creating Tables

Before you can store data, you need to define your schema. SQL’s CREATE TABLE statement does this:

import sqlite3

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

# Create a table for storing users
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

conn.commit()
conn.close()

The PRIMARY KEY AUTOINCREMENT clause gives each row a unique ID automatically. The DEFAULT CURRENT_TIMESTAMP sets the creation time to when the row is inserted.

Inserting Data

You can insert data using parameterized queries to avoid SQL injection:

import sqlite3

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

# Insert a single user
cursor.execute(
    'INSERT INTO users (username, email) VALUES (?, ?)',
    ('alice', 'alice@example.com')
)

# Insert multiple users at once
users = [
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com'),
]
cursor.executemany(
    'INSERT INTO users (username, email) VALUES (?, ?)',
    users
)

conn.commit()
conn.close()

Using ? placeholders and passing values as a tuple keeps your code safe from injection attacks.

Querying Data

Fetching data is straightforward with SELECT statements:

import sqlite3

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

# Get all users
cursor.execute('SELECT * FROM users')
all_users = cursor.fetchall()
print(all_users)
# Output: [(1, 'alice', 'alice@example.com', '2026-03-13 10:00:00'), ...]

# Get one user by ID
cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
user = cursor.fetchone()
print(user)

# Get users matching a condition
cursor.execute('SELECT username, email FROM users WHERE username LIKE ?', ('a%',))
results = cursor.fetchall()
for username, email in results:
    print(f'{username}: {email}')

conn.close()

The fetchall() method returns all matching rows as a list of tuples. fetchone() returns just the first row. For large result sets, you can iterate over the cursor directly:

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

Updating and Deleting

Modify existing data with UPDATE and remove it with DELETE:

import sqlite3

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

# Update a user's email
cursor.execute(
    'UPDATE users SET email = ? WHERE username = ?',
    ('newemail@example.com', 'alice')
)

# Delete a user
cursor.execute('DELETE FROM users WHERE username = ?', ('bob',))

conn.commit()
conn.close()

Always use WHERE clauses with UPDATE and DELETE to avoid accidentally modifying or deleting all rows.

Using Transactions

Transactions let you group multiple operations that either all succeed or all fail:

import sqlite3

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

try:
    cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', ('dave', 'dave@example.com'))
    cursor.execute('INSERT INTO users (username, email) VALUES (?, ?)', ('eve', 'eve@example.com'))
    conn.commit()  # Save changes
except sqlite3.IntegrityError as e:
    conn.rollback()  # Undo all changes
    print(f'Error: {e}')

conn.close()

If either insert fails, the rollback() call undoes both changes. This keeps your database consistent.

Working with Row Factories

By default, SQLite returns rows as tuples. You can change this to get dictionaries instead:

import sqlite3

conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row  # Enables column access by name

cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

for row in cursor:
    print(row['username'], row['email'])  # Access by column name

conn.close()

This makes your code more readable since you can use column names instead of positional indices.

Context Managers

Python’s context manager syntax works with database connections for clean resource handling:

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT COUNT(*) FROM users')
    count = cursor.fetchone()[0]
    print(f'Total users: {count}')
# Connection automatically closed when exiting the with block

This is the cleanest way to work with databases — no need to manually call close().

Getting Started

The sqlite3 module gives you a full-featured database engine with zero setup. It’s perfect for persisting application data, building prototypes, or adding data storage to small projects.

The basics covered here — creating tables, inserting, querying, updating, and deleting — handle most use cases. As you need more advanced features, explore topics like indexes for performance, joins for combining tables, and transactions for data integrity.