How to Connect to SQLite from Python

SQLite is the one database Python can talk to with zero installation: the sqlite3 module ships in the standard library. There is no server, no port, no credentials. A database is a file, and connecting means opening that file. That simplicity hides a few sharp edges around transactions, threading, and concurrent writes, which is what most of this guide is about.

Which option should you use?

Short version: use sqlite3 unless you have a reason not to. Use aiosqlite (0.22.1 as of June 2026) in asyncio applications. Use APSW when you need precise control over SQLite behavior and want to opt out of the stdlib's transaction handling. SQLAlchemy makes sense when the same code must also run against PostgreSQL or MySQL.

Connecting with sqlite3

import sqlite3
 
conn = sqlite3.connect("app.db")
cursor = conn.execute("SELECT sqlite_version()")
print(cursor.fetchone()[0])
conn.close()

connect() creates the file if it does not exist. That is convenient and also a classic bug: a typo in the path silently gives you a new empty database instead of an error. If the file should already exist, open it in read-only mode with a URI (below) or check os.path.exists first.

An in-memory database lives only as long as the connection:

conn = sqlite3.connect(":memory:")

Read-only and other URI options

Pass uri=True to unlock the file: URI syntax:

conn = sqlite3.connect("file:app.db?mode=ro", uri=True)

mode=ro fails with an error if the file is missing and rejects writes -- the safe way to open a database you only intend to query. mode=rwc (the default) creates the file if needed; mode=memory gives a shared in-memory database when combined with cache=shared.

Parameters: always ?, never f-strings

# Right
conn.execute("SELECT * FROM users WHERE email = ?", (email,))
 
# Also right: named parameters
conn.execute("SELECT * FROM users WHERE email = :email", {"email": email})
 
# Wrong: SQL injection
conn.execute(f"SELECT * FROM users WHERE email = '{email}'")

Note the one-element tuple (email,) -- passing a bare string is a common first-day error because sqlite3 will iterate it character by character and complain about the wrong number of bindings.

Rows as dictionaries

By default rows come back as plain tuples. Set a row factory to access columns by name:

conn.row_factory = sqlite3.Row
row = conn.execute("SELECT id, email FROM users LIMIT 1").fetchone()
print(row["email"])

sqlite3.Row is implemented in C and costs almost nothing. dict(row) converts a Row when you need a real dictionary, for example to serialize as JSON.

Transactions: the with trap

This is the most misunderstood part of the module. Using a connection as a context manager commits or rolls back a transaction -- it does not close the connection:

with sqlite3.connect("app.db") as conn:
    conn.execute("INSERT INTO logs (msg) VALUES (?)", ("hello",))
# transaction committed here, but conn is STILL OPEN
 
conn.close()  # you still have to do this

If you want both behaviors, combine contextlib.closing with the transaction context manager, or just call close() explicitly.

The second surprise is implicit transactions. In the default (legacy) mode, sqlite3 opens a transaction before your first INSERT/UPDATE/DELETE and holds it until you call commit(). Forgetting commit() means your writes vanish when the process exits, and the open transaction blocks other writers in the meantime.

Python 3.12 added an explicit autocommit parameter that makes the behavior PEP 249-compliant:

conn = sqlite3.connect("app.db", autocommit=False)  # explicit transactions, recommended

With autocommit=False a transaction is always open and commit()/rollback() behave the way users of other databases expect. With autocommit=True every statement commits immediately. If you support Python older than 3.12 you are stuck with the legacy isolation_level behavior; just remember to commit.

Concurrency: WAL mode and busy timeouts

SQLite allows many concurrent readers but only one writer. Out of the box (rollback journal mode), a writer also blocks readers. If your application has any concurrency at all -- a web app, background jobs -- enable write-ahead logging once per database:

conn.execute("PRAGMA journal_mode=WAL")

WAL is persistent: set it once and the database file stays in WAL mode. Readers no longer block the writer and vice versa. There is still only one writer at a time, so concurrent writes queue up. Control how long a connection waits for the write lock before raising sqlite3.OperationalError: database is locked:

conn = sqlite3.connect("app.db", timeout=10)  # seconds, default 5

If you see database is locked in production, the fix is almost always: WAL mode on, a reasonable timeout, and keeping write transactions short. It is not by itself a reason to abandon SQLite.

Threads

By default a connection can only be used from the thread that created it (check_same_thread=True). The simple, correct pattern is one connection per thread. Passing check_same_thread=False is only safe if you serialize access yourself; sharing one connection across threads without a lock is a recipe for corrupted state.

Async with aiosqlite

SQLite has no network protocol, so "async SQLite" really means running blocking calls in a worker thread. That is exactly what aiosqlite does, with an API that mirrors sqlite3:

import asyncio
import aiosqlite
 
async def main():
    async with aiosqlite.connect("app.db") as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("SELECT id, email FROM users") as cursor:
            async for row in cursor:
                print(row["email"])
        await db.commit()
 
asyncio.run(main())

Unlike the stdlib, aiosqlite's async with connect(...) does close the connection on exit. Because each connection owns a thread, do not open one per request in a busy service; reuse a connection or use a small pool. If an asyncio web app is bottlenecked on SQLite writes, that is usually the moment to ask whether you have outgrown SQLite -- see SQLite vs MySQL for where the line sits.

Common errors

Dates and types

SQLite has no native date type, and Python's old implicit datetime adapters were deprecated in Python 3.12. Store dates as ISO 8601 strings or Unix timestamps and convert explicitly:

from datetime import datetime, timezone
conn.execute("INSERT INTO events (at) VALUES (?)", (datetime.now(timezone.utc).isoformat(),))

Explicit conversion is less magical and survives across language versions and tools. For querying date columns, see SQLite date functions.

Related guides

• SQLite transactions -- locking modes, SQLITE_BUSY, savepoints
• Import CSV to SQLite
• Best SQLite GUI clients

Mako connects to SQLite files with AI-powered autocomplete. Try it free at mako.ai.