How to connect to SQLite

SQLite is a file-based database: no separate server, just a .db file. Python’s sqlite3 module is in the standard library. This guide shows how to connect, run queries, fetch results, and commit or roll back changes. You should know basic functions and lists; for parameterised queries and safety see query SQLite safely.

1. When to use SQLite

SQLite is ideal for local apps, scripts, and small data sets. The database is a single file, so you can copy or back it up easily. Use it when you need structured storage (tables, SQL) without installing PostgreSQL or MySQL. For web apps, frameworks like Django provide an ORM that can use SQLite; for raw SQL in scripts, sqlite3 is enough.

2. Connect and create a cursor

sqlite3.connect(path) opens or creates the database file. If the path does not exist, SQLite creates it. Use a context manager (with) or explicitly conn.close() when done. A cursor is created with conn.cursor() and is used to execute SQL and fetch results.

import sqlite3

conn = sqlite3.connect("app.db")
cur = conn.cursor()

For in-memory databases (useful for tests), use sqlite3.connect(":memory:").

3. Create a table and insert data

Execute DDL and DML with cur.execute(sql). After changes that modify data (INSERT, UPDATE, DELETE), call conn.commit() to persist. Forgetting to commit leaves changes only in the current connection and they are lost when you close. Use conn.rollback() to undo uncommitted changes on error.

cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT
    )
""")
cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "[email protected]"))
conn.commit()

Always use parameter placeholders ? (or named placeholders) instead of building SQL with string formatting to avoid injection and escaping issues—see query SQLite safely.

4. Query and fetch results

Use cur.execute(sql) for SELECT, then cur.fetchone() (one row), cur.fetchall() (list of rows), or cur.fetchmany(n). Each row is a tuple of column values. Iterating over the cursor also yields rows.

cur.execute("SELECT * FROM users")
rows = cur.fetchall()
for row in rows:
    print(row)

cur.execute("SELECT name, email FROM users WHERE id = ?", (1,))
row = cur.fetchone()

5. Close the connection

When you are done, close the cursor and connection so the file is not locked. Using with sqlite3.connect("app.db") as conn: ensures the connection is closed (and by default commits on exit). If you open the connection manually, call conn.close(). Leaving connections open can cause I/O on closed file-style issues when the same file is used elsewhere.

6. Next steps

Use parameterised queries for all user input—see query SQLite safely. For reading and writing CSV or JSON see read CSV and read and write JSON. For building a web app with a database see Django.