Store Data with SQLite in Python

Python SQLite access, via the sqlite3 module in the Python standard library, provides a full SQL database engine that stores data in a single file. Unlike MySQL or PostgreSQL, SQLite requires no server installation, no configuration, and no separate process. Use it for desktop applications, prototypes, data analysis, and any project where a lightweight embedded database fits better than flat files like JSON.

python

import sqlite3
 
connection = sqlite3.connect("library.db")
cursor = connection.cursor()
 
cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author TEXT NOT NULL,
        year INTEGER
    )
""")

With the table created, insert a row and commit the change so it is saved permanently to the database file on disk:

python

cursor.execute(
    "INSERT INTO books (title, author, year) VALUES (?, ?, ?)",
    ("Dune", "Frank Herbert", 1965),
)
connection.commit()
 
for row in cursor.execute("SELECT * FROM books"):
    print(row)
 
connection.close()

Each row prints as a plain tuple in column order, matching the CREATE TABLE definition above, with id first and year last:

text

(1, 'Dune', 'Frank Herbert', 1965)

sqlite3.connect("library.db") opens the file, creating it if it does not exist. cursor.execute() runs SQL statements.

The ? placeholders in the INSERT statement are filled by the tuple, preventing SQL injection. commit() saves the transaction.

Creating tables and inserting data

Use execute() with SQL Data Definition Language (DDL) to create tables, and Data Manipulation Language (DML) to insert rows. executemany() is the efficient way to insert several rows from a list of tuples in a single call, rather than looping over the list and calling execute() once per row.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
cursor = connection.cursor()
 
cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        active INTEGER DEFAULT 1
    )
""")

With the table in place, insert three users in a single executemany() call and commit the result to the database file:

python

users = [
    ("Maya", "maya@example.com"),
    ("Devin", "devin@example.com"),
    ("Priya", "priya@example.com"),
]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)
connection.commit()
 
print(f"Inserted {cursor.rowcount} rows")

rowcount reports how many rows the last statement affected, confirming all three users from the original list were inserted successfully:

text

Inserted 3 rows

executemany() runs the same statement with different parameters for each tuple in the list. rowcount tells you how many rows were affected by the last operation.

Querying data

SELECT statements return rows that you can fetch one at a time, all at once, or iterate over directly, and choosing the right approach depends mostly on how many rows you expect and how much memory you want to use at once.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
cursor = connection.cursor()
 
cursor.execute("SELECT id, name, email FROM users WHERE active = ?", (1,))
 
print("fetchone:", cursor.fetchone())
print("fetchall:", cursor.fetchall())

fetchone() returns just the first matching row and advances an internal cursor position, so a second call to fetchall() right after picks up exactly where fetchone() left off and returns everything remaining in the result set:

text

fetchone: (1, 'Maya', 'maya@example.com')
fetchall: [(2, 'Devin', 'devin@example.com'), (3, 'Priya', 'priya@example.com')]

Iterating directly over the cursor is the most memory-efficient approach for large result sets:

python

for row in cursor.execute("SELECT name FROM users ORDER BY name"):
    print(row[0])

Parameterized queries

Always use ? placeholders instead of string formatting to prevent SQL injection, no matter how trusted the input value seems to be.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
cursor = connection.cursor()
 
def find_user(email):
    cursor.execute("SELECT name FROM users WHERE email = ?", (email,))
    result = cursor.fetchone()
    return result[0] if result else None
 
print(find_user("maya@example.com"))
print(find_user("nobody@example.com"))

The first call finds a matching row and returns the name; the second finds no match, so result is None and the function returns None too:

text

Maya
None

Using f"SELECT ... WHERE email = '{email}'" would be vulnerable to SQL injection.

The ? placeholder tells the database to treat the value as data, never as SQL code.

Transactions and context managers

Python SQLite connections wrap changes in transactions. Use commit() to save and rollback() to discard. The connection can be used as a context manager for automatic commit or rollback.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
 
try:
    with connection:
        connection.execute("UPDATE users SET active = 0 WHERE name = ?", ("Devin",))
        connection.execute("UPDATE users SET active = 1 WHERE name = ?", ("Maya",))
    print("Transaction committed")
except Exception as e:
    print(f"Transaction rolled back: {e}")

With the context manager, commit() is called automatically if the block exits normally, so both updates are saved together as a single atomic unit. If an exception occurs partway through, rollback() is called instead, undoing both changes so the database never ends up in a half-updated state.

