Databases in Python: sqlite3

Working with the standard `sqlite3` module: connections and cursors, `CREATE`/`INSERT`/`SELECT`, parameters that prevent SQL injection, `executemany`, `row_factory`, transactions with `with`, a small library app and a first look at SQLAlchemy.

  • Advanced
  • 22 min
  • 12 questions
  • Lesson 37 of 66

In this lesson you will learn

  • Connect to a database, create a table, insert rows and read query results
  • Pass values only as parameters and explain how SQL injection works
  • Work efficiently with executemany, fetchone/fetchall and sqlite3.Row
  • Group related changes into a with conn: transaction

A school library keeps its list in a JSON file. To find a book you read the whole file, to change one book you rewrite all of it, and if writing stops halfway, the file is damaged. A database solves these problems, and SQLite is the simplest one: it needs no server, the whole database is one file, and Python includes it as the sqlite3 module. SQLite runs in phones, browsers and thousands of programs. In the SQL course you wrote queries by hand; now a Python program will run them.

Connection and cursorDefinition

A connection (sqlite3.connect(...)) is an open link to one database: it manages transactions (commit, rollback) and must be closed. A cursor (conn.cursor()) runs a statement and hands out the result rows one by one. conn.execute(...) is a shortcut that creates a cursor and returns it.

Connecting and the first table

sqlite3.connect('library.db') opens the file (and creates it if it does not exist), while sqlite3.connect(':memory:') creates a temporary database in memory that disappears when the connection closes — ideal for examples. execute runs SQL text. Changes are not confirmed until conn.commit() is called. An INTEGER PRIMARY KEY column numbers new rows by itself, and cur.lastrowid shows the number.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('''
    CREATE TABLE books (
        id     INTEGER PRIMARY KEY,
        title  TEXT NOT NULL,
        author TEXT NOT NULL,
        year   INTEGER,
        copies INTEGER DEFAULT 1
    )
''')
cur.execute("INSERT INTO books (title, author, year) VALUES ('Ali and Nino', 'Kurban Said', 1937)")
print(cur.lastrowid, cur.rowcount)
conn.commit()

cur.execute('SELECT id, title, year, copies FROM books')
print(cur.fetchall())
conn.close()
Expected output
1 1
[(1, 'Ali and Nino', 1937, 1)]
lastrowid is 1 (the new book’s id), and rowcount is the number of changed rows. copies was not given, so DEFAULT 1 was used. The result is a list of tuples.
  1. Connect

    conn = sqlite3.connect('library.db') — the file is created if it does not exist.

  2. Execute with parameters

    conn.execute('... WHERE id = ?', (book_id,)) — values only as parameters.

  3. Read

    fetchone(), fetchall() or a loop over the cursor.

  4. Commit

    conn.commit() or a with conn: block.

  5. Close

    conn.close() when the work is done.

PythonSQLite
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB
Store dates as ISO text ('2026-10-03'): it sorts correctly and works with SQL date functions.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

Parameters instead of string building

Gluing a user’s input into the SQL text with an f-string is the most dangerous mistake. If the value contains an apostrophe, it closes the string and the rest is read as SQL code — this is called SQL injection. In a parameterised query the text contains only ? placeholders, the values travel separately and are never read as code.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE users (name TEXT, password TEXT)')
conn.executemany('INSERT INTO users VALUES (?, ?)', [('aysel', 'x7!kL'), ('murad', 'p@ss9')])


def login_unsafe(name, password):
    sql = f"SELECT name FROM users WHERE name = '{name}' AND password = '{password}'"
    return conn.execute(sql).fetchall()


def login_safe(name, password):
    sql = 'SELECT name FROM users WHERE name = ? AND password = ?'
    return conn.execute(sql, (name, password)).fetchall()


