Skip to content
Tutorials Center
My learning

Topics

Lesson 102 of 149 ยท Data and the Web

Databases with sqlite3

Store and query data with Python's built-in sqlite3 module. Create tables, insert and select rows, use safe parameters, handle transactions and read rows by name.

Text files, CSV and JSON are fine for small amounts of data. But once you need to search through thousands of records, update a few of them safely, or combine related data, you want a database. The good news: Python ships with one. The sqlite3 module gives you SQLite, a complete SQL database engine that stores everything in a single file, with no server to install or configure.

SQLite is everywhere: in phones, browsers, desktop apps and countless small websites. In this lesson you'll create tables, insert and query rows, pass values safely with parameters (and see the attack that unsafe code invites), group changes into transactions, and read rows by column name. Every example runs live in your browser.

A little SQL ahead. The examples use basic SQL commands: CREATE TABLE, INSERT, SELECT, UPDATE. Each is explained as it appears. This lesson focuses on using a database from Python; SQL itself is a language worth learning in depth on its own.

Connecting and creating a table

Everything starts with sqlite3.connect(). Give it a file name, and SQLite opens that database file (creating it if needed). Give it the special name ":memory:", and you get a temporary database that lives in memory and disappears when the connection closes, which is perfect for experiments and tests.

A database stores data in tables. Each table has named columns with types, and each record is a row:

Python Your first database

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

What happened, step by step:

  1. CREATE TABLE defined five columns. INTEGER PRIMARY KEY makes id a unique row number that SQLite fills in automatically. NOT NULL means the column must have a value.
  2. INSERT INTO added one row. The ? marks are placeholders; the actual values come from the tuple that follows (more on why in a moment).
  3. commit() made the change permanent. Without it, changes to a file database are thrown away when the connection closes.
  4. SELECT returned a cursor, which you can loop over. Each row arrives as a tuple, and SQLite's types map to Python types: INTEGER to int, REAL to float, TEXT to str, and NULL to None.

connection.execute() is a shortcut that creates a cursor for you. You may also see code that calls cursor = connection.cursor() first and then cursor.execute(...); both work the same way.

Inserting and querying with parameters

Real programs insert many rows and filter them by values that come from users or files. Two tools make this easy:

  • executemany(sql, rows) runs one statement for every item in a list.
  • Placeholders pass values into the SQL: ? takes values by position from a tuple, and :name takes them from a dictionary.
Python executemany, placeholders and fetch methods

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The three ways to read results:

Method Returns Use it when
fetchone() the next row, or None if there are no more you expect one row (look-ups by id, COUNT(*))
fetchall() a list of all remaining rows the result is small
for row in cursor: rows one at a time the result could be large

After an INSERT, cursor.lastrowid holds the new row's id. After an UPDATE or DELETE, cursor.rowcount tells you how many rows changed.

A classic slip: the parameters must be a sequence, so a single value needs a one-item tuple (author,). Writing (author) passes a plain string, and sqlite3 tries to use each character as a separate value, failing with an "Incorrect number of bindings" error.

Never build SQL with string formatting

It's tempting to drop values into SQL with an f-string: f"SELECT * FROM users WHERE name = '{name}'". Don't. If the value contains a quote, the query breaks. Worse, a crafted value can change what the query does. This attack is called SQL injection, and it's one of the most common security holes on the web.

See it happen on a harmless in-memory table:

Python SQL injection, and how parameters stop it

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The crafted input closed the quote early and added OR '1'='1', a condition that is always true, so the "search" returned every user, admins included. With a placeholder, the database receives the value separately from the SQL text. It is always treated as data, never as code, so the attack simply searches for a user with a very strange name. And a real name like O'Brien works without any escaping.

Placeholders are for values only. You can't use ? for table or column names. If those must vary, choose them from a fixed list of allowed names in your code, never directly from user input.

Rows you can read by name

Tuples are fine for two columns, but row[3] is hard to read. Set connection.row_factory = sqlite3.Row, and every row works both like a tuple and like a read-only dictionary:

Python sqlite3.Row, joins and aggregates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A few new tools appeared here:

  • executescript() runs several SQL statements separated by semicolons. Use it for fixed setup scripts only, since it doesn't accept parameters.
  • The two tables are linked: each book's author_id refers to an author's id. Splitting data this way avoids repeating an author's details in every book row.
  • JOIN combines rows from both tables, and GROUP BY with COUNT() and AVG() lets the database do the arithmetic. Databases are very fast at this, so let them summarize data instead of loading everything into Python.
  • AS book_count names a computed column, so you can read it as row["book_count"].

Type affinity: SQLite's flexible typing

