Python SQLite Tutorial: sqlite3, Queries and Transactions

1Python SQLite: overview

SQLite is a small database built into Python's sqlite3 module that stores data in tables you create, fill and query with SQL, without needing a separate server.

GoalBy the end you can create an SQLite database in Python, insert and read rows with ? placeholders, filter, sort and group data in SQL, protect against SQL injection, and use transactions to commit or roll back changes.

Before this lesson: Python Dictionaries, Python File Handling

2Python SQLite: diagram

Every SQLite program follows the same path: open a connection, run SQL with parameters, commit or roll back the changes, read the result rows, then close.

3Python SQLite: explained step by step

Lists and dictionaries vanish when your program ends, and files like CSV or JSON must be parsed by hand. A database stores rows in tables, answers questions such as "which orders are over 50?", and keeps data consistent when something fails. Python ships with SQLite in the standard sqlite3 module: no server, just a file, or a temporary database in memory as in this lesson.

Connect and create a table with sqlite3

sqlite3.connect() returns a connection. The name ":memory:" gives a fresh database that disappears on close, ideal for learning and tests; pass a file name like "shop.db" to save data on disk.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id    INTEGER PRIMARY KEY,
        name  TEXT NOT NULL,
        price REAL,
        stock INTEGER DEFAULT 0
    )
""")
print(conn.execute("SELECT COUNT(*) FROM products").fetchone())

SQL is its own language that Python sends as a string. IF NOT EXISTS makes the statement safe to run twice, because a plain CREATE TABLE on an existing table raises an error. execute() returns a cursor, which holds the results; fetchone() gives one row as a tuple, here (0,).

Insert rows safely with ? placeholders

Put a ? where each value goes and pass the values as a tuple. SQLite then treats them strictly as data. executemany() runs one statement for a whole list of tuples.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER)")

cur = conn.execute("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", ("Pen", 1.5, 100))
print("new id:", cur.lastrowid)

more = [("Notebook", 4.0, 40), ("Backpack", 29.9, 5), ("Lamp", 18.5, 12)]
cur = conn.executemany("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", more)
print("rows added:", cur.rowcount)
conn.commit()

Why f-string SQL causes SQL injection

Building a query with an f-string looks natural after learning f-strings, and it is the classic security hole. A quote or SQL fragment inside the value stops being data and becomes part of the statement:

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT, secret TEXT)")
conn.executemany("INSERT INTO users VALUES (?, ?)", [("asha", "x1"), ("ben", "y2")])

typed = "nobody' OR '1'='1"
unsafe = f"SELECT name FROM users WHERE name = '{typed}'"
print(unsafe)
print("unsafe:", conn.execute(unsafe).fetchall())
print("safe:  ", conn.execute("SELECT name FROM users WHERE name = ?", (typed,)).fetchall())

The unsafe query turned into WHERE name = 'nobody' OR '1'='1', which is true for every row, so the "search" leaked everyone. The placeholder version looks for a user literally named that odd text and finds nobody. The same bug in everyday life is a name like O'Brien breaking the quotes. Use placeholders for every value, always.

Read rows: fetchone, fetchall and looping

After a SELECT, loop over the cursor (one row at a time, best for big tables), or call fetchall() for a list. Rows are tuples, so you can unpack them in the for line. fetchone() returns None when nothing matches, and a cursor can be read only once.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (name TEXT, price REAL, stock INTEGER)")
conn.executemany("INSERT INTO products VALUES (?, ?, ?)", [
    ("Pen", 1.5, 100), ("Notebook", 4.0, 40), ("Backpack", 29.9, 5), ("Desk", 120.0, 2),
])

for name, price in conn.execute("SELECT name, price FROM products"):
    print(f"{name:<9}{price:>7.2f}")

sql = "SELECT name FROM products WHERE price >= ? ORDER BY price DESC LIMIT ?"
print(conn.execute(sql, (4, 2)).fetchall())
print(conn.execute("SELECT name FROM products WHERE price > 500").fetchone())

Let the database filter and sort instead of looping in Python: WHERE keeps matching rows, ORDER BY sorts (add DESC for largest first, like reverse=True in sorting), and LIMIT keeps the first few.

Change and remove rows with UPDATE and DELETE

Both need a WHERE clause; without one they hit every row. Afterwards cursor.rowcount says how many rows changed, a cheap check that your WHERE matched what you meant.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (name TEXT, stock INTEGER)")
conn.executemany("INSERT INTO products VALUES (?, ?)", [("Pen", 100), ("Lamp", 0), ("Desk", 2)])

cur = conn.execute("UPDATE products SET stock = stock + ? WHERE name = ?", (50, "Lamp"))
print("updated:", cur.rowcount)
cur = conn.execute("DELETE FROM products WHERE stock < ?", (10,))
print("deleted:", cur.rowcount)
print(conn.execute("SELECT * FROM products").fetchall())

Transactions: commit, rollback and with conn

Changes belong to a transaction, a group of statements that should succeed or fail together. commit() makes them permanent and rollback() discards them. On a file database, forgetting to commit silently loses your inserts. Using the connection in a with conn: block (see the with statement) commits on success and rolls back if an exception escapes; it does not close the connection.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
conn.executemany("INSERT INTO accounts VALUES (?, ?)", [("asha", 100), ("ben", 20)])
conn.commit()

def transfer(source, target, amount):
    try:
        with conn:
            conn.execute("UPDATE accounts SET balance = balance + ? WHERE owner = ?", (amount, target))
            conn.execute("UPDATE accounts SET balance = balance - ? WHERE owner = ?", (amount, source))
        return "ok"
    except sqlite3.IntegrityError as err:
        return "refused: " + str(err)

print(transfer("asha", "ben", 30))
print(transfer("ben", "asha", 500))
print(conn.execute("SELECT * FROM accounts ORDER BY owner").fetchall())

The second transfer adds 500 to asha first, then the CHECK rule rejects ben's negative balance. Both statements share one transaction, so the rollback undoes the first one too and no money appears from nowhere.

Dict-like rows with sqlite3.Row

Set conn.row_factory = sqlite3.Row to read columns by name instead of position. dict(row) makes a real dictionary, so a result becomes a list of dicts that is easy to print, test or save as JSON.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE students (name TEXT, grade INTEGER)")
conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 91), ("Ben", 78), ("Cara", 85)])

rows = conn.execute("SELECT name, grade FROM students ORDER BY grade DESC").fetchall()
print(rows[0]["name"], rows[0].keys())
print([dict(row) for row in rows])

Summarise with GROUP BY and aggregates

COUNT, SUM, AVG, MIN and MAX turn many rows into one value, and GROUP BY gives one result per group. HAVING filters groups the way WHERE filters rows.

Python

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (customer TEXT, amount REAL)")
conn.executemany("INSERT INTO orders VALUES (?, ?)", [
    ("asha", 20.0), ("ben", 15.5), ("asha", 35.0), ("cara", 9.99), ("ben", 40.0), ("asha", 5.0),
])
sql = """
    SELECT customer, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS total
    FROM orders GROUP BY customer HAVING SUM(amount) > ? ORDER BY total DESC