attack = "' OR '1'='1"
print(login_unsafe('aysel', 'wrong'))
print(login_unsafe('aysel', attack))
print(login_safe('aysel', attack))
print(login_safe('aysel', 'x7!kL'))
Expected output
[]
[('aysel',), ('murad',)]
[]
[('aysel',)]
The passwords are stored in plain text only to keep the example short; real apps store only password hashes. With the attack string the unsafe query returned every user.

Worked example: the injection step by step

Problem

With password = "' OR '1'='1", what SQL does login_unsafe run, and why does everyone get in?

Show solutionHide solution

Solution

1) After substitution: WHERE name = 'aysel' AND password = '' OR '1'='1'.
2) AND binds before OR: (name = 'aysel' AND password = '') OR '1'='1'.
3) '1'='1' is true for every row, so the condition is true for all rows and the query returns every user.
4) With ? the text ' OR '1'='1 is compared as a password; no such password exists, so the result is [].

There are two placeholder styles: ? takes values from a tuple in order, and :name takes them from a dictionary by name. Careful: a one-value tuple needs a comma — (author,). Without the comma, (author) is just a string in brackets, and Python counts each of its letters as a separate parameter. Placeholders are only for values; table and column names cannot be passed as parameters.

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (title TEXT, author TEXT, year INTEGER)')
conn.executemany('INSERT INTO books VALUES (:title, :author, :year)', [
    {'title': 'Animal Farm', 'author': 'George Orwell', 'year': 1945},
    {'title': 'Nineteen Eighty-Four', 'author': 'George Orwell', 'year': 1949},
    {'title': 'Robinson Crusoe', 'author': 'Daniel Defoe', 'year': 1719},
])
author = 'George Orwell'
print(conn.execute('SELECT title FROM books WHERE author = ? ORDER BY year', (author,)).fetchall())
print(conn.execute('SELECT title FROM books WHERE year > :after', {'after': 1900}).fetchall())

try:
    conn.execute('SELECT title FROM books WHERE author = ?', (author))
except sqlite3.ProgrammingError as e:
    print('ProgrammingError:', e)
Expected output
[('Animal Farm',), ('Nineteen Eighty-Four',)]
[('Animal Farm',), ('Nineteen Eighty-Four',)]
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 13 supplied.
'George Orwell' has 13 characters — the tuple without a comma was passed as 13 parameters. executemany with a list of dictionaries filled the names :title, :author and :year.

Reading results: fetch methods and row_factory

A cursor can be iterated: for row in conn.execute(...) hands out rows one by one and saves memory on big results. fetchone() returns the next row, or None when no rows are left, and fetchall() returns all remaining rows as a list. executemany repeats one statement for every element of a list — the fastest way to insert thousands of rows. By default a row is a tuple; after conn.row_factory = sqlite3.Row you can reach columns by name: row['title'], dict(row).

Python
import sqlite3

books = [
    ('Ali and Nino', 'Kurban Said', 1937, 3),
    ('The Little Prince', 'Antoine de Saint-Exupéry', 1943, 5),
    ('Robinson Crusoe', 'Daniel Defoe', 1719, 2),
    ('Animal Farm', 'George Orwell', 1945, 4),
    ('Nineteen Eighty-Four', 'George Orwell', 1949, 0),
]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER, copies INTEGER)')
cur = conn.executemany('INSERT INTO books (title, author, year, copies) VALUES (?, ?, ?, ?)', books)
print('inserted:', cur.rowcount)

conn.row_factory = sqlite3.Row
for row in conn.execute('SELECT id, title, year FROM books WHERE year < ? ORDER BY year', (1940,)):
    print(row['id'], row['title'], row['year'])

row = conn.execute('''SELECT author, COUNT(*) AS n, SUM(copies) AS total FROM books
                      GROUP BY author ORDER BY n DESC, author LIMIT 1''').fetchone()
