Python Module 8 – Files, paths and data formats

SQLite databases with sqlite3

Create SQLite tables in Python, insert, query and update rows with sqlite3, pass values through placeholders, and commit or roll back transactions correctly.

Intermediate
20 minutes
Examples run with Python 3.14.8 and Pyodide 314.0.7
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answers to its 2 interview questions.

What you will learn

Create tables and insert, query, update and delete rows with sqlite3
Predict how SQLite stores dates and values that do not match a column's type
Use placeholders so that values can never change the SQL, and an allowlist for names
Group statements into a transaction that commits or rolls back as one
Explain what a second writer and a second thread meet, and keep writes fast

Before you start

What you’ll learn · Before you start

What you will learn

Create tables and insert, query, update and delete rows with sqlite3
Predict how SQLite stores dates and values that do not match a column's type
Use placeholders so that values can never change the SQL, and an allowlist for names
Group statements into a transaction that commits or rolls back as one
Explain what a second writer and a second thread meet, and keep writes fast

Before you start

On this page

Four books, three loans and one question, which books are overdue and by how many days, need no database server. SQLite keeps a complete SQL database in a single file, and Python ships with a driver for it, the sqlite3 module: sqlite3.connect("app.db") opens the file, creating it if needed, execute() runs SQL, fetchone() and fetchall() read results, and commit() saves your changes.

SQLite suits programs that keep their own data: desktop and mobile apps, command-line tools, tests, prototypes, caches and small websites. You will build that library database, see how SQLite stores dates and types, pass every value through placeholders, get transaction: A group of SQL statements that a database applies together or not at all: commit() makes every change permanent, and rollback() or an error undoes all of them. Other connections see the changes only after the commit. right, because a database that saves half of an operation is worse than one that saves nothing, and watch what happens when two connections write at once.

A connection runs SQL, and commit() keeps the result

The example creates a database file with two tables, fills them, asks which books are overdue and records a return:

A library database: create, insert, query, update Python · library.py

import sqlite3
from pathlib import Path

print("SQLite library:", sqlite3.sqlite_version)

Path("library.db").unlink(missing_ok=True)  # start from an empty database on every run
con = sqlite3.connect("library.db")  # a file; ":memory:" gives a database that lives only in memory
con.execute("""CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL)""")
con.execute("""CREATE TABLE IF NOT EXISTS loans (
    book_id INTEGER NOT NULL REFERENCES books(id),
    member TEXT NOT NULL,
    due TEXT NOT NULL)""")

books = [
    ("Malgudi Days", "R. K. Narayan"),
    ("The Guide", "R. K. Narayan"),
    ("Wings of Fire", "A. P. J. Abdul Kalam"),
    ("Train to Pakistan", "Khushwant Singh"),
]
con.executemany("INSERT INTO books (title, author) VALUES (?, ?)", books)
loans = [(1, "asha", "2026-03-01"), (3, "ravi", "2026-03-20"), (4, "meera", "2026-02-25")]
con.executemany("INSERT INTO loans (book_id, member, due) VALUES (?, ?, ?)", loans)
con.commit()  # the INSERTs opened a transaction; without commit() they would be lost

con.row_factory = sqlite3.Row  # rows that can be read by column name
query = """
    SELECT b.title, l.member, CAST(julianday(:today) - julianday(l.due) AS INTEGER) AS days_late
    FROM loans AS l JOIN books AS b ON b.id = l.book_id
    WHERE l.due < :today
    ORDER BY days_late DESC"""
for row in con.execute(query, {"today": "2026-03-10"}):
    print(f"{row['title']:<18} {row['member']:<6} {row['days_late']} days late")

cur = con.execute("DELETE FROM loans WHERE book_id = ?", (4,))  # Meera returns her book
con.commit()
print("loans removed:", cur.rowcount)
print("books by Narayan:", con.execute("SELECT count(*) FROM books WHERE author = ?", ("R. K. Narayan",)).fetchone()[0])
con.close()

Output

SQLite library: 3.51.0
Train to Pakistan  meera  13 days late
Malgudi Days       asha   9 days late
loans removed: 1
books by Narayan: 2

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 library.py

Version note

The first line differs in the browser: Pyodide’s Python is built with SQLite 3.39.0, while this lesson’s CPython 3.14.8 uses SQLite 3.51.0. Everything else prints the same. When an example must run in both, it sticks to SQL that 3.39.0 already supports.

In your browser, Pyodide 314.0.7 (CPython 3.14.2) prints:

SQLite library: 3.39.0
Train to Pakistan  meera  13 days late
Malgudi Days       asha   9 days late
loans removed: 1
books by Narayan: 2

Step through what each call did, from connect() to close(), and watch what the connection holds back until commit():

Interactive · message by message library.py, from connect() to close() Step through the example's calls. Watch what the connection holds back until commit(), and what reaches the file.

The drawing shows the whole scene; its 7 steps are listed below.

Every step as text

Open the file. sqlite3.connect("library.db") opens the database file, creating it because the example deleted the old one first, and returns a connection.
Create two tables. Two CREATE TABLE statements, run with con.execute(), make the tables books and loans in the file.
Insert in a transaction. executemany() inserts 4 books and 3 loans. The first INSERT opened a transaction, so the rows are not saved yet. No other connection sees them, and closing now would discard them.
commit(). con.commit() ends the transaction and writes the 7 rows to the file.
A query by name. With row_factory set to sqlite3.Row, the overdue query returns rows that can be read by column name. Two loans are past their due date, Meera's by 13 days and Asha's by 9.
Delete and count. A DELETE removes Meera's loan, and its cursor's rowcount is 1. commit() saves the change, and a count finds 2 books by R. K. Narayan.
Close. con.close() closes the connection. Everything that was committed stays in library.db for the next program that opens it.

sqlite3.connect(path) returns a connection. A path opens or creates a file; ":memory:" gives a private database that disappears when the connection closes, which is ideal for tests.
con.execute(sql, parameters) runs one statement and returns a The object that runs a statement and holds its result rows, which a loop or fetchone(), fetchmany() and fetchall() read. con.execute() in sqlite3 creates one for you and returns it.. You can loop over the cursor, or call fetchone() for one row and fetchall() for a list of them. Rows are tuples by default.
executemany(sql, rows) runs the same statement for every item of a sequence, the quick way to insert many rows.
con.commit() saves. The INSERT statements opened a transaction, and until it is committed nobody else sees the rows, and closing the connection would discard them.
con.row_factory = sqlite3.Row gives rows you can read by column name, as in row["title"], and still by position. The names are matched without regard to case.
cur.rowcount says how many rows an UPDATE or DELETE changed, and cur.lastrowid gives the id of the row an INSERT created.

