Python SQLite3 Tutorial: Connect, Insert, Query and Commit (Tested Examples)

Ninad Pathak

October 7, 2026

Python ships with a full SQL database: the sqlite3 module is part of the standard library, so there’s nothing to install. In this article I’ll walk you through it, from your first connection to committing your changes.

TLDR

sqlite3 is part of Python’s standard library, so there’s nothing to install. pip install sqlite3 fails because no such package exists.
Connect with sqlite3.connect("file.db"), which creates the file if it’s missing.

Run statements with con.execute(sql, params), using ? placeholders for values.
Save changes with con.commit() or a with con: block, and close the connection when you’re done.

What is Python’s sqlite3 module, and what will we build?

sqlite3 is the standard-library module for SQLite databases. The Python documentation describes SQLite as “a C library that provides a lightweight disk-based database that doesn’t require a separate server process” (Python docs: sqlite3). The module follows Python’s database API, DB-API 2.0 (PEP 249), so its connection and cursor objects look like those of other Python database drivers.

To show you how this works, I will build a small library catalogue around a books table. Step 1 saves it to a file, and the later steps use a fresh in-memory database so each script runs on its own.

Prerequisites

You need Python 3. The module comes with it, but it’s technically optional: “This is an optional module. If it is missing from your copy of CPython, look for documentation from your distributor.” Installing it with pip doesn’t work:

$ pip install sqlite3
ERROR: Could not find a version that satisfies the requirement sqlite3 (from versions: none)
ERROR: No matching distribution found for sqlite3

Each Python build links its own SQLite library, so the SQLite version depends on which Python you run. These are the three I tested with:

Python 3.13.14 with SQLite 3.53.4
Python 3.12.14 with SQLite 3.53.1
Python 3.9.6 with SQLite 3.51.0

sqlite3.sqlite_version is the “Version number of the runtime SQLite library as a string”. To get a newer SQLite in Python, use a Python build that links a newer SQLite library.

Building a library database with sqlite3

Each step is a complete script. Step 1 writes library.db, and the others use ":memory:", so you can run them in any order.

Step 1: Create the database and a table

connect() creates library.db if it doesn’t exist:

import sqlite3

con = sqlite3.connect("library.db")  # creates the file if it doesn't exist
con.execute("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, year INTEGER)")
con.execute("INSERT INTO books (title, year) VALUES (?, ?)", ("The C Programming Language", 1978))
con.commit()

for row in con.execute("SELECT id, title, year FROM books ORDER BY id"):
    print(row)
con.close()

$ python3 quickstart.py
(1, 'The C Programming Language', 1978)

CREATE TABLE IF NOT EXISTS makes the script safe to run twice, but the INSERT isn’t. A second run adds the row again:

$ python3 quickstart.py
(1, 'The C Programming Language', 1978)
(2, 'The C Programming Language', 1978)

To insert a row only if it isn’t there, use a UNIQUE constraint with an upsert. The same script gave the same output on Python 3.12 and 3.9.

Step 2: Insert many rows with executemany()

executemany() runs one statement for each item in a list:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, year INTEGER)")

books = [
    ("Structure and Interpretation of Computer Programs", 1985),
    ("The Pragmatic Programmer", 1999),
    ("Designing Data-Intensive Applications", 2017),
]
con.executemany("INSERT INTO books (title, year) VALUES (?, ?)", books)

# Named placeholders take a dict
con.execute("INSERT INTO books (title, year) VALUES (:title, :year)", {"title": "Refactoring", "year": 1999})
con.commit()

cur = con.execute("SELECT title FROM books WHERE year = ? ORDER BY title", (1999,))
print(cur.fetchall())
print(con.execute("SELECT count(*) FROM books").fetchone()[0])
con.close()

$ python3 insert_many.py
[('Refactoring',), ('The Pragmatic Programmer',)]
4

Step 3: Pass values with placeholders

Pass values separately from the SQL, using ? for positional values or :name for named ones. The Python documentation is explicit: “Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks” (How to use placeholders). String formatting also breaks on ordinary data, such as a name with an apostrophe:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE authors (name TEXT)")
name = "Flannery O'Connor"

try:
    con.execute(f"INSERT INTO authors (name) VALUES ('{name}')")  # don't do this
except sqlite3.OperationalError as e:
    print("f-string:", e)

con.execute("INSERT INTO authors (name) VALUES (?)", (name,))  # do this
print("placeholder:", con.execute("SELECT name FROM authors").fetchone()[0])
con.close()

$ python3 placeholders.py
f-string: near "Connor": syntax error
placeholder: Flannery O'Connor

Step 4: Read rows

