Python SQLite Databases

SQLite is a small database that lives in one file. Python ships SQLite as the sqlite3 module.

Connect and make a table

connect() opens the database. A cursor is the object that runs your SQL commands.

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
cursor.execute("CREATE TABLE contacts (name TEXT, phone TEXT)")
print("ready")

Add a row safely

Each ? is a placeholder. A placeholder is an empty slot for one value. You pass the values separately, as a tuple.

cursor.execute(
    "INSERT INTO contacts VALUES (?, ?)",
    ("Ana", "555-0101"),
)
connection.commit()
print(cursor.rowcount)

Add many rows at once

executemany() runs the same command for each tuple in a list.

rows = [("Ben", "555-0102"), ("Cleo", "555-0103")]
cursor.executemany("INSERT INTO contacts VALUES (?, ?)", rows)
connection.commit()
print(cursor.rowcount)

Open with a with block

A with block closes the connection for you. It also saves changes when the block ends cleanly.

with sqlite3.connect(":memory:") as connection:
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE notes (text TEXT)")
    print("done")

Example

import sqlite3

with sqlite3.connect(":memory:") as connection:
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE contacts (name TEXT, city TEXT)")
    contacts = [
        ("Ana", "Oslo"),
        ("Ben", "Lima"),
        ("Cleo", "Oslo"),
        ("Dan", "Cairo"),
    ]
    cursor.executemany("INSERT INTO contacts VALUES (?, ?)", contacts)
    cursor.execute("SELECT COUNT(*) FROM contacts")
    print("Contacts:", cursor.fetchone()[0])
    cursor.execute("SELECT COUNT(*) FROM contacts WHERE city = ?", ("Oslo",))
    print("In Oslo:", cursor.fetchone()[0])

Key points

Database: data stored in tables of rows and columns
SQL: the language you use to ask a database for data
Use sqlite3.connect() to open a database file, or ":memory:" for a temporary one
Use cursor.execute() to run one SQL command
Use COUNT(*) to count the rows a query matches
Use commit() to save changes to a file database
SQLite keeps a whole database in one small file.

In this example, the database lives only in memory. The with block opens the connection. CREATE TABLE makes a contacts table with two text columns. executemany() inserts all four contacts at once. COUNT(*) counts rows, so the first query prints 4. The second query adds a WHERE filter for Oslo. It prints 2, for Ana and Cleo.

Note

Never build SQL by formatting values into the command text.

Always pass values with ? placeholders.

Text from a user could otherwise change the command itself.

This attack is called SQL injection.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Python Playground

import sqlite3

with sqlite3.connect(":memory:") as connection:
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE contacts (name TEXT, city TEXT)")
    contacts = [
        ("Ana", "Oslo"),
        ("Ben", "Lima"),
        ("Cleo", "Oslo"),
        ("Dan", "Cairo"),
    ]
    cursor.executemany("INSERT INTO contacts VALUES (?, ?)", contacts)
    cursor.execute("SELECT COUNT(*) FROM contacts")
    print("Contacts:", cursor.fetchone()[0])
    cursor.execute("SELECT COUNT(*) FROM contacts WHERE city = ?", ("Oslo",))
    print("In Oslo:", cursor.fetchone()[0])

Exercise

Which INSERT is safe from SQL injection?

cursor.execute(f"INSERT INTO contacts VALUES ('{name}', '{phone}')") cursor.execute("INSERT INTO contacts VALUES (?, ?)", (name, phone)) cursor.execute("INSERT INTO contacts VALUES (" + name + ")") cursor.execute("INSERT INTO contacts VALUES (%s, %s)" % (name, phone))