Checkpoint

Check: Running SQL and saving it

Question 1 of 2 A program like library.py runs its INSERT statements and then calls con.close() without commit(). What does the next program that opens the file find?
Choose one answer.

An error, because the file is now damaged All the new rows, because close() saves them Half of the rows, the ones inserted first The tables, but none of the new rows

Show the answer: Question 1 of 2

Answer: The tables, but none of the new rows

Until commit(), the inserted rows belong to an open transaction that no other connection can see. Closing the connection discards it, so the file holds only what was committed before.
Question 2 of 2 In library.py, cur = con.execute("DELETE FROM loans WHERE book_id = ?", (4,)) returns a cursor. What is cur.rowcount?
Type a number.
Your answer row

Show the answer: Question 2 of 2

Answer: 1 row

rowcount says how many rows an UPDATE or DELETE changed. Only Meera's loan has book_id 4, so one row was deleted, and the example prints loans removed: 1.

SQLite has no date type, and a declared type is only a preference

The overdue query of the first example shows how to keep dates in SQLite: there is no date type, so the example stores them as ISO 8601 text such as YYYY-MM-DD, which sorts correctly as text, and uses SQLite’s julianday() function to subtract them. SQLite’s date functions also work with Julian day numbers and Unix times, but text is the form a person can read. Its columns are flexible in general: a column declared INTEGER prefers integers but will store text you give it, unless the table is declared STRICT. This program puts five values into a column declared INTEGER, asks typeof() how each one was stored, then tries the same in a STRICT table:

Five values in an INTEGER column, a STRICT table and dates as text Python · types_and_dates.py

import sqlite3

con = sqlite3.connect(":memory:")

# A column declared INTEGER prefers integers, but it stores what it cannot convert as it is.
con.execute("CREATE TABLE loose (n INTEGER)")
con.executemany("INSERT INTO loose VALUES (?)", [(42,), ("42",), ("forty-two",), (4.5,), (None,)])
for value, kind in con.execute("SELECT n, typeof(n) FROM loose"):
    print(f"{value!r:<12} stored as {kind}")

# A STRICT table refuses a value of the wrong type (SQLite 3.37.0 and newer).
con.execute("CREATE TABLE tight (n INTEGER) STRICT")
try:
    con.execute("INSERT INTO tight VALUES (?)", ("forty-two",))
except sqlite3.IntegrityError as err:
    print("STRICT table:", err)

# There is no date type: ISO 8601 text sorts in date order, and julianday() does the arithmetic.
due = ["2026-03-01", "2026-02-25", "2026-03-20"]
print("sorted as text:", sorted(due))
days = con.execute("SELECT julianday(?) - julianday(?)", ("2026-03-10", "2026-02-25")).fetchone()[0]
print("days between:", days)
con.close()

Output

42           stored as integer
42           stored as integer
'forty-two'  stored as text
4.5          stored as real
None         stored as null
STRICT table: cannot store TEXT value in INTEGER column tight.n
sorted as text: ['2026-02-25', '2026-03-01', '2026-03-20']
days between: 13.0

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 types_and_dates.py

The first five lines of the output, with the reason for each:

Value inserted	Stored as	Why
`42`	integer	it is an integer already
`'42'`	integer	text that converts to an integer without loss
`'forty-two'`	text	it does not convert, so it is kept as it is
`4.5`	real	as an integer it would lose its `.5`
`None`	null	`NULL` is a storage class of its own

SQLite calls this preference type affinity: SQLite's preference for a type in a column declared INTEGER, REAL, TEXT or NUMERIC: a value that converts without loss is stored as that type, and anything else is kept as it is. A STRICT table refuses values it cannot convert instead.: a column’s declared type is a preference, not a rule. A STRICT table, which SQLite has offered since version 3.37.0, still converts what it can without loss, but refuses a value it cannot convert, such as 'forty-two', with IntegrityError. The dates sort correctly as text because ISO 8601 puts the year first, then the month and the day, and julianday() turns each into a day number, so subtracting two of them gives 13 days. Press Edit, change the second date of the last query and run it again: the difference follows.

Checkpoint

Check: Dates and types in SQLite

Question 1 of 2 A table is created with CREATE TABLE t (n INTEGER), and the values "7" and "seven" are inserted. What does typeof(n) say for each?
Choose one answer.

integer, then an IntegrityError integer, then null integer, then text text, then text

A hint

Which of the two texts converts to an integer without losing anything?

Show the answer: Question 1 of 2

Answer: integer, then text

A declared type is a preference called type affinity. Text that converts without loss becomes an integer; other text stays text, unless the table is STRICT, which refuses it.
Question 2 of 2 The overdue query compares the due dates with l.due < :today, as plain text. Why does that give the right answer?
Choose one answer.

julianday() runs on every comparison behind the scenes SQLite recognises dates and compares them as dates ISO 8601 text puts the year first, then the month and the day, so text order is date order Text comparison is always right for dates, whatever the format

Show the answer: Question 2 of 2

Answer: ISO 8601 text puts the year first, then the month and the day, so text order is date order

With every date written year first and padded to the same length, comparing the text compares the dates. julianday() is needed only for arithmetic, such as the days between two dates.

A value formatted into SQL can rewrite the query

The tempting way to put a value into a query is an f-string. It fails on the first value that contains a quote, and the failure is a warning sign of something worse:

A name with a quote, formatted in and passed as a parameter Python · quotes_and_placeholders.py

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE members (name TEXT, city TEXT)")
con.executemany("INSERT INTO members VALUES (?, ?)", [("Asha", "Pune"), ("Sean O'Brien", "Dublin")])

name = "Sean O'Brien"  # an ordinary value with a quote in it

# Formatting the value into the SQL text makes the quote part of the SQL.
sql = f"SELECT city FROM members WHERE name = '{name}'"
print(sql)
try:
    con.execute(sql)
except sqlite3.OperationalError as err:
    print("OperationalError:", err)

# A placeholder sends the value separately: it is never read as SQL, whatever it contains.
print(con.execute("SELECT city FROM members WHERE name = ?", (name,)).fetchone())
print(con.execute("SELECT city FROM members WHERE name = :name", {"name": name}).fetchone())

