How do I use an SQLite database from Python?

The short version

The standard library's sqlite3 module gives you a full SQL database in a single file, or in memory, with no server to run. Connect, run SQL with execute, and call commit to save changes. Always pass values through parameter placeholders (?), never by building the SQL string yourself, to stay safe from SQL injection.

Connecting and creating a table

import sqlite3

con = sqlite3.connect("shop.db")     # or ":memory:" for a temporary database
con.execute("CREATE TABLE IF NOT EXISTS product (name TEXT, price INTEGER)")
con.commit()

connect opens (or creates) the database file. Passing ":memory:" gives a database that lives only for the session, which is handy for tests.

Inserting safely

Use ? placeholders and pass the values separately. This is the single most important rule.

con.execute("INSERT INTO product VALUES (?, ?)", ("pen", 2))
con.executemany(
    "INSERT INTO product VALUES (?, ?)",
    [("mug", 5), ("cup", 4)],
)
con.commit()

executemany inserts many rows in one call. Never build the SQL with an f-string or %, because a value containing SQL could then run as code, which is the classic injection vulnerability.

The injection trap

# WRONG: never do this
name = user_input
con.execute(f"SELECT * FROM product WHERE name = '{name}'")

If user_input is '; DROP TABLE product; --, this executes destructive SQL. The placeholder version is immune, because the value is passed as data, not spliced into the query.

con.execute("SELECT * FROM product WHERE name = ?", (name,))

Reading results

execute returns a cursor you can iterate or drain with fetchall and fetchone.

rows = con.execute("SELECT name FROM product WHERE price > ?", (3,)).fetchall()
print(rows)   # [('mug',), ('cup',)]

Each row is a tuple by default. Set con.row_factory = sqlite3.Row to access columns by name instead, which is clearer for wide tables.

Committing and closing

Changes are not saved until you commit, and the connection should be closed when done. A with block on the connection commits automatically on success and rolls back on error, which is the safe pattern.

with sqlite3.connect("shop.db") as con:
    con.execute("INSERT INTO product VALUES (?, ?)", ("pad", 3))
    # committed automatically at the end of the block

Working with pandas

For analysis, pandas reads a query straight into a DataFrame and writes one back to a table, which is often the easiest way to move between SQL and analysis.

import pandas as pd
df = pd.read_sql("SELECT * FROM product", con)
df.to_sql("product_copy", con, index=False)

When SQLite is and is not enough

SQLite is excellent for local storage, small applications, tests and single-user tools, and it needs no setup. For many concurrent writers or very large multi-user systems, a client-server database like PostgreSQL is a better fit. For a great deal of everyday Python work, though, the built-in sqlite3 is all you need.