Python’s sqlite3 Package: A Complete Guide

Python includes a powerful built-in database package called sqlite3, which provides direct access to SQLite. Unlike many database systems, SQLite requires no separate server, no external installation in most Python environments, and no network configuration. It stores relational data in a single local file and is ideal for scripts, prototypes, desktop apps, local tooling, testing, and many small-to-medium production workloads.

For Python developers, sqlite3 is often the first real database interface they use—and for good reason. It combines: - Native Python support - SQL capabilities - Zero configuration - Transaction support - Portable storage

What Is sqlite3?

sqlite3 is Python’s standard library interface to SQLite.

Import it directly:

1

import sqlite3

No pip install needed.

It allows Python code to: - Create databases - Define tables - Insert records - Query data - Use transactions - Manage relational data with SQL

We have a full article on sqlite: SQLite3: What It Is And Why It's Used We also have a cheat sheet for Python's sqlite3 package: sqlite3 cheat sheet

Creating a Database Connection

The first step is opening a connection.

1
2
3

import sqlite3

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

This will: - Open example.db if it exists - Create it if it doesn’t

In-Memory Database

For temporary usage:

1

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

Useful for: - Unit tests - Fast temporary data processing - Disposable databases

Note: Data is not persistent with in-memory databases.

Creating a Cursor

Most database operations use a cursor.

1

cursor = conn.cursor()

The cursor executes SQL commands and fetches results.

Creating Tables

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

cursor.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
)
""")

# Then save changes:

conn.commit()

Sqlite uses standard SQL syntax

SQLite Data Types

SQLite uses flexible typing. Common types:

Handling Dates and Times:

SQLite stores dates typically as: - TEXT (2026-04-27) - INTEGER timestamps - REAL Julian dates

Example: created_at TEXT

Inserting Data

1
2
3
4
5
6

cursor.execute(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    ("Alice", 30)
)

conn.commit()

Why Use ? in your sql-query

This is parameter binding and allows for dynamic data input.

1
2
3
4

cursor.execute(
    "INSERT INTO users (name, age) VALUES (?, ?)",
    ("Alice", 30)
)

Inserts the first parameter inside the tuple in the name column (name = 'Alice'), the secon argument in the age column (age = 30).

Never use f-strings or unparameterized queries in general when running sql-queries. This is dangerous:

1
2

cursor.execute(
    f"... WHERE name = {name} ...."

Parameterized queries prevent SQL injection and formatting bugs!

Inserting Multiple Rows

````python users = [ ("Alice", 30), ("Bob", 25), ("Carol", 41) ]

cursor.executemany( "INSERT INTO users (name, age) VALUES (?, ?)", users )

conn.commit()

1
2
3
4
5
6

###Querying Data
```python
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

print(rows)

Output: [(1, 'Alice', 30), (2, 'Bob', 25)]

Fetch Methods

fetchone()

1

row = cursor.fetchone()

Returns one row.

fetchmany(n)

1

rows = cursor.fetchmany(10)

Returns n-number of rows

fetchall()

1

rows = cursor.fetchall()

Returns all rows

Filtering Results

1
2
3
4

cursor.execute(
    "SELECT name FROM users WHERE age > ?",
    (30,)
)

Filters the table for rows where the age column holds a value > 30

Updating Data

1
2
3
4
5
6

cursor.execute(
    "UPDATE users SET age = ? WHERE name = ?",
    (31, "Alice")
)

conn.commit()

Deleting Data

1
2
3
4
5
6

cursor.execute(
    "DELETE FROM users WHERE name = ?",
    ("Bob",)
)

conn.commit()

Transactions

SQLite supports ACID transactions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

conn.execute("BEGIN")

cursor.execute(...)
cursor.execute(...)

conn.commit()


# Rollback on failure:
conn.rollback()

Using Context Managers

Recommended when running database operations:

1
2
3
4
5

with sqlite3.connect("app.db") as conn:
    conn.execute(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        ("Dave", 28)
    )

This: - Commits automatically if successful - Rolls back on exception

Row Factories (Dictionary-Like Results)

Default rows are tuples.

Instead:

1
2
3
4
5
6
7

conn.row_factory = sqlite3.Row

# Then

row = cursor.fetchone()

print(row["name"])

Example Row Factory:

1
2
3
4
5
6
7
8

conn = sqlite3.connect("app.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
row = cursor.fetchone()

print(dict(row))

Very useful for readable code and further data processing rather than having to use tuples.

Creating Indexes

Improve performance and usability:

1
2
3

cursor.execute(
    "CREATE INDEX idx_users_name ON users(name)"
)

Use indexes for: - Frequent lookups - Filtering - Joins

Common Errors

Forgetting commit()

Writes won’t persist.

Using String Formatting in SQL

Unsafe: f"SELECT * FROM users WHERE id={x}"

Not Closing Connections

Use:

1

conn.close()

or context managers.

Performance Tips

Use Transactions for Bulk Inserts

Slow iteration: for item in data: insert + commit each time

Faster bulk or single operations: single transaction + executemany()

Use Indexes

Large tables need indexing.

Avoid Fetching Everything

Prefer:

1

LIMIT 100

When sqlite3 Is Great

Use it for: - Local applications - Desktop software - Internal tools - Prototypes - Tests - Learning SQL - Small web apps - Data scripts

When It’s Less Ideal

Use server databases instead when you need: - High write concurrency - Multiple app servers - Heavy analytics at scale - Complex access control - Networked DB infrastructure

Then consider PostgreSQL.

Example Mini App

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

import sqlite3

with sqlite3.connect("notes.db") as conn:
    cur = conn.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY,
        text TEXT
    )
    """)

    cur.execute(
        "INSERT INTO notes (text) VALUES (?)",
        ("Learn sqlite3",)
    )

    cur.execute("SELECT * FROM notes")

    for row in cur.fetchall():
        print(row)

Python’s sqlite3 package gives developers something simple: A real relational database with zero setup.

You can learn SQL, build apps, test systems, and store structured data immediately.

Many developers underestimate Python’s sqlite3 package because it is built in and simple to start with. But beneath that simplicity is a mature relational database engine capable of serious work.