# Placeholders stand for values only, never for table or column names: check those against an allowlist.
SORTABLE = {"name", "city"}
column = "city"
if column not in SORTABLE:
    raise ValueError(f"cannot sort by {column!r}")
print([row[0] for row in con.execute(f"SELECT name FROM members ORDER BY {column}")])

# execute() runs exactly one statement.
try:
    con.execute("SELECT 1; SELECT 2")
except sqlite3.ProgrammingError as err:
    print("ProgrammingError:", err)

Output

SELECT city FROM members WHERE name = 'Sean O'Brien'
OperationalError: near "Brien": syntax error
('Dublin',)
('Dublin',)
["Sean O'Brien", 'Asha']
ProgrammingError: You can only execute one statement at a time.

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 quotes_and_placeholders.py

Formatted into the text, Sean O'Brien ends the SQL string literal after O and leaves Brien' as stray SQL, so SQLite reports a syntax error. That is the harmless case. The same mechanism lets a value that someone writes on purpose change what a query does, for example to match every row or to skip a check, which is called SQL injection: A flaw where text from outside is put into the text of an SQL statement, so that crafted input changes what the statement does, for example to match every row. Placeholders prevent it, because values travel apart from the SQL. and is one of the most common security flaws in applications. Escaping quotes by hand is not a fix, because the rules differ between databases and are easy to get wrong.

Try it

Press Edit, change name to "D'Souza" and run the example again. The formatted query fails the same way, near "Souza", while both placeholder lines print None, because no member has that name.

The fix is to never put values into SQL text at all. With a placeholder (SQL): A marker such as ? or :name where a value goes in an SQL statement. The driver sends the values apart from the SQL text, and the database binds them to the compiled statement as data., the program sends the SQL and the values separately, and SQLite compiles the statement before it sees any value:

A value formatted into SQL, and the same value passed as a parameter

Text description of the diagram

The diagram shows two paths, one above the other.

Formatted into the text: the program builds the SQL with an f-string, such as WHERE name = '{name}'. SQLite then compiles text that already contains the value, so a quote inside the value ends the string early and changes the SQL. At best that is a syntax error; at worst the input rewrites the query.
Passed as a parameter: the program writes WHERE name = ? and passes the value separately, as a tuple. SQLite compiles the text with the question mark in it, and binds the value to the compiled statement afterwards, as data. Whatever characters the value contains, it can never become part of the SQL.

sqlite3 accepts two placeholder styles. ? (the qmark style) takes a sequence of values in order, and :name (the named style) takes a dictionary. Since Python 3.14, mixing them up, named placeholders with a sequence, raises ProgrammingError instead of a warning. Three more rules come from the example:

Placeholders stand for values only. A table or column name cannot be a parameter. When the user chooses a sort column, check the name against a fixed set of names your program knows, an allowlist: A list of what is accepted, such as the URL schemes http and https; anything not on it is refused. Unlike a denylist, it also refuses values nobody thought of. More in “XML and HTML parsing without security holes”, as SORTABLE does, and only then put it in the SQL.
execute() runs exactly one statement, and refuses a string that holds two. executescript() runs several, and is meant for SQL you wrote yourself, such as a schema file, never for text that contains user input.
Validate inputs as well. Placeholders stop values from changing the SQL, but a quantity should still be a positive number and a name should still have a sensible length. The OWASP cheat sheet on preventing SQL injection puts parameterised queries first, calls escaping input by hand fragile, and adds allowlist validation as a further layer and as the defence for names that cannot be parameters.

Checkpoint

Check: Placeholders and allowlists

Question 1 of 2 Which call looks up a member by a name the user typed, safely?
Choose one answer.

con.execute("SELECT * FROM members WHERE name = '%s'" % name) con.execute("SELECT * FROM members WHERE name = ?", name) con.execute("SELECT * FROM members WHERE name = ?", (name,)) con.execute(f"SELECT * FROM members WHERE name = '{name}'")

Show the answer: Question 1 of 2

Answer: con.execute("SELECT * FROM members WHERE name = ?", (name,))

A placeholder with a tuple of parameters sends the value separately from the SQL text. The f-string and % versions put the value into the SQL itself. The last one passes a string where a sequence is expected, so each character would count as a separate parameter and the call fails unless the name has exactly one character.
Question 2 of 2 A report lets the user choose the column to sort by. How do you put that column into the SQL?
Choose one answer.

Wrap it in single quotes Check the name against a fixed set of allowed column names, then put it into the SQL text Escape it with html.escape() and format it in With a placeholder, ORDER BY ?

Show the answer: Question 2 of 2

Answer: Check the name against a fixed set of allowed column names, then put it into the SQL text

Placeholders stand for values only, and ORDER BY ? would sort by a constant. Names cannot be parameters, so the program must only ever insert a name it chose itself, which an allowlist guarantees.

with con: commits a transaction, or rolls all of it back

A transaction groups statements so that they take effect together or not at all. A transfer between two wallets is the classic case: the credit and the debit must both happen, or neither. The connection is a context manager: An object with __enter__() and __exit__() methods. The with statement calls __enter__() before its block and __exit__() after it, however the block ends, so a file is closed or a lock released even after an error. More in “Context managers and the with statement” for exactly this:

A transfer that either completes or leaves no trace Python · transaction.py

import sqlite3
from contextlib import closing

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE wallets (owner TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
con.executemany("INSERT INTO wallets VALUES (?, ?)", [("asha", 500), ("ravi", 100)])
con.commit()


def transfer(con, payer, payee, amount):
    with con:  # commits when the block succeeds, rolls back when it raises
        con.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, payee))
        con.execute("UPDATE wallets SET balance = balance - ? WHERE owner = ?", (amount, payer))


def balances(con):
    return dict(con.execute("SELECT owner, balance FROM wallets ORDER BY owner"))


transfer(con, "asha", "ravi", 200)
print("after 200:", balances(con))
try:
    transfer(con, "asha", "ravi", 400)  # Asha has only 300 left: the CHECK constraint fails
except sqlite3.IntegrityError as err:
    print("IntegrityError:", err)
print("after the failed 400:", balances(con))  # Ravi's credit was rolled back too
print("still open:", con.execute("SELECT count(*) FROM wallets").fetchone()[0], "wallets")  # with did not close it
con.close()

