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.
import sqlite3 con = sqlite3.connect(":memory:") con.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
Try it
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.
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 awithblock commits on success and rolls back on an exception, but it does not close the connection. - Types. SQLite stores
NULL,INTEGER,REAL,TEXTandBLOB, which map toNone,int,float,strandbytes. 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 declaredSTRICT. - Integer division.
7 / 2is3in SQL when both sides are integers, unlike Python. - Foreign keys are off by default. Run
PRAGMA foreign_keys = ONon 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.
Related
json,csv,pickle- Lessons: SQLite in Python, SQLAlchemy, Context managers