SQLite in Python: relational database basics

Your phone's messages, your browser's history, the settings of half the apps on your laptop — a huge share of them live in SQLite: a serverless relational database built right into Python. Nothing to install, the whole database is a single file, and the same approach later carries over to any other RDBMS.

SQLite is where we'll learn the basic pattern for working with a relational database: how to send a SQL query from Python and read the rows back into code.

This article gives a general overview of working with SQL from Python. For a deep dive into SQL itself, see the free SQL Academy course with practical exercises.

Connection and cursor

Python's standard library includes the sqlite3 module. Basic pattern: open a connection, get a cursor (which runs queries), close the connection.

Python 3.13
12345678910
import sqlite3

# Connect to the database (the file is created automatically)
connection = sqlite3.connect('tasks.db')
cursor = connection.cursor()

# ... queries go here

connection.close()
print("Done")
Done

It's nicer to wrap the connection in with: on exit it commits your changes automatically, or rolls them back if an error occurred inside. One subtlety: with sqlite3 the context manager does not close the connection, so you still need close() at the end.

Python 3.13
1234567891011
import sqlite3

connection = sqlite3.connect('tasks.db')

with connection:
    cursor = connection.cursor()
    # ... queries

connection.close()               # with doesn't close it — we close it ourselves

print("Done")
Done

Creating a table

In a relational database, data lives in tables. Each table has a schema: which columns, what types, what constraints. You create one with the SQL CREATE TABLE command:

Python 3.13
12345678910111213
import sqlite3

with sqlite3.connect('tasks.db') as connection:
    cursor = connection.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            completed BOOLEAN DEFAULT FALSE
        )
    ''')

print("Table tasks ready")
Table tasks ready

What the SQL parts mean:

• CREATE TABLE IF NOT EXISTS tasks — create table tasks if it doesn't exist yet
• id INTEGER PRIMARY KEY AUTOINCREMENT — integer primary key, auto-incremented
• title TEXT NOT NULL — text field, required
• completed BOOLEAN DEFAULT FALSE — boolean field, defaults to False

CRUD: the four basic operations

CRUD stands for Create / Read / Update / Delete: the four operations that cover almost all data work. Going forward we'll assume tasks table is already created.

CREATE: inserting data

Python 3.13
1234567891011121314
import sqlite3

with sqlite3.connect('tasks.db') as connection:
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO tasks (title) VALUES (?)",
        ("Learn SQLite",)
    )
    cursor.execute(
        "INSERT INTO tasks (title) VALUES (?)",
        ("Buy groceries",)
    )

print("Tasks added")
Tasks added

A crucial point: values are never embedded directly in the SQL string via f-strings or concatenation. Instead, use the ? placeholder and pass the value as the second argument to execute. This guards against SQL injection:

Python 3.13
123456789
# UNSAFE: user input concatenated into SQL
search = "' OR '1'='1"
cursor.execute(f"SELECT * FROM tasks WHERE title = '{search}'")
# SQL becomes: SELECT * FROM tasks WHERE title = '' OR '1'='1'
# the condition '1'='1' is always true → ALL tasks come back, not just the match

# SAFE: value passed separately
cursor.execute("SELECT * FROM tasks WHERE title = ?", (search,))
# looks for a task literally titled "' OR '1'='1" — nothing extra comes back

Here the attacker slipped a piece of SQL into an ordinary search field and got back every row in the table. The same trick bypasses a password check or deletes data. Rule: never concatenate user input into a SQL string, always pass it through ? parameters.

READ: querying data

Python 3.13
123456789
import sqlite3

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

for row in rows:
    print(row)
(1, 'Learn SQLite', 0)
(2, 'Buy groceries', 0)

cursor.fetchall() returns all rows as a list of tuples. Access fields by index: row[0] is id, row[1] is title, etc.

For a single row (say by id), use fetchone():

Python 3.13
12345678
import sqlite3

with sqlite3.connect('tasks.db') as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT title FROM tasks WHERE id = ?", (1,))
    row = cursor.fetchone()

print(row)
('Learn SQLite',)

UPDATE: changing data

Python 3.13
12345678910
import sqlite3

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

print("Task 1 marked completed")
Task 1 marked completed

WHERE id = ? is essential: without a condition, UPDATE updates every row in the table.

DELETE: removing data

Python 3.13
1234567
import sqlite3

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

print("Task 2 deleted")
Task 2 deleted

Same warning: without a WHERE, DELETE empties the whole table.

What's beyond this article

There are several production topics we're not covering in depth here but you should know they exist:

• Transactions (BEGIN/COMMIT/ROLLBACK): a group of changes runs atomically, all or none. The with sqlite3.connect(...) block commits automatically on exit.
• JOIN and querying several tables at once: most real schemas have multiple linked tables (users and their tasks, orders and items), and you pull data from them in a single query.
• Indexes: speed up lookups on frequently queried columns.

These are covered in the SQL Academy course.

Understanding check

Why pass values to execute() through the ? parameter instead of embedding them in the SQL string?

---

The next article covers SQLAlchemy Core: a library that builds SQL queries from Python expressions instead of strings. SQL injections are handled automatically, and the same code works across PostgreSQL, MySQL, and SQLite.