# closing() closes the connection at the end of its block; the inner with handles the transaction.
with closing(sqlite3.connect(":memory:")) as con2:
    with con2:
        con2.execute("CREATE TABLE t (x)")
        con2.execute("INSERT INTO t VALUES (1)")
    print("rows in t:", con2.execute("SELECT count(*) FROM t").fetchone()[0])
try:
    con2.execute("SELECT 1")
except sqlite3.ProgrammingError as err:
    print("ProgrammingError:", err)

Output

after 200: {'asha': 300, 'ravi': 300}
IntegrityError: CHECK constraint failed: balance >= 0
after the failed 400: {'asha': 300, 'ravi': 300}
still open: 2 wallets
rows in t: 1
ProgrammingError: Cannot operate on a closed database.

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 transaction.py

Step through both transfers and watch the balances inside each transaction:

Interactive · message by message Two transfers, one committed and one rolled back Step through transaction.py. Watch the balances inside the transaction, and what with con: does when the block ends.

The drawing shows the whole scene; its 7 steps are listed below.

Every step as text

Credit Ravi. transfer(con, "asha", "ravi", 200) first adds 200 to Ravi's wallet. The UPDATE opens a transaction, so the change is not saved yet.
Debit Asha. The second UPDATE takes 200 from Asha. Both changes now wait in the same transaction.
Commit. The with block ends without an error, so with con commits both changes together.
Credit 400. The second transfer adds 400 to Ravi first, inside a new transaction.
The CHECK fails. Taking 400 from Asha's 300 would leave −100, so the CHECK (balance >= 0) constraint stops the second UPDATE with IntegrityError.
Roll back. The error leaves the with block, so with con rolls the whole transaction back, Ravi's credit included. The balances are 300 and 300 again.
Still open. with con managed the transaction, not the connection, so con still answers and counts 2 wallets. Only close(), or contextlib.closing(), closes it.

The second transfer adds 400 to Ravi’s wallet, then fails the table’s CHECK (balance >= 0) constraint on Asha’s. Because both statements ran inside with con:, the IntegrityError rolled back the whole transaction, Ravi’s credit included, and the balances are exactly what they were before. When the block ends without an error, with con: commits.

Version note

Python 3.14 removed sqlite3.version and sqlite3.version_info, which described the module rather than the library; read sqlite3.sqlite_version for the SQLite version, as the first example does. Python 3.15 makes every sqlite3.connect() parameter except the database keyword-only, so pass them by name, as in sqlite3.connect(path, timeout=10).

Two details catch people out. First, with con: manages the transaction, not the connection: the documentation says it neither opens a new transaction nor closes the connection, which is why con still answers after the block. Close connections yourself with con.close(), or with contextlib.closing(), as the end of the example does; since Python 3.13 a connection that is garbage-collected without close() emits a ResourceWarning. Second, by default sqlite3 opens transactions implicitly, before an INSERT, UPDATE, DELETE or REPLACE, so a SELECT on its own starts none. Python 3.12 added the autocommit argument to connect(), and the documentation recommends autocommit=False, which keeps a transaction open at all times as the DB-API standard expects. Either way, commit explicitly or use with con:.

Key idea

Put the statements that belong together in one with con: block: it commits all of them when the block succeeds and rolls all of them back when it raises, but it leaves the connection open.

Checkpoint

Check: Transactions with with con

Question 1 of 2 In transaction.py, what is Ravi's balance right after the failed transfer of 400?
Type a number.
Your answer

A hint

The credit of 400 ran first. What did the rollback do with it?

Show the answer: Question 1 of 2

Answer: 300

The credit of 400 and the failed debit were in one transaction, so the IntegrityError rolled both back. Ravi is left with the 300 he had after the first transfer, as the example prints.
Question 2 of 2 A with con: block ends without an error. Which is true afterwards?
Choose one answer.

The transaction is still open until close() The transaction is committed, and con is still open The transaction is committed, and con is closed Every later statement now commits by itself

Show the answer: Question 2 of 2

Answer: The transaction is committed, and con is still open

with con commits when the block succeeds and rolls back when it raises. It neither opens a new transaction nor closes the connection, which is why the example's con still counts 2 wallets after the block.

Two connections cannot write at once, so keep transactions short

SQLite allows one writer at a time. Any other connection that tries to write must wait until the lock is released, by default up to 5 seconds (the timeout argument of connect()), and then raises OperationalError: database is locked. This program opens two connections to one file and lets them collide, with a timeout of 0.2 seconds so that the run is quick. It also starts a thread, which the Python that runs in your browser cannot do, so this page shows its recorded output:

Two connections and a thread on one SQLite file Python · one_writer.py

import sqlite3
import threading
from pathlib import Path

Path("shop.db").unlink(missing_ok=True)  # start from an empty database on every run
a = sqlite3.connect("shop.db")
a.execute("CREATE TABLE stock (item TEXT, qty INTEGER)")
a.commit()
b = sqlite3.connect("shop.db", timeout=0.2)  # waits at most 0.2 s for a lock, instead of 5 s

a.execute("INSERT INTO stock VALUES ('tea', 40)")  # A's transaction is open: A holds the write lock
print("A is writing, in a transaction:", a.in_transaction)
try:
    b.execute("INSERT INTO stock VALUES ('rice', 25)")  # B waits 0.2 s for the lock, then gives up
except sqlite3.OperationalError as err:
    print("B:", err)
    b.rollback()
print("B reads:", b.execute("SELECT count(*) FROM stock").fetchone()[0], "rows")  # A's row is not committed yet

a.commit()  # the lock is free again
b.execute("INSERT INTO stock VALUES ('rice', 25)")
b.commit()
print("after both commits:", a.execute("SELECT item, qty FROM stock ORDER BY item").fetchall())


def use_from_another_thread():
    try:
        a.execute("SELECT 1")
    except sqlite3.ProgrammingError as err:
        print("another thread:", str(err).split(".")[0])


worker = threading.Thread(target=use_from_another_thread)
worker.start()
worker.join()
a.close()
b.close()

Output

A is writing, in a transaction: True
B: database is locked
B reads: 0 rows
after both commits: [('rice', 25), ('tea', 40)]
another thread: SQLite objects created in a thread can only be used in that same thread

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 one_writer.py

Step through the run and watch who holds the write lock on shop.db:

Interactive · message by message Two connections that write to one file Step through one_writer.py. Watch who holds the write lock on shop.db, and what the second connection meets while it waits.

The drawing shows the whole scene; its 8 steps are listed below.

Every step as text

