The sqlite3 module provides a full SQL database with zero setup. No server needed—everything lives in a single file. Perfect for local apps, prototypes, and embedded storage.

Basic Connection

import sqlite3
 
# Connect (creates file if doesn't exist)
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()
 
# Execute SQL
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')
 
conn.commit()
conn.close()

Context Manager

Better pattern with automatic cleanup:

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    rows = cursor.fetchall()
# Connection closed automatically

Insert Data

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    
    # Single insert
    cursor.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ('Alice', 'alice@example.com')
    )
    
    # Multiple inserts
    users = [
        ('Bob', 'bob@example.com'),
        ('Charlie', 'charlie@example.com'),
    ]
    cursor.executemany(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        users
    )
    
    conn.commit()

Query Data

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    
    # Fetch all
    cursor.execute('SELECT * FROM users')
    all_users = cursor.fetchall()
    
    # Fetch one
    cursor.execute('SELECT * FROM users WHERE id = ?', (1,))
    user = cursor.fetchone()
    
    # Iterate
    cursor.execute('SELECT name, email FROM users')
    for name, email in cursor:
        print(f"{name}: {email}")

Named Parameters

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    
    # Use named parameters
    cursor.execute(
        'SELECT * FROM users WHERE name = :name',
        {'name': 'Alice'}
    )
    
    # Or with dict
    params = {'min_id': 5, 'max_id': 10}
    cursor.execute(
        'SELECT * FROM users WHERE id BETWEEN :min_id AND :max_id',
        params
    )

Row Factory

Get dictionary-like rows:

import sqlite3
 
conn = sqlite3.connect('myapp.db')
conn.row_factory = sqlite3.Row
 
cursor = conn.cursor()
cursor.execute('SELECT * FROM users WHERE id = 1')
user = cursor.fetchone()
 
# Access by name
print(user['name'])
print(user['email'])
 
# Also by index
print(user[0])
 
conn.close()

In-Memory Database

Perfect for testing:

import sqlite3
 
# Use :memory: for temporary database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
 
cursor.execute('CREATE TABLE test (id INTEGER, value TEXT)')
cursor.execute('INSERT INTO test VALUES (1, "hello")')
 
# Data exists only in memory, gone when connection closes

Transactions

import sqlite3
 
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()
 
try:
    cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)',
                   ('Dave', 'dave@example.com'))
    cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)',
                   ('Eve', 'eve@example.com'))
    conn.commit()  # Commit both
except sqlite3.Error:
    conn.rollback()  # Rollback on error
finally:
    conn.close()

Last Insert ID

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    cursor.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        ('Frank', 'frank@example.com')
    )
    conn.commit()
    
    print(f"Inserted with ID: {cursor.lastrowid}")

Update and Delete

import sqlite3
 
with sqlite3.connect('myapp.db') as conn:
    cursor = conn.cursor()
    
    # Update
    cursor.execute(
        'UPDATE users SET email = ? WHERE name = ?',
        ('newemail@example.com', 'Alice')
    )
    print(f"Updated {cursor.rowcount} rows")
    
    # Delete
    cursor.execute('DELETE FROM users WHERE id = ?', (5,))
    print(f"Deleted {cursor.rowcount} rows")
    
    conn.commit()

Custom Functions

Register Python functions for SQL:

import sqlite3
 
def uppercase(s):
    return s.upper() if s else None
 
conn = sqlite3.connect('myapp.db')
conn.create_function('upper_py', 1, uppercase)
 
cursor = conn.cursor()
cursor.execute('SELECT upper_py(name) FROM users')

Aggregate Functions

import sqlite3
 
class Average:
    def __init__(self):
        self.total = 0
        self.count = 0
    
    def step(self, value):
        if value is not None:
            self.total += value
            self.count += 1
    
    def finalize(self):
        return self.total / self.count if self.count else None
 
conn = sqlite3.connect('myapp.db')
conn.create_aggregate('py_avg', 1, Average)

Date and Time

import sqlite3
from datetime import datetime
 
# Enable datetime parsing
conn = sqlite3.connect(
    'myapp.db',
    detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
)
 
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY,
        name TEXT,
        created TIMESTAMP
    )
''')
 
# Insert with datetime
cursor.execute(
    'INSERT INTO events (name, created) VALUES (?, ?)',
    ('Meeting', datetime.now())
)
 
# Retrieve as datetime
cursor.execute('SELECT name, created FROM events')
for name, created in cursor:
    print(f"{name}: {created}")  # created is datetime object

Thread Safety

import sqlite3
import threading
 
# check_same_thread=False for multi-threaded access
conn = sqlite3.connect('myapp.db', check_same_thread=False)
 
# Better: connection per thread
local = threading.local()
 
def get_connection():
    if not hasattr(local, 'conn'):
        local.conn = sqlite3.connect('myapp.db')
    return local.conn

Backup

import sqlite3
 
# Backup to another database
source = sqlite3.connect('myapp.db')
backup = sqlite3.connect('backup.db')
 
source.backup(backup)
 
source.close()
backup.close()

Practical Example: Task Manager

import sqlite3
from datetime import datetime
 
class TaskDB:
    def __init__(self, path='tasks.db'):
        self.conn = sqlite3.connect(path)
        self.conn.row_factory = sqlite3.Row
        self._init_db()
    
    def _init_db(self):
        self.conn.execute('''
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY,
                title TEXT NOT NULL,
                done BOOLEAN DEFAULT FALSE,
                created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        self.conn.commit()
    
    def add(self, title):
        cursor = self.conn.execute(
            'INSERT INTO tasks (title) VALUES (?)', (title,)
        )
        self.conn.commit()
        return cursor.lastrowid
    
    def complete(self, task_id):
        self.conn.execute(
            'UPDATE tasks SET done = TRUE WHERE id = ?', (task_id,)
        )
        self.conn.commit()
    
    def list_pending(self):
        cursor = self.conn.execute(
            'SELECT * FROM tasks WHERE done = FALSE ORDER BY created'
        )
        return cursor.fetchall()
    
    def close(self):
        self.conn.close()

When to Use sqlite3

Use sqlite3 when:

  • Local application storage
  • Embedded databases
  • Prototypes and MVPs
  • Single-user applications
  • Testing with real SQL

Use PostgreSQL/MySQL when:

  • Multiple concurrent users
  • Network access needed
  • Advanced features required
  • High write volume

SQLite handles surprisingly heavy workloads. Many production apps use it successfully.

React to this post: