SQLite with Python: Tables, Inserts, and Queries

Create it, fill it, ask it questions

Advanced 13 min

In this lesson

Time to actually use one. This lesson builds a table, puts rows in it, and queries them back, all in real code you can run on this page. By the end you will have done the full cycle, and you will know what happens if you forget to commit().

Explain it like I’m 5

You are writing notes into a filing cabinet and then asking it questions. commit() is the moment you actually file the note instead of leaving it on the desk.

Connect, then get a cursor

sqlite3.connect() opens a database file, creating it if it does not exist. Pass the special name ":memory:" and you get a database that lives only in RAM, which is perfect for experiments and for the examples on this page.

From the connection you get a cursor: the thing that runs statements and holds the results. Run SQL with cursor.execute().

Example 
import sqlite3

conn = sqlite3.connect(":memory:")   # or "library.db" for a real file
cur = conn.cursor()

cur.execute("""
    CREATE TABLE books (
        id    INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        year  INTEGER
    )
""")
print("table created")
Output 
table created
One connection, one cursor, one CREATE TABLE.

Inserting rows, with placeholders

Values go into a statement as ? placeholders, a parameterized query, with the actual values passed separately as a tuple. Never format them into the SQL string, the next lesson shows exactly what goes wrong when you do.

executemany() runs the same statement for a whole list of value tuples, which is how you insert in bulk without a loop.

Example 
cur.execute("INSERT INTO books (title, year) VALUES (?, ?)", ("Dune", 1965))

cur.executemany("INSERT INTO books (title, year) VALUES (?, ?)", [
    ("Neuromancer", 1984),
    ("Snow Crash", 1992),
])

conn.commit()
print("rows in table:", cur.execute("SELECT COUNT(*) FROM books").fetchone()[0])
One row, then two more, then commit.

Querying: WHERE, ORDER BY, and getting results out

SELECT names the columns you want, FROM names the table, WHERE filters, and ORDER BY sorts. Results come back as tuples, in the column order you asked for.

Take them with fetchone() for a single row, fetchall() for a list of all of them, or by looping over the cursor directly, which is best for large results because it does not build the whole list in memory.

Example 
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)")
cur.executemany("INSERT INTO books (title, year) VALUES (?, ?)",
                [("Dune", 1965), ("Neuromancer", 1984), ("Snow Crash", 1992)])
conn.commit()

cur.execute(
    "SELECT title, year FROM books WHERE year > ? ORDER BY year",
    (1980,),
)
for row in cur.fetchall():
    print(row)
Output 
('Neuromancer', 1984)
('Snow Crash', 1992)
A filtered, sorted query. Note the trailing comma in (1980,).

The table and four rows are set up for you. Write the query: select title and year for books published after 1980, sorted by year, and print each title and year on one line.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)")
cur.executemany("INSERT INTO books (title, year) VALUES (?, ?)", [
    ("Dune", 1965), ("Neuromancer", 1984),
    ("Snow Crash", 1992), ("Ancillary Justice", 2013),
])
conn.commit()

# TODO: select title and year for books after 1980, ordered by year
cur.execute("SELECT title, year FROM books")

for row in cur.fetchall():
    print(row[0], row[1])

Closing up

Connections should be closed when you are finished. The tidiest way is the with block you already use for files: with sqlite3.connect(...) as conn: commits automatically when the block ends without an error, and rolls back if one is raised.

One wrinkle worth knowing: for SQLite that with block manages the transaction, not the connection itself, so you still call conn.close() at the end. Unit 10 comes back to how blocks like this work and how to write your own.

Example 
import sqlite3

conn = sqlite3.connect("library.db")
try:
    with conn:                    # commits on success, rolls back on error
        conn.execute("INSERT INTO books (title, year) VALUES (?, ?)", ("Dune", 1965))
finally:
    conn.close()                  # always release the file
Commit on success, roll back on failure, close either way.

Common mistake: Forgetting commit() and losing every insert

Why it happens:

The inserts run without error, so nothing suggests anything is missing until the next run finds an empty table.

How to fix it:

Call conn.commit() after your changes, or wrap them in with conn: which commits for you.

Common mistake: Passing a single value without making it a tuple

Why it happens:

(1980) looks like a tuple but Python reads it as a plain number in brackets.

How to fix it:

Add the trailing comma: (1980,). The parameters argument must be a sequence, and one-item tuples need that comma.

Common mistake: Expecting rows to come back as dictionaries

Why it happens:

JSON from Unit 7 arrived as dicts, so rows feel like they should too.

How to fix it:

By default a row is a tuple, indexed by position. Set conn.row_factory = sqlite3.Row to access columns by name instead, covered in the next lesson.

Common mistake: Using a cursor after closing the connection

Why it happens:

The cursor variable still exists, so it looks usable.

How to fix it:

A cursor is only valid while its connection is open. Fetch what you need before closing, or keep the connection open for as long as you are querying.

What happens to your inserts if the program ends before commit()?

Why write VALUES (?, ?) instead of formatting values into the SQL string?

What does cur.fetchone() return?

Mini exercise (medium)

Build it end to end. Create a tasks table with title and done, insert four tasks (two done, two not), commit, then print the titles of the unfinished ones in alphabetical order.

Now you. Edit the starter code below, then Run it, everything happens in the browser.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE tasks (title TEXT, done INTEGER)")
# TODO: insert four tasks (two done, two not), then commit

# TODO: print the titles of the unfinished tasks, alphabetically

What to learn next

You did the full cycle: connect(), a cursor, CREATE TABLE, INSERT with ? placeholders, SELECT with WHERE and ORDER BY, and fetchone() versus fetchall(). You also know what happens when you forget commit(), which is that nothing happens at all.

Two habits separate this from SQL that survives real data. Safe Queries and When to Reach for an ORM shows exactly what breaks without placeholders, and how to stop depending on column order.