"""
for row in conn.execute(sql, (10,)):
    print(row)

Where to go next

To keep data between runs, connect to a file name and commit. To analyse a result as a table, pandas basics shows the library whose pandas.read_sql_query(sql, conn) loads a query straight into a DataFrame (not needed here). You can also fill a database from a CSV by passing its rows to executemany().

4Python SQLite: syntax

import sqlite3

conn = sqlite3.connect(":memory:")                  # or "app.db" for a file
conn.row_factory = sqlite3.Row                      # optional: rows by column name
conn.execute("CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, name TEXT)")
conn.execute("INSERT INTO t (name) VALUES (?)", ("Asha",))          # ? + tuple
conn.executemany("INSERT INTO t (name) VALUES (?)", [("Ben",), ("Cara",)])
rows = conn.execute("SELECT * FROM t WHERE name = ? ORDER BY id LIMIT 5", ("Ben",)).fetchall()
row  = conn.execute("SELECT * FROM t").fetchone()                   # None if empty
conn.execute("UPDATE t SET name = ? WHERE id = ?", ("Ana", 1))
conn.execute("DELETE FROM t WHERE id = ?", (2,))
with conn:                                          # commit, or rollback on error
    conn.execute("INSERT INTO t (name) VALUES (?)", ("Dan",))
conn.commit(); conn.rollback()                      # manual control
conn.close()

5Python SQLite: run the example

Press Run (or Ctrl + Enter). The output (step 6) appears directly below the code. Change anything and run it again.

Interactive example

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER)")
conn.executemany(
    "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)",
    [("Pen", 1.5, 100), ("Notebook", 4.0, 40), ("Backpack", 29.9, 5)],
)
conn.execute("UPDATE products SET stock = stock - ? WHERE name = ?", (2, "Backpack"))
conn.commit()

cheap = conn.execute(
    "SELECT name, price FROM products WHERE price < ? ORDER BY price", (10,)
).fetchall()
print(cheap)
total = conn.execute("SELECT SUM(price * stock) FROM products").fetchone()[0]
print(round(total, 2))
conn.close()

Expected output[('Pen', 1.5), ('Notebook', 4.0)]
399.7

7Python SQLite: how the example works

Hover or tap a step to highlight its lines in the example above.

Line 1sqlite3 is part of the standard library, so there is nothing to install.
Line 3connect(":memory:") opens a throw-away database that lives in RAM. Use a file name like "shop.db" to keep the data.
Line 4conn.execute() sends one SQL statement. CREATE TABLE defines the columns and their types; id INTEGER PRIMARY KEY is numbered automatically.
Line 5-8executemany() runs the same INSERT once per tuple in the list. Each ? is replaced by the matching value from the tuple, so no value is ever pasted into the SQL text.
Line 9The UPDATE lowers the Backpack stock by 2. Both values come through placeholders, and WHERE limits the change to one row.
Line 10commit() ends the transaction and makes the inserts and the update permanent.
Line 12-14The SELECT keeps products under 10, sorted by price. The one value is passed as the tuple (10,); the trailing comma is what makes it a tuple. fetchall() returns a list of tuples.
Line 15The list holds only the Pen and the Notebook, cheapest first.
Line 16SUM(price * stock) is calculated by the database across all rows and returns one row with one column, so fetchone()[0] picks out the number. It is 1.5*100 + 4.0*40 + 29.9*3.
Line 17round() hides tiny floating-point noise before printing.
Line 18close() releases the database. For an in-memory database this also deletes it.

8Python SQLite: common mistakes

Building SQL with an f-string

✗ Wrong

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE students (name TEXT)")
name = "O'Brien"
conn.execute(f"INSERT INTO students (name) VALUES ('{name}')")

sqlite3.OperationalError: near "Brien": syntax error

✓ Right

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE students (name TEXT)")
name = "O'Brien"
conn.execute("INSERT INTO students (name) VALUES (?)", (name,))
print(conn.execute("SELECT name FROM students").fetchall())

The apostrophe in the value ends the SQL string early. The same weakness lets users inject their own SQL. Use a ? placeholder and pass the value in a tuple; SQLite quotes it for you.

Writing (1) instead of (1,) for a single parameter

✗ Wrong

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE items (id INTEGER, name TEXT)")
row = conn.execute("SELECT * FROM items WHERE id = ?", (1)).fetchone()

sqlite3.ProgrammingError: parameters are of unsupported type

✓ Right

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE items (id INTEGER, name TEXT)")
row = conn.execute("SELECT * FROM items WHERE id = ?", (1,)).fetchone()
print(row)

(1) is just the number 1 in brackets. A one-element tuple needs a trailing comma: (1,). A string is even worse, because "Asha" is treated as four separate values and gives Incorrect number of bindings supplied.

Creating a table that already exists

✗ Wrong

import sqlite3

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

sqlite3.OperationalError: table products already exists

✓ Right

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE IF NOT EXISTS products (name TEXT)")
conn.execute("CREATE TABLE IF NOT EXISTS products (name TEXT)")
print("ready")

Set-up code often runs more than once, especially with a file database. Add IF NOT EXISTS so the second run quietly does nothing.

Forgetting to commit on a file database

✗ Wrong

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "shop.db")
conn = sqlite3.connect(path)
conn.execute("CREATE TABLE notes (text TEXT)")
conn.execute("INSERT INTO notes VALUES (?)", ("buy milk",))
conn.close()

check = sqlite3.connect(path)
print(check.execute("SELECT COUNT(*) FROM notes").fetchone()[0])

No error, but it prints 0: the row was never saved

✓ Right

import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "shop.db")
conn = sqlite3.connect(path)
conn.execute("CREATE TABLE notes (text TEXT)")
with conn:
    conn.execute("INSERT INTO notes VALUES (?)", ("buy milk",))
conn.close()

check = sqlite3.connect(path)
print(check.execute("SELECT COUNT(*) FROM notes").fetchone()[0])

Inserts and updates sit in an open transaction until you commit. Closing the connection without committing throws them away. Call conn.commit(), or use with conn: so it happens automatically.

Treating the cursor as a list

✗ Wrong

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE items (name TEXT)")
rows = conn.execute("SELECT * FROM items")
print(len(rows))

TypeError: object of type 'sqlite3.Cursor' has no len()

✓ Right

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE items (name TEXT)")
rows = conn.execute("SELECT * FROM items").fetchall()
print(len(rows))

execute() returns a cursor, not the rows. Call .fetchall() to get a list, or loop over the cursor. For a count, ask SQL: SELECT COUNT(*) ....

9Python SQLite: exercises

Write your answer, then press Check. The checks run in your browser.

1. Save scores in a table

Write code Easy

Write a function add_scores(conn, rows) that receives an open connection and a list of (player, points) tuples. It must create the table scores(player TEXT, points INTEGER) if it does not exist, insert every row with executemany and ? placeholders, commit, and return the number of rows in the table.

Your code

import sqlite3

def add_scores(conn, rows):
    # create the table (IF NOT EXISTS), insert the rows with placeholders, commit
    # then return how many rows the table holds
    pass

conn = sqlite3.connect(":memory:")
print(add_scores(conn, [("Asha", 30), ("Ben", 20)]))

Hint: Three statements on the connection: CREATE TABLE IF NOT EXISTS, executemany with (?, ?), then SELECT COUNT(*). fetchone()[0] gives the number.

One possible solution

import sqlite3

def add_scores(conn, rows):
    conn.execute("CREATE TABLE IF NOT EXISTS scores (player TEXT, points INTEGER)")
    conn.executemany("INSERT INTO scores (player, points) VALUES (?, ?)", rows)
    conn.commit()
    return conn.execute("SELECT COUNT(*) FROM scores").fetchone()[0]

conn = sqlite3.connect(":memory:")
print(add_scores(conn, [("Asha", 30), ("Ben", 20)]))

2. Predict what survives the rollback

Predict the output Easy

Read the program and type exactly what it prints. Think about which changes were committed and which were rolled back.

Read this code

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE tasks (title TEXT, done INTEGER)")
conn.execute("INSERT INTO tasks VALUES ('write', 0)")
conn.commit()

conn.execute("INSERT INTO tasks VALUES ('test', 0)")
conn.execute("UPDATE tasks SET done = 1 WHERE title = 'write'")
conn.rollback()
print(conn.execute("SELECT * FROM tasks").fetchall())

with conn:
    conn.execute("INSERT INTO tasks VALUES ('ship', 1)")
print(conn.execute("SELECT COUNT(*) FROM tasks WHERE done = 1").fetchone())

Your prediction — type the exact output

Hint: rollback() undoes everything since the last commit. The with conn: block commits its insert, and then only the 'ship' row has done = 1. fetchone() returns a tuple.

Actual output

[('write', 0)]
(1,)

2 more exercises → 6 more examples

Finished this lesson?

Progress is saved in this browser — create a free account to keep it across devices.

Questions learners ask

Do I need to install anything to use SQLite in Python?

No. The sqlite3 module is part of the standard library, and SQLite itself is built into it. There is no server to start. A database is a single file (or lives in memory with ":memory:"), which makes it ideal for small apps, prototypes, tests and local tools.

What is the difference between commit() and close()?

commit() saves the current transaction so the changes become permanent. close() only releases the connection and does not save anything: uncommitted changes are thrown away. Also remember that with conn: commits or rolls back but does not close the connection.

Why must I use ? placeholders instead of f-strings in SQL?

A placeholder sends the value separately from the SQL text, so it can never change the meaning of the query. With an f-string, a quote or SQL fragment in the value becomes part of the statement, which breaks queries and allows SQL injection. Placeholders also handle quoting and types for you. Only values can be parameters; table and column names cannot.

10Next lesson

Up nextPython LoggingThe logging module records messages from your program with a severity level, a source name and a destination you config…

← Python API Example: Build an API Client · All Practical Python lessons · Practise in the IDE

1Python SQLite: overview

2Python SQLite: diagram

3Python SQLite: explained step by step

Connect and create a table with sqlite3

Insert rows safely with ? placeholders

Why f-string SQL causes SQL injection

Read rows: fetchone, fetchall and looping

Change and remove rows with UPDATE and DELETE

Transactions: commit, rollback and with conn

Dict-like rows with sqlite3.Row

Summarise with GROUP BY and aggregates

Where to go next

4Python SQLite: syntax

5Python SQLite: run the example

7Python SQLite: how the example works

8Python SQLite: common mistakes

Building SQL with an f-string

✗ Wrong

✓ Right

Writing (1) instead of (1,) for a single parameter

✗ Wrong

✓ Right

Creating a table that already exists

✗ Wrong

✓ Right

Forgetting to commit on a file database

✗ Wrong

✓ Right

Treating the cursor as a list

✗ Wrong

✓ Right

9Python SQLite: exercises

1. Save scores in a table

2. Predict what survives the rollback

Questions learners ask

Further reading

10Next lesson