Skip to content
Tutorials Center
My learning

Topics

Python Reference ยท Files, Paths and Text I/O

The sqlite3 module

Reference for Python's sqlite3 module: connect to SQLite, run parameterized queries, fetch rows, use transactions and row factories, and avoid SQL injection.

The sqlite3 module is Python's built-in interface to SQLite, a complete SQL database stored in a single file (or in memory). There is no server to install, which makes it ideal for small applications, caches, tests and learning SQL.

The pattern is always the same: open a connection, execute SQL through it (or through a cursor), fetch rows, and commit changes. Always pass values as parameters with ? placeholders, never by pasting them into the SQL string.

Python
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")

Try it

Python Create, insert, query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Named rows, transactions and errors

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Main API

Name Description
sqlite3.connect(database) Opens (or creates) a database file; ":memory:" creates a temporary in-memory database. Returns a Connection.
Connection.execute(sql, params=()) Shortcut that creates a cursor, runs the statement and returns the cursor.
Connection.executemany(sql, seq_of_params) Runs one statement once for each parameter set.
Connection.executescript(sql) Runs several statements separated by semicolons. No parameters.
Connection.commit(), rollback(), close() End a transaction or close the connection. close() does not commit.
Cursor.fetchone(), fetchmany(n), fetchall() Return one row (or None), up to n rows, or all remaining rows. Cursors are also iterable.
Cursor.rowcount, lastrowid Rows changed by the last write; the row id of the last insert.
Connection.row_factory Set to sqlite3.Row to access columns by name or index.
sqlite3.Error Base exception. Subclasses include IntegrityError, OperationalError, ProgrammingError and DatabaseError.

Usage notes

  • Parameters, not string formatting. Use ? (or :name) placeholders and pass values separately. This prevents SQL injection and handles quoting for you. Placeholders only work for values, not for table or column names.
  • Parameters must be a sequence or mapping. A single value needs a tuple: ("Ada",), not ("Ada"), or SQLite sees one binding per character.
  • Transactions. Data-changing statements start a transaction implicitly in the default mode, and nothing is saved until commit(). Using the connection in a with block commits on success and rolls back on an exception, but it does not close the connection.
  • Types. SQLite stores NULL, INTEGER, REAL, TEXT and BLOB, which map to None, int, float, str and bytes. Other Python types (datetime, Decimal) need adapters or must be converted to strings or numbers yourself. SQLite is loosely typed: a column's declared type is a hint, not a strict rule, unless the table is declared STRICT.
  • Integer division. 7 / 2 is 3 in SQL when both sides are integers, unlike Python.
  • Foreign keys are off by default. Run PRAGMA foreign_keys = ON on each new connection to enforce them.
  • One writer at a time. Many readers can work in parallel, but writes lock the database file. Connections are not shared across threads by default.
  • In the browser runner, files exist only during one run, so use ":memory:" or build everything inside the example.