A writes. A inserts tea, 40. The INSERT opens A's transaction, and A now holds the database's write lock until it commits.
B waits. B inserts rice, 25, and needs the same lock. It waits for it, up to its timeout of 0.2 seconds; the default is 5 seconds.
B gives up. The lock is still A's when the 0.2 seconds are over, so B raises OperationalError, database is locked.
B rolls back. The failed INSERT had already begun B's transaction. B rolls it back, so it keeps no lock that A's commit would have to wait for.
B can still read. Reading needs no write lock, so B counts the rows. It sees 0, because A's row is not committed yet.
A commits. A's commit writes tea to the file and frees the write lock.
B tries again. Now B's INSERT and commit go through, and the table holds rice, 25 and tea, 40.
Another thread. A thread that did not create A tries to use it. sqlite3 refuses with ProgrammingError, because check_same_thread is True by default.

B’s rollback() is not decoration. The failed INSERT had already begun B’s transaction, and the read that follows would have kept a read lock inside it; SQLite’s locking documentation says a writer may have to wait until such read locks clear before it can commit, so A’s commit() would then have failed in turn. The last line shows the thread rule: by default sqlite3 refuses to let one thread use a connection that another thread created, so open a separate connection in every thread.

Two habits follow. Keep write transactions short, because every other writer waits for them. And batch writes into transactions: SQLite’s FAQ explains that by default SQLite waits until the data of each transaction is safely stored on the disk, so it runs far more INSERT statements a second than transactions. Inserting thousands of rows with one commit each pays that wait thousands of times; one transaction around an executemany() pays it once.

SQLite’s own documentation describes when it fits: data that one application owns, files that should be easy to copy, and moderate write loads. When many machines must write to the same data at once, use a client-server database such as PostgreSQL, which the next lesson connects to through psycopg.

Checkpoint

Check: One writer, one thread per connection

Question 1 of 2 Connection A has run an INSERT and not committed. Connection B, opened with timeout=0.2, runs an INSERT on the same file. What happens?
Choose one answer.

B waits until A commits, however long that takes B's row is written at once, beside A's A's transaction is rolled back to let B in B waits 0.2 seconds for the lock, then raises OperationalError, database is locked

Show the answer: Question 1 of 2

Answer: B waits 0.2 seconds for the lock, then raises OperationalError, database is locked

A's open transaction holds the write lock. B waits for it up to its timeout and then raises OperationalError: database is locked, as one_writer.py prints. After A commits, B's INSERT goes through.
Question 2 of 2 A worker thread uses a connection that the main thread created, with the default settings of connect(). What does sqlite3 do?
Choose one answer.

It waits until the main thread has finished It runs the statement, because SQLite is thread-safe It opens a new connection for the thread It raises ProgrammingError, because check_same_thread is True by default

Show the answer: Question 2 of 2

Answer: It raises ProgrammingError, because check_same_thread is True by default

check_same_thread=True is the default, so using a connection from another thread raises ProgrammingError, as the last line of one_writer.py shows. Open a separate connection in every thread.

SQLite Online (SQL Playground) Try the lesson's CREATE TABLE and SELECT statements in your browser before you put them in Python. SQLite Viewer & Editor Open the library.db file a program wrote and browse its tables and rows.

Practise: a library database with safe searches

The exercise asks for a BookStore class: add_book() returns the new id, search() matches part of a title in any case with % and _ taken literally, and checkout() lends a book once, in one transaction. Its sample tests run in your browser:

Exercise · Medium · Python

A small library database with safe searches and loans

Complete the class BookStore in book_store.py. Keep the names and parameters: the tests use them. BookStore(path) opens the SQLite database at path (":memory:" by default) and creates its two tables if they do not exist yet.

add_book(title, author) stores a book and returns its new id.
search(title_part) returns a list of (id, title) tuples for the books whose title contains title_part, ignoring upper and lower case, sorted by title. Characters such as ', ;, % and _ in title_part are matched as themselves: searching for 100% finds titles that contain 100%, not every title that contains 100.
checkout(book_id, member) lends a book. It returns True when the loan was recorded and False when the book is already on loan, since a book has at most one active loan. It raises ValueError for an id that does not exist. Check and record the loan inside one transaction.
close() closes the connection.

Pass every value through placeholders; never put a value into the SQL text.

Starter code · book_store.py

import sqlite3


class BookStore:
    """Books and loans in one SQLite database."""

    def __init__(self, path=":memory:"):
        self.con = sqlite3.connect(path)
        with self.con:
            self.con.execute("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL)")
            self.con.execute("CREATE TABLE IF NOT EXISTS loans (book_id INTEGER PRIMARY KEY REFERENCES books(id), member TEXT NOT NULL)")

    def add_book(self, title, author):
        """Store a book and return its id."""
        raise NotImplementedError("write add_book()")

    def search(self, title_part):
        """[(id, title), ...] of books whose title contains title_part, any case, sorted by title."""
        raise NotImplementedError("write search()")

    def checkout(self, book_id, member):
        """True if the loan was recorded, False if the book is already on loan; ValueError for an unknown id."""
        raise NotImplementedError("write checkout()")

    def close(self):
        self.con.close()

The sample tests · test_book_store.py

import tempfile
from pathlib import Path

from book_store import BookStore

TITLES = [
    ("The Guide", "R. K. Narayan"),
    ("guide to Sorting", "A. Author"),
    ("Rock 'n' Roll; a history", "B. Writer"),
    ("100% Python", "C. Coder"),
    ("1000 Puzzles", "D. Maker"),
]


def filled():
    store = BookStore()
    ids = [store.add_book(t, a) for t, a in TITLES]
    return store, ids


def test_add_returns_new_ids():
    """each new book gets a new id"""
    store, ids = filled()
    assert len(set(ids)) == len(TITLES)


def test_search_any_case_sorted():
    """titles containing the text, ignoring case, sorted by title"""
    store, ids = filled()
    titles = [title for _, title in store.search("GUIDE")]
    assert titles == ["The Guide", "guide to Sorting"]


def test_search_quotes_and_semicolons():
    """quotes and semicolons are just characters, and the tables survive"""
    store, ids = filled()
    assert store.search("Rock 'n' Roll;") == [(ids[2], "Rock 'n' Roll; a history")]
    assert len(store.search("")) == len(TITLES)


def test_search_wildcards_are_literal():
    """% and _ match only themselves"""
    store, ids = filled()
    assert store.search("100%") == [(ids[3], "100% Python")]
    assert store.search("_") == []