print(dict(row), row.keys())
print(conn.execute('SELECT title FROM books WHERE copies = 99').fetchone())
Expected output
inserted: 5
3 Robinson Crusoe 1719
1 Ali and Nino 1937
{'author': 'George Orwell', 'n': 2, 'total': 4} ['author', 'n', 'total']
None
row_factory is set on the connection and affects later queries. When no row matches, fetchone() returns None — always check for it.
Method / attributeWhat it does
conn.execute(sql, params)runs one statement, returns a cursor
conn.executemany(sql, seq)repeats the statement for every element of a sequence
conn.executescript(text)runs several statements separated by ; (no parameters)
fetchone(), fetchall(), fetchmany(n)one row / all rows / n rows
cur.lastrowid, cur.rowcountid of the last inserted row / number of changed rows
conn.commit(), conn.rollback(), conn.close()confirm / cancel / close

Transactions and with

TransactionDefinition

A group of changes executed on an all-or-nothing basis. COMMIT confirms them all at once, ROLLBACK cancels them all as if nothing had happened. A money transfer is the classic example: taking money from one account and adding it to another must happen together.

By default the sqlite3 module opens a transaction by itself before INSERT, UPDATE and DELETE, and you must end it with commit(). The most convenient way is a with conn: block: if the block finishes without errors, it does COMMIT; if an exception happens, ROLLBACK. But the connection stays open — close it with close() or with contextlib.closing (see «Context managers and the with statement»). The theory is in the SQL course lesson «Indexes, views and transactions».

Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('''CREATE TABLE accounts (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL CONSTRAINT non_negative CHECK (balance >= 0))''')
with conn:
    conn.executemany('INSERT INTO accounts VALUES (?, ?)', [('Aysel', 100), ('Murad', 50)])


def transfer(src, dst, amount):
    try:
        with conn:    # one transaction: COMMIT on success, ROLLBACK on an error
            conn.execute('UPDATE accounts SET balance = balance + ? WHERE name = ?', (amount, dst))
            conn.execute('UPDATE accounts SET balance = balance - ? WHERE name = ?', (amount, src))
        print(f'{src} -> {dst}: {amount} OK')
    except sqlite3.IntegrityError as e:
        print(f'{src} -> {dst}: {amount} cancelled ({e})')


transfer('Aysel', 'Murad', 30)
transfer('Murad', 'Aysel', 500)
print(conn.execute('SELECT * FROM accounts ORDER BY name').fetchall())
conn.close()
Expected output
Aysel -> Murad: 30 OK
Murad -> Aysel: 500 cancelled (CHECK constraint failed: non_negative)
[('Aysel', 70), ('Murad', 80)]
In the second transfer Aysel received 500, then Murad’s balance would have become negative and the CHECK constraint raised IntegrityError. with did a ROLLBACK, so Aysel’s already written 500 was cancelled too.

Mini app: a library

Let us put the pieces together. connect opens the file and prepares the tables with CREATE TABLE IF NOT EXISTS, and borrow checks a book’s stock, reduces it and records the loan in one transaction. At the end the connection is closed and opened again: the data has stayed in the file.

Python
import sqlite3


def connect(path):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.executescript('''
        CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT UNIQUE NOT NULL,
                                          copies INTEGER NOT NULL CHECK (copies >= 0));
        CREATE TABLE IF NOT EXISTS loans (book_id INTEGER REFERENCES books (id), reader TEXT NOT NULL);
    ''')
    return conn


def borrow(conn, title, reader):
    with conn:
        book = conn.execute('SELECT id, copies FROM books WHERE title = ?', (title,)).fetchone()
        if book is None or book['copies'] == 0:
            return f'{reader}: {title!r} is not available'
        conn.execute('UPDATE books SET copies = copies - 1 WHERE id = ?', (book['id'],))
        conn.execute('INSERT INTO loans VALUES (?, ?)', (book['id'], reader))
    return f'{reader} borrowed {title!r}'


conn = connect('library.db')
with conn:
    conn.executemany('INSERT INTO books (title, copies) VALUES (?, ?)',
                     [('Ali and Nino', 1), ('The Little Prince', 2)])
