iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Use Python’s built-in sqlite3 module to open or create an SQLite database, run SQL with bound parameters, and manage transactions. For a persistent database, connect to a file; for temporary data, connect to :memory:. In either case, decide how writes are committed and close the connection when you are finished.
Connect to a database file or an in-memory database
The sqlite3 module provides Python’s DB-API interface to SQLite. It is part of the standard library in many Python distributions, though CPython documents it as an optional module that depends on the SQLite library. If importing sqlite3 fails, consult the documentation for your Python distributor.
A file-backed connection opens an existing database or creates the file if it does not exist:
Recommended Free Tools
import sqlite3
con = sqlite3.connect("tutorial.db")Use a path-like value for a database file. The file persists after the connection closes, so a later run of the program can reopen it. By contrast, :memory: creates a temporary database that exists only in memory and is not saved as a database file.
#1 Best Overall
con = sqlite3.connect(":memory:")| Target | Persistence | Typical use |
|---|---|---|
A database file, such as tutorial.db |
Data remains available to reopen after the connection closes. | Application data that should persist between runs. |
:memory: |
Temporary; the database does not persist as a file. | Short-lived examples or temporary work. |
Both examples use the same connection and query APIs. Optional connection settings should be passed as keyword arguments in new code. Python 3.14 documentation marks positional use of several connect() parameters as deprecated; those parameters become keyword-only in Python 3.15.
Create a table, insert rows, and retrieve results
After connecting, call execute() to run a statement. You can call it directly on the connection for straightforward work, or create a cursor when you want to work explicitly with one. This example creates a table, inserts two rows, and reads them back:
import sqlite3
con = sqlite3.connect("tutorial.db")
try:
con.execute("CREATE TABLE IF NOT EXISTS movie (")
con.execute("""
CREATE TABLE IF NOT EXISTS movie (
title TEXT,
year INTEGER
)
""")
movies = [("Arrival", 2016), ("Moonlight", 2016)]
con.executemany(
"INSERT INTO movie(title, year) VALUES(?, ?)",
movies,
)
rows = con.execute(
"SELECT title, year FROM movie ORDER BY title"
).fetchall()
for title, year in rows:
print(title, year)
con.commit()
finally:
con.close()The first CREATE TABLE call above is unnecessary and malformed: omit it. Use this corrected complete version:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →import sqlite3
con = sqlite3.connect("tutorial.db")
try:
con.execute("""
CREATE TABLE IF NOT EXISTS movie (
title TEXT,
year INTEGER
)
""")
movies = [("Arrival", 2016), ("Moonlight", 2016)]
con.executemany(
"INSERT INTO movie(title, year) VALUES(?, ?)",
movies,
)
rows = con.execute(
"SELECT title, year FROM movie ORDER BY title"
).fetchall()
for title, year in rows:
print(title, year)
con.commit()
finally:
con.close()fetchall() returns the selected rows as a sequence; iterate over that result to process each row. executemany() runs a statement for multiple sets of bound values. The example inserts the same two titles each time it runs; use a uniqueness constraint or a different insertion strategy if repeated runs should not create duplicate records.
Bind values safely instead of building SQL with strings
Put a placeholder in the SQL where a value belongs, then pass the value separately. For example:
title = "Arrival"
year = 2016
con.execute(
"INSERT INTO movie(title, year) VALUES(?, ?)",
(title, year),
)The question marks are placeholders; the tuple supplies their values in order. Do not use string formatting or concatenation to insert user-provided values into SQL. Python’s official tutorial recommends placeholders to bind values and avoid SQL injection attacks. This applies to query values as well as inserted values.
Placeholders bind data values, not SQL structure such as table or column names. If a query needs a varying identifier, choose it from a fixed set of trusted options rather than treating arbitrary input as a placeholder value.
Understand when changes are saved
SQLite transaction behavior depends on the connection’s transaction-control mode. In the Python 3.14.8 documentation, the recommended control is the connection’s autocommit attribute. Choose the behavior deliberately instead of assuming that all Python versions use the same default.
| Mode | Transaction behavior | Effect of commit() and rollback() |
|---|---|---|
autocommit=False |
PEP 249-compliant behavior; a transaction is kept open and changes should be explicitly committed or rolled back. | Use them to commit or undo the current transaction. |
autocommit=True |
SQLite autocommit mode. | Both methods have no effect. |
LEGACY_TRANSACTION_CONTROL |
The current documented default; isolation_level controls implicit transaction behavior. |
Behavior follows the legacy transaction-control rules. |
The Python 3.14.8 documentation says the default will change to False in a future Python release. Because the default may change, set autocommit explicitly when predictable transaction behavior matters. For example, use sqlite3.connect("tutorial.db", autocommit=False) when you want to manage transactions with explicit commits and rollbacks.
Call con.commit() after successful writes when the selected mode requires an explicit commit. If an operation fails and you want to discard uncommitted changes, call con.rollback(). In autocommit=True mode, those calls do nothing, so do not rely on them to undo or save work.
Use a context manager without forgetting to close
A connection used as a context manager handles transaction outcome: it commits an open transaction when the block exits successfully and rolls it back if an uncaught exception leaves the block. It does not close the connection. Close it explicitly after the work is done.
import sqlite3
from contextlib import closing
with closing(sqlite3.connect("tutorial.db", autocommit=False)) as con:
with con:
con.execute(
"INSERT INTO movie(title, year) VALUES(?, ?)",
("Arrival", 2016),
)Here, closing() ensures the connection is closed, while the inner connection context manages the transaction. Python 3.13 added a ResourceWarning for a connection discarded before close() is called, so make connection cleanup explicit.
Account for timeouts, threads, and URI paths
- Locked database: The documented default connection timeout is 5.0 seconds. If a table remains locked beyond the timeout, SQLite can raise
OperationalError. A longer timeout may give another operation more time to finish, but it does not resolve a persistent locking problem. - Using a connection across threads:
check_same_thread=Trueis the default and prevents using a connection from a thread other than the one that created it. Setting it toFalsedoes not automatically make concurrent writes safe; writes may need to be serialized, and the underlying SQLite build’s threading mode also matters. - Opening a file URI: Set
uri=Trueif the database target is afile:URI rather than an ordinary path.
These options affect how a connection is opened or used; they do not replace parameter binding, transaction handling, or explicit cleanup.
Verify persistence by reopening the file
To confirm that file-backed changes persisted, close the connection and open the same database path again, then run a query:
import sqlite3
with sqlite3.connect("tutorial.db") as con:
rows = con.execute(
"SELECT title, year FROM movie ORDER BY title"
).fetchall()
print(rows)This verifies the rows in the named file can be read by a new connection. It is different from testing with :memory:, whose contents are temporary rather than stored in that file.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Reference
Python Software Foundation, sqlite3 — DB-API 2.0 interface for SQLite databases (Python 3.14.8 documentation).
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.
