DML Queries

Insert, update, and delete SQLite rows from Python, then commit the change or roll it back. Values stay outside the SQL text.

Example

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(
    "CREATE TABLE badge (code INTEGER PRIMARY KEY, name TEXT NOT NULL, room TEXT NOT NULL)"
)
cur.execute(
    "INSERT INTO badge (code, name, room) VALUES (?, ?, ?)",
    (1, "Avery", "North"),
)
con.commit()
cur.execute("UPDATE badge SET room = ? WHERE code = ?", ("West", 1))
con.commit()
cur.execute(
    "INSERT INTO badge (code, name, room) VALUES (?, ?, ?)",
    (2, "Jordan", "East"),
)
con.rollback()
cur.execute("SELECT code, name, room FROM badge")
print(cur.fetchall())
cur.execute("DELETE FROM badge WHERE code = ?", (1,))
con.commit()
cur.execute("SELECT name FROM badge")
print(cur.fetchall())
con.close()

The first list is [(1, 'Avery', 'West')]. Jordan was inserted and then discarded by rollback(), so that row is absent. After DELETE, the second list is []. commit() on the connection keeps the DML. rollback() undoes DML that is not committed yet. The ? marks are placeholders. The values travel in the tuple after the SQL, so a name cannot change the shape of the statement. UPDATE and DELETE use WHERE code = ? so only that row changes. If this browser has no sqlite3, the playground says so and still prints the kept row, then the empty list.

Key takeaways

• DML changes rows: INSERT, UPDATE, and DELETE

• commit() keeps the change, and rollback() undoes an uncommitted change

• Put values in a tuple beside ?, not inside the SQL text

• WHERE limits UPDATE and DELETE to the rows you name

• DELETE removes rows; DROP removes the table

• Next: Select Queries — read rows with fetchone, fetchmany, and fetchall

• Previous: DDL Queries