🎓 A1Learner
🔍

Python Querying SQLite

A query is a question a program asks a database in SQL. A query can return the rows themselves.

Select rows in a set order

ORDER BY sorts the result. Ask for it whenever order matters.

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE books (title TEXT, year INTEGER)")
cursor.executemany("INSERT INTO books VALUES (?, ?)", [("Dune", 1965), ("Emma", 1815), ("Solaris", 1961)])
for row in cursor.execute("SELECT title, year FROM books ORDER BY year"):
    print(row)

Filter with WHERE

WHERE keeps only rows that match. fetchall() returns every remaining row as a list.

cursor.execute("SELECT title FROM books WHERE year > ? ORDER BY title", (1900,))
for row in cursor.fetchall():
    print(row[0])

Take one row, then a few

fetchone() returns one row. fetchmany(n) returns the next n rows.

cursor.execute("SELECT title FROM books ORDER BY year")
print(cursor.fetchone())
print(cursor.fetchmany(2))

Read columns by name

sqlite3.Row lets you use column names instead of numbers. Set row_factory on the connection first.

connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute("SELECT title, year FROM books ORDER BY year")
row = cursor.fetchone()
print(row["title"], row["year"])

Update and delete rows

rowcount tells you how many rows a change touched.

cursor.execute("UPDATE books SET year = ? WHERE title = ?", (1816, "Emma"))
print("updated:", cursor.rowcount)
cursor.execute("DELETE FROM books WHERE title = ?", ("Solaris",))
print("deleted:", cursor.rowcount)

Join two tables

A join matches rows from two tables. Here it matches a loan to its book by title.

cursor.execute("CREATE TABLE loans (title TEXT, borrower TEXT)")
cursor.execute("INSERT INTO loans VALUES (?, ?)", ("Dune", "Ana"))
cursor.execute("SELECT loans.borrower, books.title FROM loans JOIN books ON loans.title = books.title")
for row in cursor.fetchall():
    print(row["borrower"], "has", row["title"])

Example

import sqlite3

with sqlite3.connect(":memory:") as connection:
    connection.row_factory = sqlite3.Row
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE books (id INTEGER, title TEXT)")
    cursor.execute("CREATE TABLE loans (book_id INTEGER, borrower TEXT)")
    cursor.executemany("INSERT INTO books VALUES (?, ?)", [(1, "Dune"), (2, "Emma"), (3, "Solaris")])
    cursor.executemany("INSERT INTO loans VALUES (?, ?)", [(3, "Ben"), (1, "Ana"), (1, "Cleo")])
    cursor.execute("SELECT borrower, title FROM loans JOIN books ON loans.book_id = books.id ORDER BY borrower")
    for row in cursor.fetchall():
        print(row["borrower"], "has", row["title"])

Key points

  • Use SELECT to choose which columns to read
  • Use WHERE to keep only matching rows
  • Use ORDER BY to sort the result rows
  • Use fetchone(), fetchmany(n), and fetchall() to collect results
  • Use row_factory with sqlite3.Row to read values by column name
  • Use JOIN to combine matching rows from two tables
  • A query is a question written in SQL for a database.

In this example, the library keeps books in one table. Loans live in a second table. Each loan stores the id of its book. The join matches each loan to the book with that id. row_factory lets the loop read borrower and title by name. ORDER BY borrower sorts the report by person. The output lists who has which book.

Note

Without ORDER BY, SQLite does not promise any row order.

The same query can return rows in a different order later.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Python Playground

Exercise

You print query results and the order must never change. Which part of the query guarantees the order?