for reader, title in [('Aysel', 'Ali and Nino'), ('Murad', 'Ali and Nino'), ('Leyla', 'Dune')]:
    print(borrow(conn, title, reader))
conn.close()

conn = connect('library.db')     # a new connection: the data is still in the file
sql = '''SELECT b.title, b.copies, COUNT(l.reader) AS loans
         FROM books b LEFT JOIN loans l ON l.book_id = b.id GROUP BY b.id ORDER BY b.title'''
for row in conn.execute(sql):
    print(dict(row))
conn.close()
Expected output
Aysel borrowed 'Ali and Nino'
Murad: 'Ali and Nino' is not available
Leyla: 'Dune' is not available
{'title': 'Ali and Nino', 'copies': 0, 'loans': 1}
{'title': 'The Little Prince', 'copies': 2, 'loans': 0}
LEFT JOIN also shows books without loans (loans 0). In the browser library.db is created anew in an empty folder on every run.

As a project grows, SQL strings, copying column names by hand and turning rows into objects become tiresome. SQLAlchemy does the same work with Python classes, and the same code keeps working when you move from SQLite to PostgreSQL. Compare (details in the lesson «SQLAlchemy: databases with an ORM»):

sqlite3

rows = conn.execute(
    'SELECT title, year FROM books WHERE author = ? ORDER BY year',
    ('George Orwell',),
).fetchall()

SQLAlchemy

stmt = (select(Book.title, Book.year)
        .where(Book.author == 'George Orwell')
        .order_by(Book.year))
rows = session.execute(stmt).all()
Both send parameterised SQL; in the ORM Book is a class describing the table, and session manages the connection and the transaction.

Exercise

Insert the products list into the table with one executemany call. Then select the name and price of the products that cost less than 10 and are in stock (stock > 0), cheapest first, and print every row as name: price. Pass the limit (10) as a parameter.

Exercise · Python
import sqlite3

products = [('pen', 1.5, 120), ('notebook', 3.2, 0), ('backpack', 45.0, 7),
            ('ruler', 0.8, 60), ('calculator', 18.9, 12)]
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE products (name TEXT, price REAL, stock INTEGER)')
# 1) insert all products with one executemany call
# 2) select name and price of the products cheaper than 10 that are in stock, cheapest first
# 3) print every row as "name: price"
Expected output
ruler: 0.8
pen: 1.5

Exercise

sell(name, qty) must, in one transaction, add a sale row to sales and reduce the quantity in stock. If the stock would drop below 0 (a CHECK error), nothing may change and the function returns False; on success it returns True.

Exercise · Python
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE stock (name TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0))')
conn.execute('CREATE TABLE sales (name TEXT, qty INTEGER)')
conn.executemany('INSERT INTO stock VALUES (?, ?)', [('pen', 5), ('ruler', 2)])
conn.commit()


def sell(name, qty):
    # in ONE transaction: add a row to sales and reduce the stock;
    # if the stock would drop below 0, nothing may change and the function returns False
    return False


print(sell('pen', 3), sell('ruler', 5), sell('pen', 2))
print(conn.execute('SELECT * FROM stock ORDER BY name').fetchall())
print(conn.execute('SELECT * FROM sales').fetchall())
Expected output
True False True
[('pen', 0), ('ruler', 2)]
[('pen', 3), ('pen', 2)]

Remember

  • sqlite3.connect(path) opens a file, ':memory:' a temporary database; conn.execute returns a cursor.
  • Always pass values as ? or :name parameters; a one-value tuple is (x,).
  • fetchone() (or None), fetchall(), or a loop over the cursor; sqlite3.Row gives access to columns by name.
  • executemany inserts many rows with one call.
  • with conn: commits on success and rolls back on an error but does not close the connection; close() without commit() loses changes.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. What does sqlite3.connect(':memory:') create?