INSERT, UPDATE and DELETE
Try it: INSERT, UPDATE and DELETE
How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.
How it works
- The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
- INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
- UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
- UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
- cur.rowcount is the number of rows inserted, changed or removed.
- The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.
Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.
Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.
Loading the simulation…