Python’s standard-library sqlite3 module lets a script open or create an SQLite database, run SQL, retrieve rows, and save changes without a separate database server. The key steps are to choose a file or in-memory database, bind values with placeholders, manage the transaction deliberately, and close the connection.
Choose a database target
Use sqlite3.connect() with a path-like target. A file-backed database persists after the connection closes and can be opened again; :memory: creates a temporary database that disappears when its connection ends. The right choice depends on whether the data needs to outlive the current run.
As an Amazon Associate I earn from qualifying purchases.
| Target | Persistence | Typical use |
|---|---|---|
A filename such as tutorial.db |
Stored in a database file and available to later connections. | Application data or a script whose results should remain available. |
:memory: |
Temporary; available only while the in-memory connection exists. | Short-lived examples, experiments, or tests. |
Open a connection and create a table
Import sqlite3 and connect to a filename. If the file does not exist, connect() creates it. This example creates a table and adds two records using parameter placeholders:
Free tools Windows power users keep installed
One-click scans. No signup required.
import sqlite3
con = sqlite3.connect("tutorial.db")
try:
con.execute("""
CREATE TABLE IF NOT EXISTS movie (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
year INTEGER NOT NULL
)
""")
movies = [("The Matrix", 1999), ("Arrival", 2016)]
con.executemany(
"INSERT INTO movie(title, year) VALUES(?, ?)",
movies,
)
con.commit()
finally:
con.close()The optional connection settings in new code should be passed by keyword. Python 3.14 documentation marks positional use of several connect() parameters as deprecated; they become keyword-only in Python 3.15. The standard-library module is optional in some CPython distributions and depends on the SQLite library, so a missing module may require guidance from your Python distributor. Python’s sqlite3 documentation covers the available arguments and platform caveats.
#1 Best Overall
Bind values safely instead of building SQL strings
Use ? placeholders for values, then pass those values separately as a tuple or other supported parameter sequence. For multiple records, executemany() accepts an iterable of parameter sets, as in the example above. Do not use f-strings, concatenation, or other string formatting to insert input into SQL: placeholders keep SQL code separate from data and help prevent SQL injection.
title = "Spirited Away"
year = 2001
con.execute(
"INSERT INTO movie(title, year) VALUES(?, ?)",
(title, year),
)Read query results
For a query, call execute() and fetch the rows. Each returned row is sequence-like by default, so the selected columns can be accessed by position:
con = sqlite3.connect("tutorial.db")
try:
rows = con.execute(
"SELECT title, year FROM movie ORDER BY year"
).fetchall()
for title, year in rows:
print(title, year)
finally:
con.close()fetchall() returns all remaining rows as a list. For a large result set, iterate over the cursor returned by execute() instead of collecting every row at once.
Commit or roll back changes intentionally
Transaction behavior depends on the connection’s mode and, in some cases, Python version. Python 3.14.8 recommends controlling transactions with the autocommit attribute. With autocommit=False, the connection follows PEP 249-compliant behavior: a transaction remains open and changes must be committed or rolled back. With autocommit=True, SQLite’s autocommit mode applies, and calls to commit() or rollback() have no effect.
The current default is LEGACY_TRANSACTION_CONTROL, and the documentation says this default will change to False in a future Python release. Under legacy control, isolation_level governs implicit transaction behavior. For code that depends on a particular policy, specify it explicitly rather than relying on a default that may change.
| Mode | Behavior | Effect of commit() and rollback() |
|---|---|---|
autocommit=False |
PEP 249-compliant transaction control; a transaction remains open. | Use them to save or undo pending changes. |
autocommit=True |
SQLite autocommit mode. | Both methods have no effect. |
LEGACY_TRANSACTION_CONTROL |
Current default; implicit behavior is governed by isolation_level. |
Behavior follows the legacy transaction rules. |
For a straightforward script, explicitly calling commit() after successful writes and rollback() when handling a failure makes the intended outcome clear. Python’s connection context manager can also commit an open transaction when its block exits normally and roll it back when an uncaught exception exits the block.
Understand what the connection context manager does
A connection’s with block controls the transaction outcome; it does not close the connection. Close it separately, or use contextlib.closing() when you want a context manager to close it too.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
import sqlite3
from contextlib import closing
with closing(sqlite3.connect("tutorial.db")) as con:
with con:
con.execute(
"INSERT INTO movie(title, year) VALUES(?, ?)",
("Moonlight", 2016),
)Here, the inner block commits on normal exit and rolls back if an uncaught exception leaves it; the outer closing() block closes the connection. Since Python 3.13, discarding a connection without calling close() can raise a ResourceWarning.
Handle locking and threads with care
- Lock timeout: The documented default timeout is five seconds. If a table remains locked beyond the connection’s timeout, an operation can raise
OperationalError. Thetimeoutargument can adjust how long the connection waits. - Thread ownership:
check_same_thread=Trueis the default and rejects using a connection from a thread other than the one that created it. Setting it toFalseremoves that check; it does not make concurrent writes safe. Coordinate or serialize writes as needed, and account for the threading mode of the underlying SQLite build. - URI targets: Set
uri=Trueto use afile:URI as the database target. This is an opt-in connection setting, not required for an ordinary filename.
Reopen the file to verify persistence
After closing a file-backed connection, open the same path in a new connection and query it. Rows returned from that second connection confirm that the data was saved to the file rather than existing only in the first connection’s transaction or in memory.
import sqlite3
with sqlite3.connect("tutorial.db") as con:
rows = con.execute(
"SELECT title, year FROM movie ORDER BY year"
).fetchall()
print(rows)The connection context manager commits or rolls back but still does not close the connection, so production code should close it explicitly—for example, with contextlib.closing() around this pattern.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