Most databases reject a value that doesn't fit the column's declared type. SQLite is more relaxed. A column has a type affinity, a preferred storage type, and SQLite tries to convert incoming values to it, but it will store a value that can't be converted rather than refuse it. Each individual value has its own storage class: NULL, INTEGER, REAL, TEXT or BLOB. The SQL function typeof() tells you which one a stored value has:

Python typeof() and column affinity

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the three rows against the column declarations:

  • n INTEGER has integer affinity. The text '42' looks like a number, so SQLite stores the integer 42. The text 'forty' can't be converted, so it is stored as text anyway. The float 4.0 has no fractional part, so it becomes the integer 4.
  • r REAL converts '3.5' and 2 to floats, but keeps 'abc' as text.
  • t TEXT converts the number 99 to the text '99'.
  • A column with no declared type (anything) has no affinity and keeps every value exactly as it came, which is why it holds '7' as text and 7 as an integer side by side.

The practical lesson: the declared type is a strong hint, not a guarantee. A typo in your code can slip a string into a numeric column, and sorting or comparing that column then behaves strangely, because SQLite orders NULL first, then numbers, then text, then blobs.

Two tools give you strictness back. A CHECK constraint can test the type of each value, and a table declared STRICT (SQLite 3.37 and newer) refuses values that can't be converted:

Python STRICT tables and CHECK(typeof(...))

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the loose table, 'twelve' is quietly accepted. The STRICT table still converts numeric text such as '12' to an integer, but rejects text that isn't a number. The CHECK version works the same way here, because the column's affinity converts '12' before the check runs, and then typeof() sees an integer. Its rejection message names the rule that failed, which is easier to read than a generic type error. (The table name is formatted into the SQL only because it comes from a fixed tuple in the code, never from a user.)

Your Python values follow the same map. int is stored as INTEGER, float as REAL, str as TEXT, bytes as BLOB and None as NULL. Python ints larger than 64 bits raise OverflowError. bool is stored as 0 or 1 and comes back as an int.

Adapters and converters for your own types

SQLite stores only the five basic types, so a datetime.date or a dictionary needs help. An adapter turns a Python object into a storable value on the way in. A converter turns stored bytes back into an object on the way out. Converters run for columns whose declared type matches the converter's name, when you connect with detect_types=sqlite3.PARSE_DECLTYPES:

Python Adapters and converters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second query shows the truth: inside the database the date is plain text, and the converter rebuilds the Python object each time you read it. Explicit adapters like this are the safe choice; the built-in default date adapters are deprecated since Python 3.12.

LEFT JOIN: keeping rows that have no match

The earlier join example used JOIN, which is short for INNER JOIN: a row appears only when both tables have a match. An author with no books vanished from the result. To keep every row of the left table, use LEFT JOIN. Columns from the right table are then NULL (None in Python) where nothing matched, and COUNT(books.id) counts only real matches, so it correctly reports 0:

Python LEFT JOIN, COALESCE and a custom row factory

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Lena Holm appears with a count of 0 because of the LEFT JOIN. COALESCE(x, fallback) returns the first value that isn't NULL, which is handy for filling in blanks. The last query uses a classic pattern: left join and then keep the rows where the right side is NULL, which finds records that point at nothing (an anti-join).

The dict_factory function shows what a row_factory really is: any function that takes the cursor and the raw tuple and returns whatever you want each row to be. cursor.description supplies the column names. A factory like this gives you ordinary dictionaries, which convert straight to JSON, at the price of the sqlite3.Row conveniences such as access by position.

Transactions: all or nothing

Some changes belong together. Moving money between accounts means subtracting from one and adding to the other. If the program fails between those two steps, you must not end up with half a transfer. A transaction groups statements so that they either all take effect (commit) or none do (roll back).

Using the connection as a context manager handles this for you: with connection: commits when the block finishes normally and rolls back if an exception escapes it:

Python A transfer that rolls back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the failed transfer, the first UPDATE had already added 500 to Alice's balance. Then the second one broke the CHECK (balance >= 0) rule, SQLite raised IntegrityError, and the with block rolled back both statements. The balances are exactly as they were. Constraints like CHECK, NOT NULL, UNIQUE and PRIMARY KEY are your database's last line of defense against bad data.

with connection: does not close the connection. It only manages the transaction. Call connection.close() when you're done, or wrap the connection in contextlib.closing(...) to close it automatically.

By default, sqlite3 opens a transaction automatically before INSERT, UPDATE and DELETE, and keeps it open until you call commit() or rollback(). Python 3.12 added an autocommit option to connect() for code that wants explicit control; the default behavior is what this lesson uses.

Working with database files

On your own computer, use a file name instead of ":memory:":

