Theory
The program that lost sixty rows
A senior's story, true every year: their Python script inserted a whole class's marks, printed every row back correctly, and finished cleanly. Next morning the table was empty.
No crash. No error. The rows were printed, so they existed... and then they did not.
One missing line explains everything, and it is the line this lesson exists to burn in: conn.commit(). First, the four operations themselves; then the seal that makes them real.
Theory
The pencil register returns
Remember Unit 1's rule: databases write in pencil first, pen on COMMIT.
Python's sqlite3 follows it strictly: every INSERT, UPDATE and DELETE you execute is pencil. Your own program rereads pencil marks fine (that is why the senior's prints looked perfect). But close the register without inking: conn.commit(): and the pencil is erased on the way out. Reads need no ink; changes always do.
Theory
The safe way to pass values: ? placeholders
Real programs insert Python variables, not typed constants. The safe form uses ? placeholders plus a tuple:
cur.execute("INSERT INTO marks VALUES (?, ?, ?)", (roll, subject, score))
The module quotes and escapes each value itself.
Never glue SQL together with + or f-strings: a name like O'Brien shatters the quoting, and a malicious input can rewrite your query entirely (SQL injection). One value still needs a tuple: (roll,) with the comma.
Practical
Full CRUD, sealed properly
import sqlite3
conn = sqlite3.connect('college.db')
cur = conn.cursor()
# CREATE a row (INSERT)
cur.execute("INSERT INTO marks VALUES (?, ?, ?)", (104, 'DBMS', 67))
# bulk insert: executemany over a list of tuples
new_rows = [(105, 'DBMS', 72), (106, 'DBMS', 58)]
cur.executemany("INSERT INTO marks VALUES (?, ?, ?)", new_rows)
# UPDATE (the recheck, from Python this time)
cur.execute("UPDATE marks SET score = ? WHERE roll = ? AND subject = ?",
(88, 101, 'DBMS'))
print(cur.rowcount) # 1 row affected
# DELETE a withdrawn student's row
cur.execute("DELETE FROM marks WHERE roll = ?", (106,)) # note (106,)
# READ needs no commit: SELECT + fetch as last lesson
cur.execute("SELECT COUNT(*) FROM marks WHERE subject = 'DBMS'")
print(cur.fetchone()[0])
conn.commit() # THE SEAL: changes become permanent NOW
conn.close()
Think first
Diagnose the senior's bug
The senior's script: connect, cursor, executemany 60 INSERTs, SELECT them back (all 60 print), conn.close(). No commit anywhere. Before tapping: WHY did the prints show 60 rows, and why was the table empty next morning?
Show the answer
The 60 INSERTs were pencil marks inside an open transaction. The same connection reads its own pencil, so the SELECT saw all 60 and printed them: the program looked perfect from inside.
conn.close() without commit rolled the transaction back: pencil erased, table unchanged. The morning-after check used a fresh connection, which sees only ink.
One line before close(): conn.commit(): and the story never happens.
Quiz
Which statement about commit() is TRUE in Python's sqlite3?
- INSERT/UPDATE/DELETE need conn.commit() to persist; SELECT does not
- Every statement including SELECT requires commit()
- commit() is automatic when the program ends successfully
- commit() is called on the cursor: cur.commit()
Show the answer
INSERT/UPDATE/DELETE need conn.commit() to persist; SELECT does not
Reads change nothing, so SELECT never needs a commit; every data CHANGE is provisional until conn.commit() inks it. Option C is the lie that eats data: normal program exit does NOT auto-commit (the senior's bug). And commit lives on the connection (the phone line owns the transaction), not the cursor: cur.commit() is an AttributeError. Two facts, both worth marks: who needs commit, and who owns it.
Watch out
Three habits that lose data or marks
String-built SQL: "...WHERE name = '" + name + "'" breaks on O'Brien and opens SQL injection: placeholders always.
The one-element tuple: execute(sql, (roll)) passes a bare number and fails: write (roll,) with the comma.
DELETE without WHERE: DELETE FROM marks empties the whole table, silently and legally. Write the WHERE first, then the DELETE, and check cur.rowcount after.
Theory
The unit, complete
Skeleton (connect, cursor, execute, close), fetching (one, all), and now writing with the seal: you can build ResultDesk's whole database layer. The Unit 1 promise is also kept: conn.rollback() is your Python-side eraser, and BEGIN/COMMIT semantics you learned in SQL are exactly what sqlite3 automates. Unit 4 hands the same college.db to pandas, where a whole table becomes one Python object.
Summary
Key takeaways
- All CRUD flows through cur.execute(); SELECT pairs with fetch, changes pair with commit.
- Pass values via ? placeholders and a tuple: never string-concatenate SQL (injection, quote bugs).
- One value = one-element tuple with the comma: (roll,).
- executemany() bulk-runs one statement over a list of tuples; cur.rowcount counts affected rows.
- conn.commit() makes changes permanent; close() without commit rolls them back: the classic lost-data bug.
- commit()/rollback() belong to the connection, not the cursor.
- Memory hook: pencil until commit inks it.