def test_checkout_once():
    """a book can be lent once; a second loan is refused"""
    store, ids = filled()
    assert store.checkout(ids[0], "asha") is True
    assert store.checkout(ids[0], "ravi") is False
    assert store.checkout(ids[1], "ravi") is True


def test_checkout_unknown_book():
    """an id that does not exist raises ValueError"""
    store, ids = filled()
    try:
        store.checkout(999, "asha")
    except ValueError:
        return
    assert False, "an unknown book was lent"


def test_data_is_saved():
    """books and loans are still there after closing and reopening the database"""
    path = Path(tempfile.mkdtemp()) / "library.db"
    store = BookStore(path)
    book = store.add_book("Malgudi Days", "R. K. Narayan")
    store.checkout(book, "meera")
    store.close()
    again = BookStore(path)
    assert again.search("malgudi") == [(book, "Malgudi Days")]
    assert again.checkout(book, "asha") is False
    again.close()

A hint

LIKE treats % and _ as wildcards, so put a backslash in front of each (and in front of any backslash) in title_part, then write title LIKE ? ESCAPE '\' and pass f"%{escaped}%" as the parameter. SQLite's LIKE already ignores the case of ASCII letters. For checkout(), a with self.con: block commits the loan or rolls it back, and cursor.lastrowid gives the id of the row an INSERT created.