Python
import sqlite3
from contextlib import closing

with closing(sqlite3.connect("library.db")) as connection:
    connection.row_factory = sqlite3.Row
    connection.execute("PRAGMA foreign_keys = ON")    # enforce REFERENCES links
    with connection:
        connection.execute("""CREATE TABLE IF NOT EXISTS notes (
                                  id INTEGER PRIMARY KEY,
                                  text TEXT NOT NULL,
                                  created TEXT DEFAULT CURRENT_TIMESTAMP)""")
        connection.execute("INSERT INTO notes (text) VALUES (?)", ("Learn sqlite3",))
    for note in connection.execute("SELECT * FROM notes"):
        print(note["id"], note["text"], note["created"])

A few practical notes:

  • CREATE TABLE IF NOT EXISTS lets the same script run again without errors.
  • SQLite doesn't enforce foreign keys unless you turn them on with PRAGMA foreign_keys = ON for each connection.
  • SQLite has no separate date type. Store dates as ISO 8601 text (date.isoformat()) and convert back with date.fromisoformat() (see Dates and Times). The automatic date converters in sqlite3 are deprecated since Python 3.12, so do the conversion yourself.
  • The command-line tool sqlite3 library.db (or python -m sqlite3 library.db since Python 3.12) opens a database so you can poke at it with SQL directly.

Beyond SQLite

SQLite is perfect for desktop apps, tools, tests, prototypes and many websites. When lots of users write at the same time, or data lives on a separate server, teams usually move to PostgreSQL or MySQL. Their Python drivers (such as psycopg and mysqlclient) follow the same standard database interface you just learned: connect(), execute(), parameters, fetchall(), commit(). The main visible difference is the placeholder style, often %s instead of ?.

Many projects also use an ORM (object-relational mapper) such as SQLAlchemy or Django's ORM. You define tables as Python classes and query with Python code, and the ORM writes parameterized SQL for you. You'll meet Django's ORM in Web Development with Flask and Django. Knowing plain SQL and sqlite3 makes ORMs far easier to understand and debug.

Check your understanding

Quick check ยท 8 questions

of 8 answered You scored / 8 ยท perfect!

  1. 1. What does sqlite3.connect(":memory:") give you?

    The special name ":memory:" creates a fresh in-memory database, ideal for experiments and tests.
  2. 2. Which line safely finds a user by a name typed into a form?

    Only the placeholder version sends the value separately from the SQL, so it can't change the query. The other three allow SQL injection.
  3. 3. You insert rows into a database file but they are gone the next time you open it. What is the most likely cause?

    sqlite3 opens a transaction before INSERT, UPDATE and DELETE. Call commit() or use with connection: to save the changes.
  4. 4. What happens when an exception is raised inside with connection:?

    The connection's context manager commits on success and rolls back on an exception, then lets the exception continue. It does not close the connection.
  5. 5. After conn.row_factory = sqlite3.Row, how can you read a column called price?

    sqlite3.Row supports access by column name with square brackets and by position, and dict(row) converts it into a regular dictionary.
  6. 6. A column is declared qty INTEGER in an ordinary (non-STRICT) table. What happens when you insert the text 'twelve'?

    Type affinity makes SQLite convert values when it can ('12' becomes 12), but a value that can't be converted is stored as it is. Declare the table STRICT or add a CHECK to refuse it.
  7. 7. An author has no books. Which join still returns that author, with a book count of 0?

    A LEFT JOIN keeps every row of the left table and fills the right side with NULL when nothing matches. COUNT(books.id) ignores NULLs, so it reports 0.
  8. 8. What is a row_factory?

    Setting connection.row_factory changes what each fetched row is, for example sqlite3.Row or a function that builds dictionaries from cursor.description.

Summary

  • sqlite3.connect() opens a database file, or a temporary one with ":memory:"; no server or installation is needed.

  • execute() runs SQL; executemany() repeats it for many rows; executescript() runs fixed multi-statement scripts.

  • Always pass values with ? or :name placeholders; building SQL with f-strings invites SQL injection.

  • Read results with fetchone(), fetchall() or a for loop; lastrowid and rowcount report what changed.

  • connection.row_factory = sqlite3.Row lets you read columns by name.

  • SQLite columns have a type affinity, a preferred type; values that can't be converted are stored as they are. Check with typeof(), and use STRICT tables or CHECK constraints for firm typing.

  • LEFT JOIN keeps rows without a match (right side NULL); COALESCE fills in blanks; a custom row_factory function can return dicts or any object.

  • commit() saves changes; with connection: commits or rolls back a whole transaction, but doesn't close the connection.

Next: MySQL and PostgreSQL from Python.