Topolearnsqlite3 ships with Python, so it's the quickest way to give any script or app a real database.
Python's standard library includes the sqlite3 module, so there's nothing to install.
con = sqlite3.connect("app.db")opens a connection (":memory:"for a throwaway database).con.execute(sql, params)runs a statement and returns a cursor;fetchone(),fetchall()or iterating the cursor gets rows as tuples.- Always pass values as parameters:
?placeholders with a tuple, or:nameplaceholders with a dict. Never build SQL with f-strings — that invites SQL injection and breaks on quotes. executemany(sql, rows)inserts many rows efficiently.con.row_factory = sqlite3.Rowreturns rows you can index by column name.
Transactions: by default (the legacy mode) the module opens a transaction implicitly before INSERT, UPDATE, DELETE and REPLACE, and nothing is saved until you call con.commit(). Closing without committing discards the changes. Using the connection as a context manager, with con:, commits if the block succeeds and rolls back if it raises — but it does not close the connection. Python 3.12 added an explicit autocommit parameter that makes this behaviour configurable (next lessons).
cursor.lastrowid gives the id of the last inserted row and cursor.rowcount the number of rows changed. Use contextlib.closing(con) or call con.close() when you're done.
Example
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
con.execute("CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT, city TEXT)")
with con: # commit on success
cur = con.execute("INSERT INTO user (name, city) VALUES (?, ?)", ("Ani", "Yerevan"))
print(cur.lastrowid) # 1
con.executemany("INSERT INTO user (name, city) VALUES (?, ?)",
[("Davit", "Gyumri"), ("O'Brien", "Dublin")]) # quotes are safe
for row in con.execute("SELECT id, name FROM user WHERE city = :c", {"c": "Gyumri"}):
print(row["id"], row["name"]) # 2 Davit
name = "x' OR '1'='1" # injection attempt is just data
print(con.execute("SELECT count(*) FROM user WHERE name = ?", (name,)).fetchone()[0]) # 0
con.close()Remember
- Use ? or :name placeholders, never string formatting
- Changes need con.commit() (or a with con: block)
- with con: commits or rolls back but doesn't close the connection
Watch out for
- Forgetting to commit, so inserts silently disappear when the program exits.