execute() returns a cursor. Iterate over the cursor to read rows. fetchone() and fetchall() also work. Rows are tuples by default. Set row_factory = sqlite3.Row to read columns by name (row factories):

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
con.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)")
con.execute("INSERT INTO books (title, year) VALUES ('Refactoring', 1999)")

row = con.execute("SELECT id, title, year FROM books").fetchone()
print(row["title"], row["year"])
print(row.keys())
print(dict(row))
con.close()

$ python3 rows.py
Refactoring 1999
['id', 'title', 'year']
{'id': 1, 'title': 'Refactoring', 'year': 1999}

Step 5: Update and delete rows

cursor.rowcount tells you how many rows an UPDATE or DELETE changed:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)")
con.executemany("INSERT INTO books (title, year) VALUES (?, ?)",
                [("Refactoring", 1999), ("The Pragmatic Programmer", 1999), ("Clean Code", 2008)])

cur = con.execute("UPDATE books SET year = ? WHERE title = ?", (2018, "Refactoring"))
print("updated:", cur.rowcount)
cur = con.execute("DELETE FROM books WHERE year < ?", (2000,))
print("deleted:", cur.rowcount)
con.commit()
print(con.execute("SELECT title, year FROM books ORDER BY id").fetchall())
con.close()

$ python3 update_delete.py
updated: 1
deleted: 1
[('Refactoring', 2018), ('Clean Code', 2008)]

Step 6: Group changes in a transaction

Changes aren’t saved until you commit. Used as a context manager, a connection “automatically commits or rolls back open transactions when leaving the body” (connection context manager). Here a transfer that would break a CHECK constraint is rolled back as a whole:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
con.executemany("INSERT INTO accounts VALUES (?, ?)", [("alice", 100), ("bob", 0)])
con.commit()

def transfer(amount):
    with con:  # commits on success, rolls back on an exception
        con.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'bob'", (amount,))
        con.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'alice'", (amount,))

transfer(30)
try:
    transfer(500)  # alice would go negative: the CHECK constraint fails
except sqlite3.IntegrityError as e:
    print("rolled back:", e)

print(con.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
con.close()

$ python3 transactions.py
rolled back: CHECK constraint failed: balance >= 0
[('alice', 70), ('bob', 30)]

The with block doesn’t close the connection. Call con.close() yourself, or wrap the connection in contextlib.closing(). Since Python 3.13, a connection that is deleted without being closed emits a ResourceWarning (Connection).

Running it

Each script above ran with Python 3.13.14 and SQLite 3.53.4, and the outputs are from that run. Step 1 is the only one that leaves a file behind, library.db.

The connect() options you’ll reach for next (connect() reference):

Parameter	What it does
`timeout`	Seconds to wait for a lock before raising `OperationalError`. The default is 5.0
`check_same_thread`	By default, only the thread that created a connection can use it
`autocommit`	Added in Python 3.12 to choose PEP 249 transaction behaviour (Connection.autocommit)

Pass ":memory:" instead of a file name for a temporary database that lives only in RAM, which is handy for tests.

Common problems

These are the errors you’re likely to hit once more than one connection is involved.

“database is locked”

SQLite allows one writer at a time. If another connection holds an uncommitted write, a second writer waits up to timeout seconds and then raises OperationalError: database is locked. In this script, the first connection doesn’t commit until after the second one gives up:

import sqlite3

writer = sqlite3.connect("locked.db")
writer.execute("CREATE TABLE IF NOT EXISTS log (msg TEXT)")
writer.commit()
writer.execute("INSERT INTO log VALUES ('first')")  # opens a write transaction, not committed yet

other = sqlite3.connect("locked.db", timeout=0.1)  # wait at most 0.1 s for the lock
try:
    other.execute("INSERT INTO log VALUES ('second')")
except sqlite3.OperationalError as e:
    print("OperationalError:", e)

writer.commit()  # releases the lock
other.execute("INSERT INTO log VALUES ('second')")
other.commit()
print(other.execute("SELECT msg FROM log").fetchall())
writer.close()
other.close()

$ python3 locked.py
OperationalError: database is locked
[('first',), ('second',)]

Keep write transactions short and commit promptly. More causes and fixes are in SQLite database is locked. WAL mode lets readers keep reading while one connection writes.

Rows inserted but not saved

If you insert rows and they’re gone the next time you open the file, check that the transaction was committed. Call con.commit() or use with con: as in Step 6.

Conclusion

Python’s sqlite3 module gives you a real SQL database in one file, with nothing to install. Connect and use ? placeholders for every value. Then commit your changes. Keep write transactions short once more than one connection uses the same file.