Storing and changing rows with SQLite¶

~20 min · prerequisite: How an HTTP request reaches your code and comes back

What you will be able to do¶

Create a table and insert rows with placeholders instead of pasted values.
Read, update, and delete rows with SELECT, UPDATE, and DELETE.
Explain why a change must be committed.

The idea¶

A variable disappears when your program stops. A database keeps data after that. SQLite stores a database in a single file, and Python's standard sqlite3 module reads and writes it, with no server to install.

Data is stored in tables. A table is like one spreadsheet sheet. Its columns name the fields, and each row is one record. Four operations, called CRUD, cover most work:

Create adds rows with INSERT.
Read finds rows with SELECT.
Update changes rows with UPDATE.
Delete removes rows with DELETE.

Never paste values into the SQL text. Write a placeholder, a question mark, and pass the values in a tuple. If you build SQL with an f-string from user input, a user can type SQL of their own. That is called SQL injection.

Changes are saved only when you call connection.commit(). Closing the connection without a commit discards them.

Worked example¶

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute(
    "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)"
)

cur = conn.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Ada", "ada@example.com"))
print("new id:", cur.lastrowid)

row = conn.execute("SELECT id, name, email FROM users WHERE id = ?", (1,)).fetchone()
print(row)

conn.execute("UPDATE users SET name = ? WHERE id = ?", ("Ada Lovelace", 1))
print(conn.execute("SELECT name FROM users WHERE id = ?", (1,)).fetchone())

conn.execute("DELETE FROM users WHERE id = ?", (1,))
conn.commit()
print("rows left:", conn.execute("SELECT COUNT(*) FROM users").fetchone()[0])
conn.close()

Line by line, this is what Python does:

sqlite3.connect(":memory:") opens a database that exists only in memory.
CREATE TABLE defines three columns. UNIQUE means no two rows may share an email.
INSERT fills the two ? placeholders with the tuple, in order.
cur.lastrowid is the id SQLite assigned, which is 1.
SELECT ... WHERE returns matching rows. fetchone() takes the first one.
UPDATE and DELETE change and remove the row whose id is 1. commit() saves the changes.

The output ends with rows left: 0.

Try it¶

Save this as tasks.py. It stores rows in a file, so they survive between runs:

import sqlite3

conn = sqlite3.connect("tasks.db")
conn.execute(
    "CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0)"
)
conn.execute("INSERT INTO tasks (title) VALUES (?)", ("buy milk",))
conn.commit()
for task_id, title, done in conn.execute("SELECT id, title, done FROM tasks ORDER BY id"):
    print(task_id, title, "done" if done else "open")
conn.close()

Run it twice from an empty folder:

$ python3 -I tasks.py
1 buy milk open
$ python3 -I tasks.py
1 buy milk open
2 buy milk open

The second run finds the first row still in tasks.db.

Run this on your own computer: the in-browser runner does not include the sqlite3 module. Save it as a .py file and run python3 file.py.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, pages INTEGER)")
conn.execute("INSERT INTO books (title, pages) VALUES (?, ?)", ("Dune", 412))
conn.execute("INSERT INTO books (title, pages) VALUES (?, ?)", ("Emma", 474))
conn.commit()

row = conn.execute("SELECT title, pages FROM books WHERE id = ?", (1,)).fetchone()
assert row == ("Dune", 412)

conn.execute("UPDATE books SET pages = ? WHERE id = ?", (420, 1))
conn.commit()
assert conn.execute("SELECT pages FROM books WHERE id = ?", (1,)).fetchone()[0] == 420

conn.execute("DELETE FROM books WHERE id = ?", (2,))
conn.commit()
assert conn.execute("SELECT COUNT(*) FROM books").fetchone()[0] == 1
conn.close()

Run this on your own computer: the in-browser runner does not include the sqlite3 module. Save it as a .py file and run python3 file.py.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT)")

payload = "Ada'); DROP TABLE users; --"
conn.execute("INSERT INTO users (name) VALUES (?)", (payload,))
conn.commit()

# The text is stored as plain data, and the table still exists.
assert conn.execute("SELECT name FROM users").fetchall() == [(payload,)]
conn.close()

Common mistakes¶

Forgetting commit(). Python shows no error. Insert a row, call close() without a commit, and a new connection finds no row. Add conn.commit().
Using a table that does not exist. Querying a table before CREATE TABLE runs gives:
```
sqlite3.OperationalError: no such table: users
```
Check the table name.

Wrong number of placeholders. Two ? with one value gives:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

Pass one value per ?.

Passing a string instead of a tuple. A string is read as characters, so "Ada" binds three values and fails with Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied. Write ("Ada",).

Breaking a UNIQUE column. Inserting the same email twice gives:

sqlite3.IntegrityError: UNIQUE constraint failed: users.email

Practice¶

Create a table notes (id INTEGER PRIMARY KEY, body TEXT), insert one row, and assert that COUNT(*) is 1.
Insert "Chen", "Ada", and "Bola" into people. Assert that SELECT name FROM people ORDER BY name returns them sorted.
Write add_task(conn, title) and list_open(conn), which returns titles where done = 0.
Write mark_done(conn, task_id) that sets done to 1, and assert that list_open no longer shows it.
Stretch: write find_by_name(conn, text) using LIKE ? with "%" + text + "%" as the value. Assert that the search "'; DROP TABLE people; --" returns no rows and that people still has its row.

Solution

```python import sqlite3

conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)") conn.execute("INSERT INTO notes (body) VALUES (?)", ("buy eggs",)) assert conn.execute("SELECT COUNT(*) FROM notes").fetchone()[0] == 1 ```

```python import sqlite3

conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE people (name TEXT)") conn.executemany("INSERT INTO people (name) VALUES (?)", [("Chen",), ("Ada",), ("Bola",)]) names = conn.execute("SELECT name FROM people ORDER BY name").fetchall() assert names == [("Ada",), ("Bola",), ("Chen",)] ```

```python def add_task(conn, title): conn.execute("INSERT INTO tasks (title, done) VALUES (?, 0)", (title,)) conn.commit()

def list_open(conn): rows = conn.execute("SELECT title FROM tasks WHERE done = 0 ORDER BY id").fetchall() return [title for (title,) in rows] ```

The table is CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER).

python def mark_done(conn, task_id): conn.execute("UPDATE tasks SET done = 1 WHERE id = ?", (task_id,)) conn.commit()

After mark_done(conn, 1), list_open(conn) no longer contains the first task.

```python import sqlite3

def find_by_name(conn, text): pattern = "%" + text + "%" return conn.execute("SELECT name FROM people WHERE name LIKE ?", (pattern,)).fetchall()

conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE people (name TEXT)") conn.execute("INSERT INTO people (name) VALUES (?)", ("Ada",)) assert find_by_name(conn, "'; DROP TABLE people; --") == [] assert conn.execute("SELECT COUNT(*) FROM people").fetchone()[0] == 1 ```

LIKE still reads % and _ as wildcards. A placeholder does not change that.

Recap¶

Use ? placeholders and pass values in a tuple. Never build SQL with f-strings from user input.
INSERT, SELECT, UPDATE, and DELETE make up CRUD.
Call commit() to save changes. A ":memory:" database disappears on close.