The sample tests run on this device, in your browser (Pyodide): nothing is sent to mysmartcopilot.com. The first run downloads Python (about 13.5 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Python Online Compiler Change transaction.py so the second transfer fits the balance, and watch it commit.

The practice below returns to the library, the wallets and the two writers of shop.db. It opens with the part your checkpoint answers left weakest, and each pick comes with its reason:

Practice that adapts to you

Practise this lesson

16 questions about 5 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.

Question 1 of 16 After con.row_factory = sqlite3.Row, which of these work on a row from SELECT title, author FROM books?
Choose every answer that is right.

row[0] row["TITLE"] row.title row["title"]
Show the answer: Question 1 of 16
Answer:
- row[0]
- row["TITLE"]
- row["title"]
sqlite3.Row supports access by index and by column name, and the names are matched without regard to case. It has no attribute access; for that, write your own row factory or convert rows to dataclasses.
Question 2 of 16 A program inserts a row with con.execute() but has not called commit() yet. A second connection to the same file counts the rows. What does it see?
Choose one answer.

The count with the new row An error, because the table is being changed Zero rows, because the table is locked The count from before the insert

Show the answer: Question 2 of 16

Answer: The count from before the insert

An INSERT opens a transaction, and its changes become visible to other connections at commit(). Until then a second connection reads the last committed state, as B's count of 0 in one_writer.py shows.
Question 3 of 16 A script inserts 3 rows, prints a SELECT of them on the same connection, then crashes before commit(). What did the print show, and what does the file hold?
Choose one answer.

The print showed the 3 rows, and the file holds the first one The print showed the 3 rows, and the file holds them too The print showed the 3 rows, and the file holds none of them The print showed nothing, and the file holds none of them

Show the answer: Question 3 of 16

Answer: The print showed the 3 rows, and the file holds none of them

Inside its transaction a connection reads its own changes, so the SELECT finds the 3 rows. The crash ends the program before commit(), so the transaction is discarded and the file keeps the last committed state.
Question 4 of 16 SQLite has no date type. How does library.py store each loan's due date?
Choose one answer.

As a Python datetime pickled into a BLOB As ISO 8601 text, year first As a DATE column that SQLite checks As the number of seconds since 1970

Show the answer: Question 4 of 16

Answer: As ISO 8601 text, year first

Dates are text in library.py, written year first, so they sort and compare in date order, and julianday() converts them to day numbers when the query needs to subtract them.
Question 5 of 16 A loan was due on the 1st of a month, and the query runs on the 20th of the same month. What does julianday(today) - julianday(due) give?
Type a number.
Your answer days

A hint

julianday() gives each date as a day number; the difference is in days.

Show the answer: Question 5 of 16

Answer: 19 days

julianday() turns each date into a day number, so the difference of two dates is the number of days between them: from the 1st to the 20th of a month is 19 days, returned as 19.0.
Question 6 of 16 A column of prices must never hold text by mistake, such as a price typed as "12,50". What should the table be?
Choose one answer.

A table without column types, so SQLite stores anything A STRICT table with the column declared REAL An ordinary table, with every value checked by typeof() later An ordinary table with the column declared REAL

Show the answer: Question 6 of 16

Answer: A STRICT table with the column declared REAL

An ordinary column's type is a preference, so text that does not convert is kept as text. A STRICT table, since SQLite 3.37.0, converts what it can without loss and refuses the rest with IntegrityError.
Question 7 of 16 What does a placeholder such as ? do that formatting the value into the SQL does not?
Choose one answer.

It checks that the value is not a known attack It makes the query run faster, which is its only purpose It escapes the quotes in the value before it is formatted in The SQL and the value are sent apart, and SQLite compiles the statement before it binds the value as data

Show the answer: Question 7 of 16

Answer: The SQL and the value are sent apart, and SQLite compiles the statement before it binds the value as data

With a placeholder the statement is compiled with the ? in it, and the value is bound to it afterwards as data, so a quote or a semicolon in the value is just a character.
Question 8 of 16 On Python 3.14, what does con.execute("SELECT city FROM members WHERE name = :name", ("Asha",)) do?
Choose one answer.

It formats Asha into the SQL text It returns Asha's city, matching :name to the first item It looks for a member called :name It raises ProgrammingError, because named placeholders need a dictionary

Show the answer: Question 8 of 16

Answer: It raises ProgrammingError, because named placeholders need a dictionary

The named style takes a mapping, such as {"name": "Asha"}; the qmark style takes a sequence. Python 3.14 turned the old mix-up into ProgrammingError.
Question 9 of 16 A colleague suggests doubling every single quote in a name before formatting it into the SQL, instead of using placeholders. Why does the lesson reject that?
Choose one answer.

Escaping rules differ between databases and are easy to get wrong, while placeholders keep values out of the SQL altogether Placeholders are slower, so escaping would win It makes names with quotes impossible to store Doubling quotes is never needed in SQL

Show the answer: Question 9 of 16

Answer: Escaping rules differ between databases and are easy to get wrong, while placeholders keep values out of the SQL altogether

Hand-made escaping must cover every rule of every database and every input; one miss is a hole. Placeholders never put the value into the SQL text, so there is nothing to escape.

Question 10 of 16 What does transaction.py print?

What does this program print? Choose one answer.

import sqlite3
from contextlib import closing

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE wallets (owner TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
con.executemany("INSERT INTO wallets VALUES (?, ?)", [("asha", 500), ("ravi", 100)])
con.commit()


def transfer(con, payer, payee, amount):
    with con:  # commits when the block succeeds, rolls back when it raises
        con.execute("UPDATE wallets SET balance = balance + ? WHERE owner = ?", (amount, payee))
        con.execute("UPDATE wallets SET balance = balance - ? WHERE owner = ?", (amount, payer))


def balances(con):
    return dict(con.execute("SELECT owner, balance FROM wallets ORDER BY owner"))


transfer(con, "asha", "ravi", 200)
print("after 200:", balances(con))
try:
    transfer(con, "asha", "ravi", 400)  # Asha has only 300 left: the CHECK constraint fails
except sqlite3.IntegrityError as err:
    print("IntegrityError:", err)
print("after the failed 400:", balances(con))  # Ravi's credit was rolled back too
print("still open:", con.execute("SELECT count(*) FROM wallets").fetchone()[0], "wallets")  # with did not close it
con.close()

# closing() closes the connection at the end of its block; the inner with handles the transaction.
with closing(sqlite3.connect(":memory:")) as con2:
    with con2:
        con2.execute("CREATE TABLE t (x)")
        con2.execute("INSERT INTO t VALUES (1)")
    print("rows in t:", con2.execute("SELECT count(*) FROM t").fetchone()[0])
try:
    con2.execute("SELECT 1")
except sqlite3.ProgrammingError as err:
    print("ProgrammingError:", err)

after 200: {'asha': 300, 'ravi': 300} IntegrityError: CHECK constraint failed: balance >= 0 after the failed 400: {'asha': 300, 'ravi': 700} still open: 2 wallets rows in t: 1 ProgrammingError: Cannot operate on a closed database. after 200: {'asha': 300, 'ravi': 300} IntegrityError: CHECK constraint failed: balance >= 0 after the failed 400: {'asha': 300, 'ravi': 300} ProgrammingError: Cannot operate on a closed database. after 200: {'asha': 300, 'ravi': 300} IntegrityError: CHECK constraint failed: balance >= 0 after the failed 400: {'asha': 300, 'ravi': 300} still open: 2 wallets rows in t: 1 ProgrammingError: Cannot operate on a closed database.

Show the answer

Answer: it prints

after 200: {'asha': 300, 'ravi': 300}
IntegrityError: CHECK constraint failed: balance >= 0
after the failed 400: {'asha': 300, 'ravi': 300}
still open: 2 wallets
rows in t: 1
ProgrammingError: Cannot operate on a closed database.

The second transfer credits Ravi first, then fails the CHECK constraint on Asha's balance. with con: rolls the whole transaction back, so Ravi's credit disappears too. The connection stays open after the with block, and only closing() closes con2, so using it afterwards raises ProgrammingError.

Question 11 of 16 What does with con: do for an sqlite3 connection con?
Choose one answer.

It commits the open transaction when the block succeeds and rolls it back when the block raises; the connection stays open It switches the connection to autocommit mode for the block It locks the database file for other programs until the program ends It opens the connection and closes it at the end of the block

Show the answer: Question 11 of 16

Answer: It commits the open transaction when the block succeeds and rolls it back when the block raises; the connection stays open

The sqlite3 documentation says the connection's context manager commits or rolls back, and neither opens a new transaction nor closes the connection. Close it with close() or contextlib.closing().
Question 12 of 16 You want one block that commits on success, rolls back on an error and always closes the connection at the end. Which code does that?
Choose one answer.

con = sqlite3.connect(path), the statements, then con.close() with sqlite3.connect(path) as con: around the statements with closing(sqlite3.connect(path)) as con: alone with closing(sqlite3.connect(path)) as con: and inside it with con: around the statements

Show the answer: Question 12 of 16

Answer: with closing(sqlite3.connect(path)) as con: and inside it with con: around the statements

Two context managers, two jobs: contextlib.closing() closes the connection, and the connection's own with block commits or rolls back the transaction inside it.
Question 13 of 16 With the default settings of sqlite3.connect(), how many seconds does a connection wait for another writer's lock before it raises OperationalError?
Type a number.
Your answer seconds

Show the answer: Question 13 of 16

Answer: 5 seconds

The timeout argument of connect() defaults to five seconds. one_writer.py set it to 0.2 so that the run is quick; after that time the waiting connection raises OperationalError, database is locked.
Question 14 of 16 In one_writer.py, why does connection B call rollback() after its INSERT failed with "database is locked"?
Choose one answer.

To undo the row B inserted The failed INSERT had already begun B's transaction, and a read inside it would keep a lock that A's commit must wait for It is not needed; it only makes the output tidier Because rollback() frees A's write lock

Show the answer: Question 14 of 16

Answer: The failed INSERT had already begun B's transaction, and a read inside it would keep a lock that A's commit must wait for

In sqlite3's default mode, a BEGIN runs before the INSERT, so B is inside a transaction even though the INSERT failed. Rolling it back leaves B holding nothing, so A can commit.
Question 15 of 16 A web app runs on one machine and writes to one SQLite file. Under load, some requests fail with "database is locked". Which change attacks the cause?
Choose one answer.

Set the timeout to 0, so requests never wait Keep each write transaction short, so the lock is held for as little time as possible Share one connection between all the request threads Open the database read-only

Show the answer: Question 15 of 16

Answer: Keep each write transaction short, so the lock is held for as little time as possible

SQLite allows one writer at a time. The shorter each write transaction, the shorter every wait, and the less often a waiting connection reaches its timeout and raises database is locked.
Question 16 of 16 A script inserts 10,000 rows into an SQLite file and calls commit() after every row. Which change makes it fastest while still saving every row?
Choose one answer.

Run all the inserts in one transaction: executemany() inside with con: Open a new connection for every row Read each row back with a SELECT after its commit Set a longer timeout in connect()

Show the answer: Question 16 of 16

Answer: Run all the inserts in one transaction: executemany() inside with con:

By default SQLite waits until each transaction is safely stored on the disk, so it runs far more INSERT statements a second than transactions, as its FAQ explains. One transaction around executemany() pays that wait once for all the rows, and with con: still commits them together or not at all.

Interview questions

Why should you pass values to sqlite3 as parameters instead of building the SQL string with an f-string?

Because a formatted value becomes part of the SQL text, and a parameter never does. With an f-string, a quote inside the value ends a string literal early, which at best causes a syntax error and at worst lets crafted input change what the query does, the flaw known as SQL injection. With a placeholder, ? or :name, the driver sends the statement and the values separately: SQLite compiles the statement first and binds the values to it as data, so no character in a value can alter the SQL. Parameters also handle types correctly, such as bytes and None, and let the database reuse a compiled statement. Placeholders work only for values, so table and column names that depend on input must be checked against an allowlist instead.

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Fresher to mid

An import script reads 200,000 rows from a CSV file and inserts them into an SQLite file with one execute() and one commit() per row. It takes over ten minutes. Why is it slow, how do you make it fast, and how do you keep the import all-or-nothing?

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Mid to senior

A small web app shares one global sqlite3 connection between its request threads. First it fails with a ProgrammingError about objects created in another thread; after someone passes check_same_thread=False, users see "database is locked" under load. Explain both errors and design the data access.

Model answer, Pro. First step and follow-ups

Key takeaways

sqlite3.connect() opens a database file or ":memory:"; execute(), executemany(), fetchone() and fetchall() do the work, commit() saves, and sqlite3.Row, rowcount and lastrowid report what came back.
SQLite has no date type, so keep dates as ISO 8601 text, which sorts in date order, and remember that a column’s declared type is only a preference unless the table is STRICT.
Pass every value through ? or :name placeholders and never format values into SQL; names of tables and columns need an allowlist instead.
with con: commits on success and rolls back on an exception, but does not close the connection: call close() or use contextlib.closing().
SQLite lets one connection write at a time, so a second writer waits up to its timeout and then gets “database is locked”; keep write transactions short, batch many writes into one, and give each thread its own connection.
SQLite fits data one application owns; many concurrent writers on several machines call for a database server.

Terms in this lesson

placeholder (SQL): A marker such as ? or :name where a value goes in an SQL statement. The driver sends the values apart from the SQL text, and the database binds them to the compiled statement as data.
SQL injection: A flaw where text from outside is put into the text of an SQL statement, so that crafted input changes what the statement does, for example to match every row. Placeholders prevent it, because values travel apart from the SQL.
transaction: A group of SQL statements that a database applies together or not at all: commit() makes every change permanent, and rollback() or an error undoes all of them. Other connections see the changes only after the commit.
type affinity: SQLite's preference for a type in a column declared INTEGER, REAL, TEXT or NUMERIC: a value that converts without loss is stored as that type, and anything else is kept as it is. A STRICT table refuses values it cannot convert instead.

References

sqlite3: DB-API 2.0 interface for SQLite databases (Python Software Foundation)
sqlite3: how to use placeholders to bind values in SQL queries (Python Software Foundation)
sqlite3: how to use the connection context manager (Python Software Foundation)
What's New In Python 3.14: sqlite3 (Python Software Foundation)
SQL As Understood By SQLite (SQLite)
Datatypes In SQLite (SQLite)
STRICT Tables (SQLite)
File Locking And Concurrency In SQLite Version 3 (SQLite)
SQLite Frequently Asked Questions: INSERT is really slow (SQLite)
Appropriate Uses For SQLite (SQLite)
Pyodide: WebAssembly constraints (threading) (Pyodide project)
SQL Injection Prevention Cheat Sheet (OWASP Foundation)

Report a problem with this lesson

In this module: Files, paths and data formats

Reading and writing text files safely Free lesson, Pro answers: 25 minutes
pathlib: paths, folders and file operations Free lesson, Pro answers: 20 minutes
CSV files with the csv module Free lesson, Pro answers: 20 minutes
JSON: load, dump and custom types Free lesson, Pro answers: 20 minutes
Config files, environment variables and secrets Free lesson, Pro answers: 25 minutes
Binary files, struct and checksums Free lesson, Pro answers: 25 minutes
Archives and compression: zip, tar, gzip, zstd Free lesson, Pro answers: 20 minutes
XML and HTML parsing without security holes Free lesson, Pro answers: 20 minutes
pickle, shelve and safe serialisation Free lesson, Pro answers: 25 minutes
SQLite databases with sqlite3 Free lesson, Pro answers: 20 minutes
Beyond SQLite: DB-API drivers and ORMs Free lesson, Pro answers: 20 minutes

Your country

SQLite databases with sqlite3

What you will learn

Before you start

What you will learn

Before you start

A connection runs SQL, and commit() keeps the result

SQLite has no date type, and a declared type is only a preference

A value formatted into SQL can rewrite the query

with con: commits a transaction, or rolls all of it back

Two connections cannot write at once, so keep transactions short

Practise: a library database with safe searches

A small library database with safe searches and loans

Results of the sample tests

Interview questions

An import script reads 200,000 rows from a CSV file and inserts them into an SQLite file with one `execute()` and one `commit()` per row. It takes over ten minutes. Why is it slow, how do you make it fast, and how do you keep the import all-or-nothing?

A small web app shares one global sqlite3 connection between its request threads. First it fails with a ProgrammingError about objects created in another thread; after someone passes `check_same_thread=False`, users see "database is locked" under load. Explain both errors and design the data access.

Key takeaways

References

Related tools

What you will learn

Before you start

What you will learn

Before you start

A connection runs SQL, and commit() keeps the result

SQLite has no date type, and a declared type is only a preference

A value formatted into SQL can rewrite the query

with con: commits a transaction, or rolls all of it back

Two connections cannot write at once, so keep transactions short

Practise: a library database with safe searches

Results of the sample tests

Interview questions

An import script reads 200,000 rows from a CSV file and inserts them into an SQLite file with one execute() and one commit() per row. It takes over ten minutes. Why is it slow, how do you make it fast, and how do you keep the import all-or-nothing?

A small web app shares one global sqlite3 connection between its request threads. First it fails with a ProgrammingError about objects created in another thread; after someone passes check_same_thread=False, users see "database is locked" under load. Explain both errors and design the data access.

Key takeaways

References

Related tools

An import script reads 200,000 rows from a CSV file and inserts them into an SQLite file with one `execute()` and one `commit()` per row. It takes over ten minutes. Why is it slow, how do you make it fast, and how do you keep the import all-or-nothing?