Python SQLite: Complete Beginner Database Guide

Many applications need to save information after the program closes. A list in memory disappears when the process ends, while a database keeps structured records and lets you search, update, and relate them efficiently. Python SQLite is an excellent first database because SQLite runs inside your application, stores data in a single file, and is available through Python’s standard library.

This beginner guide builds a small task database and explains connections, tables, parameters, transactions, queries, updates, and safe cleanup. No separate database server is required. If database terminology is new, start with the broader Python and SQLite introduction.

What SQLite is

SQLite is a relational database engine embedded in the application that uses it. Instead of connecting to a separate server process, your Python code opens a local database file. This makes SQLite suitable for desktop tools, prototypes, command-line applications, test environments, browser storage, and many small-to-medium workloads.

The standard sqlite3 module follows Python’s database API conventions. The official sqlite3 documentation is the primary reference for connection, cursor, transaction, and type behavior.

Create and connect to a database

Calling sqlite3.connect() opens an existing file or creates it. A context manager commits successful work and rolls back when an exception escapes the block. Closing the connection afterward releases resources.

import sqlite3

with sqlite3.connect("tasks.db") as connection:
    connection.execute("""
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            completed INTEGER NOT NULL DEFAULT 0
        )
    """)

SQLite uses dynamic typing, but declaring sensible column types communicates intent and enables useful conversions. INTEGER PRIMARY KEY gives each row a unique identifier. NOT NULL prevents incomplete records.

Insert data with parameters

Never build SQL by inserting user values with f-strings or string concatenation. Parameter placeholders separate SQL instructions from data and protect against SQL injection while handling quoting correctly.

import sqlite3

title = "Review pull request"

with sqlite3.connect("tasks.db") as connection:
    connection.execute(
        "INSERT INTO tasks (title) VALUES (?)",
        (title,),
    )

The comma in (title,) creates a one-item tuple. The question mark is SQLite’s positional placeholder. For several rows, use executemany().

tasks = [
    ("Write documentation",),
    ("Run the test suite",),
    ("Prepare release notes",),
]

with sqlite3.connect("tasks.db") as connection:
    connection.executemany(
        "INSERT INTO tasks (title) VALUES (?)",
        tasks,
    )

Parameterization is a core secure-coding habit. It is just as important as validating input and handling errors with the practices described in Python try and except.

Read rows from SQLite

A SELECT query returns a cursor. You can iterate over it, call fetchone(), or retrieve all rows with fetchall(). Iteration is often clearer and does not require materializing every row at once.

import sqlite3

with sqlite3.connect("tasks.db") as connection:
    cursor = connection.execute(
        "SELECT id, title, completed FROM tasks ORDER BY id"
    )

    for task_id, title, completed in cursor:
        status = "done" if completed else "open"
        print(task_id, title, status)

Return rows that behave like dictionaries

Tuple rows are lightweight, but named access is easier to maintain when a query contains many columns. Set row_factory to sqlite3.Row.

import sqlite3

with sqlite3.connect("tasks.db") as connection:
    connection.row_factory = sqlite3.Row

    row = connection.execute(
        "SELECT id, title, completed FROM tasks WHERE id = ?",
        (1,),
    ).fetchone()

    if row is not None:
        print(row["title"])

Checking for None is essential because fetchone() returns it when no record matches. The guide to None in Python explains why it differs from zero, an empty string, and False.

Update and delete records

Use a WHERE clause to target rows. Accidentally omitting it from an UPDATE or DELETE statement affects every row in the table.

import sqlite3

with sqlite3.connect("tasks.db") as connection:
    connection.execute(
        "UPDATE tasks SET completed = 1 WHERE id = ?",
        (2,),
    )

import sqlite3

with sqlite3.connect("tasks.db") as connection:
    connection.execute(
        "DELETE FROM tasks WHERE id = ?",
        (3,),
    )

For destructive operations, it is often wise to select the target first, show it to the user, and require confirmation. Backups are also easy because a small SQLite database is usually one file. The automatic backup tutorial provides ideas for protecting that file.

Filter and search with SQL

SQL performs filtering close to the data, so avoid loading every row into Python just to discard most of them. The next query returns open tasks whose title contains a search term.

import sqlite3

search = "test"

with sqlite3.connect("tasks.db") as connection:
    rows = connection.execute(
        """
        SELECT id, title
        FROM tasks
        WHERE completed = 0
          AND title LIKE ?
        ORDER BY title
        """,
        (f"%{search}%",),
    ).fetchall()

print(rows)

The percent signs are wildcard characters for LIKE, but the value is still passed as a parameter. Sorting in SQL is preferable when the database should define result order; for in-memory collections, see sort versus sorted.

Transactions and atomic changes

A transaction groups related operations. Either all of them succeed or none should remain. The connection context manager commits when the block finishes normally and rolls back when an exception occurs.

import sqlite3

def transfer_task(source_id: int, new_title: str) -> None:
    with sqlite3.connect("tasks.db") as connection:
        connection.execute(
            "UPDATE tasks SET completed = 1 WHERE id = ?",
            (source_id,),
        )
        connection.execute(
            "INSERT INTO tasks (title) VALUES (?)",
            (new_title,),
        )

Transactions matter whenever partial work would leave inconsistent data: moving money, adjusting inventory, registering an order, or updating several related tables.

A small repository class

Separating database operations from interface code makes the program easier to test. The following class contains a focused subset of task operations.

import sqlite3

class TaskRepository:
    def __init__(self, database: str = "tasks.db") -> None:
        self.database = database

    def add(self, title: str) -> int:
        with sqlite3.connect(self.database) as connection:
            cursor = connection.execute(
                "INSERT INTO tasks (title) VALUES (?)",
                (title,),
            )
            return int(cursor.lastrowid)

    def list_open(self) -> list[tuple[int, str]]:
        with sqlite3.connect(self.database) as connection:
            return connection.execute(
                """
                SELECT id, title
                FROM tasks
                WHERE completed = 0
                ORDER BY id
                """
            ).fetchall()

Type hints document expected values and improve editor assistance. Our Python type hints guide explains the syntax used here.

Common mistakes

• Building SQL with user input instead of placeholders.
• Forgetting to commit changes or misunderstanding transaction boundaries.
• Using fetchall() for an enormous result set.
• Assuming a missing row raises an exception instead of checking for None.
• Running UPDATE or DELETE without a carefully reviewed WHERE clause.
• Sharing one connection carelessly across threads.
• Storing dates in inconsistent formats without a documented strategy.

When SQLite is not the right choice

SQLite is not a replacement for every database server. A networked application with many simultaneous writers, centralized user management, advanced replication, or large operational workloads may need PostgreSQL, MySQL, or another server database. The comparison is about requirements, not whether SQLite is “real”—it is a complete transactional relational database engine.

For a server-based alternative, see the tutorial on connecting Python to MySQL.

Frequently asked questions

Do I need to install SQLite?

Standard Python distributions include the sqlite3 module and typically bundle SQLite support. You can verify it by importing sqlite3 and printing sqlite3.sqlite_version.

Can multiple programs open the database?

Yes, SQLite coordinates access and supports concurrent readers, but writes are serialized. Keep write transactions short and choose a server database when heavy concurrent writing is a core requirement.

Should I store booleans as INTEGER?

SQLite does not have a separate Boolean storage class. Python’s adapter commonly stores false and true as zero and one. Add constraints when the database must reject other values.

Final thoughts

Python SQLite is a practical bridge from in-memory scripts to persistent applications. Start with a clear schema, use parameterized queries, wrap related writes in transactions, and keep database code separated from presentation logic. Those habits scale well even when a future project moves to a larger database system.