Using the connection as a row factory

By default, query results return plain tuples, which means remembering column positions by index. Set row_factory to sqlite3.Row to access columns by name instead, which makes code that reads query results much easier to follow.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
 
for row in cursor.execute("SELECT name, email FROM users LIMIT 2"):
    print(f"{row['name']} <{row['email']}>")

Each row behaves like a dictionary as well as a tuple, so column names can be used directly in the format string instead of numeric indexes:

text

Maya <maya@example.com>
Devin <devin@example.com>

row['name'] is far more readable than row[0], especially once a query selects many columns and the numeric position stops being obvious at a glance. sqlite3.Row objects also support index access and iteration over column names, so existing tuple-based code keeps working unchanged.

Enabling WAL mode

In Python, SQLite's Write-Ahead Logging (WAL) mode allows concurrent reads while a write is in progress, improving performance for multi-user access.

python

import sqlite3
 
connection = sqlite3.connect("app.db")
connection.execute("PRAGMA journal_mode=WAL;")

WAL mode is persistent: once enabled, it stays turned on for the database file even after the connection closes and the program exits. It is recommended for most use cases, especially web applications with multiple threads or processes reading and writing the same database concurrently.

Practical example: a bookmark manager

Build a small application that stores, lists, and searches bookmarks. The class wraps the connection and table setup so callers never write raw SQL directly.

python

import sqlite3
 
class BookmarkManager:
    def __init__(self, db_path="bookmarks.db"):
        self.conn = sqlite3.connect(db_path)
        self.conn.execute("PRAGMA journal_mode=WAL;")
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS bookmarks (id INTEGER PRIMARY KEY AUTOINCREMENT, "
            "url TEXT NOT NULL, title TEXT, tags TEXT, created_at TEXT DEFAULT (datetime('now')))"
        )
    def add(self, url, title=None, tags=None):
        self.conn.execute("INSERT INTO bookmarks (url, title, tags) VALUES (?, ?, ?)", (url, title, tags))
        self.conn.commit()

add() handles inserting a new bookmark and committing the change in a single call, so callers never have to remember to commit separately. The remaining two methods cover reading data back out of the database, either by keyword search across the title and tags, or by most recent creation date:

python

    def search(self, keyword):
        return self.conn.execute(
            "SELECT * FROM bookmarks WHERE title LIKE ? OR tags LIKE ?",
            (f"%{keyword}%", f"%{keyword}%"),
        ).fetchall()
 
    def recent(self, limit=10):
        return self.conn.execute(
            "SELECT id, title, url FROM bookmarks ORDER BY created_at DESC LIMIT ?",
            (limit,),
        ).fetchall()

With the class defined, create a manager, add two bookmarks, and search for the one whose title or tags mention "python":

python

bm = BookmarkManager()
bm.add("https://python.org", "Python Homepage", "python,reference")
bm.add("https://sqlite.org", "SQLite Docs", "database,reference")
 
for row in bm.search("python"):
    print(f"{row[2]}: {row[1]}")

Only the first bookmark matches "python" in either its title or its tags, so the LIKE-based search returns a single result showing that entry's title and URL:

text

Python Homepage: https://python.org

The BookmarkManager class encapsulates all the database logic behind a small, focused interface, so calling code never has to write raw SQL directly. The LIKE query supports partial text search across both the title and tags columns. datetime('now') uses SQLite's built-in date function to timestamp each new bookmark automatically.

Common mistakes

Using string formatting for SQL values. f"SELECT * FROM users WHERE name = '{name}'" invites SQL injection. Always use ? placeholders and pass values as a tuple.

Forgetting to commit. Changes to the database are only saved after commit() is called explicitly. If the program exits or crashes before committing, all of those changes are silently lost, even though the code that made them appeared to run successfully. Use the with connection: context manager to auto-commit on success and roll back automatically on error.

Not closing connections. Open connections hold file locks that can block other processes from writing to the same database file. Always close connections with .close() or use a context manager to guarantee cleanup even when an error occurs. In long-running programs, consider a connection pool or reopening connections periodically.

Using SQLite for high-concurrency writes. SQLite serializes writes, meaning only one write can happen at a time regardless of how many threads or processes are involved. If many processes need to write simultaneously and at high volume, consider PostgreSQL or MySQL instead. SQLite excels at read-heavy workloads and applications with a single writer.