Defining Database Tables with CREATE TABLE
INSERT adds rows to a database table; commit() must be called to permanently save changes to the database file.
One Table, Four Operations
A database table changes through a sequence of operations. INSERT adds rows, SELECT reads data, and DELETE removes rows that match a condition. The source program uses these operations with a music database and a Track table: it inserts two tracks, reads their title and play count, and then deletes the rows it created so the program can be run again without accumulating duplicate data.
The Table Structure
CREATE TABLE establishes a table that later operations can use. In the source program, the table is named Track. The stored records include a title and a play count, because SELECT later requests the title and plays columns. The important distinction is that creating or having a table establishes where rows belong, while INSERT, SELECT, and DELETE work with the rows in that table.
The table name and column names must line up across operations. The source SELECT reads title and plays from Track, so those are the pieces of each returned row that the program can inspect.
Adding Rows and Saving Them
INSERT adds rows to a table. In the source program, the first two executable operations insert Thunderstruck with 20 plays and My Way with 15 plays. Each INSERT uses question-mark placeholders, and the actual values are supplied separately as a tuple to execute(). This parameterized form should be used instead of building a SQL statement through string concatenation, because parameterized queries help prevent SQL injection vulnerabilities.
INSERT first places the new rows in the transaction buffer. Calling commit() is the step that permanently saves those changes to the database file. If the program crashes before commit() is called, the inserted rows are lost rather than being written permanently to the file.
Saving Two Music Tracks
Trace the source program after it inserts Thunderstruck with 20 plays and My Way with 15 plays.
Insert the first row: INSERT adds the Thunderstruck row to the transaction buffer.
Insert the second row: INSERT adds the My Way row to the transaction buffer.
Commit the transaction: commit() permanently saves both inserted rows to the database file.
The database file permanently contains the two inserted Track rows after commit() completes.
Reading Results Through a Cursor
SELECT retrieves data from a table. The source SELECT requests the title and plays columns from Track, rather than describing the result as an undifferentiated collection of all table data. A cursor then supplies the returned rows during iteration. Each returned row is a tuple containing two values: the track title and the play count. For example, the source explains that a row can appear as ('Thunderstruck', 20).
The cursor does not immediately load every matching row into memory when SELECT executes. Instead, it reads data on demand as the program loops through the results. The cursor fetches rows one at a time, or in small batches, as iteration continues. This is memory-efficient, especially when a table contains millions of rows, because the entire result set does not have to be loaded at once.
What do you think happens?
After SELECT requests title and plays from Track, how does the cursor provide the two matching rows?
Reveal answer
Answer: It provides the rows during iteration, on demand
The cursor reads rows as the loop progresses rather than loading the entire result set into memory at once.
Filtering Deletions Safely
DELETE removes rows from a table. Its WHERE clause identifies the rows that should be removed. In the source program, the condition is plays less than 100. Thunderstruck has 20 plays and My Way has 15, so both rows match and are deleted.
Deletion also needs commit() when the removal must be permanent. Before that commit(), the rows are removed from the transaction buffer, but the database file still contains them. The source program commits after DELETE so the rows it created are permanently removed and the program can be run repeatedly without accumulating duplicates.
Trace the Complete Sequence
From Insert to Cleanup
Trace the source music-database program as it creates, reads, and removes two Track rows.
Start with the Track table: The program works with the Track table and its title and plays data.
Run both INSERT operations: Thunderstruck with 20 plays and My Way with 15 plays are placed in the transaction buffer.
Call commit(): The two inserted rows are permanently saved to the database file.
Run SELECT: The program requests title and plays from Track and iterates through the returned tuples with a cursor.
Run DELETE with plays less than 100: Both source rows match because 20 and 15 are each less than 100.
Call commit() again: The deletion is permanently written to the database file.
Both source tracks are deleted, leaving the table without those rows so the program can be run again without accumulating duplicate copies.
| Stage | Table or transaction effect | Persistent file effect |
|---|---|---|
| INSERT | Two rows enter the transaction buffer | Not permanent yet |
| First commit() | Inserted rows remain available | Rows are saved to the database file |
| SELECT and cursor iteration | Rows are read on demand | No table contents are changed |
| DELETE with plays less than 100 | Both source rows are removed from the transaction buffer | Removal is not permanent yet |
| Second commit() | Deleted rows remain removed | Removal is saved to the database file |
The source program's state changes in sequence
Mistakes to Catch Early
Assuming INSERT is permanent without calling commit().
The rows have not been written permanently to the database file and can be lost.
Fix:
Call commit() after the INSERT operations when the changes must persist.Treating cursor iteration as if every result row is loaded immediately.
The cursor reads rows on demand during iteration rather than loading all rows at once.
Fix:
Think of the cursor as providing one row at a time, or in small batches, as the loop progresses.Running DELETE without a WHERE clause.
Without a WHERE condition, the operation can accidentally delete all data.
Fix:
Use a WHERE clause that identifies the intended rows.Forgetting to commit a deletion.
The database file can still contain the rows.
Fix:
Call commit() after DELETE when the removal must be permanently saved.Building SQL through string concatenation instead of parameterized queries.
String concatenation can create SQL injection vulnerabilities.
Fix:
Use question-mark placeholders and pass the actual values separately to execute().
When diagnosing an unexpected database state, check the operation order, whether commit() was called after INSERT or DELETE, which columns SELECT requested, whether the cursor was iterated, and whether the DELETE condition matched the rows you expected.
Check Your Understanding
Trace this situation in words: the Track table contains Thunderstruck with 20 plays and My Way with 15 plays. SELECT requests title and plays, the cursor iterates through the results, and DELETE uses the condition plays less than 20. Which source row matches the condition, which row remains, and what additional operation is needed to make the deletion permanent in the database file?
Hints
- Compare each play count with 20.
- A WHERE condition selects only rows that satisfy it.
- DELETE changes the transaction state first.
- commit() is needed to persist the deletion to the database file.
Practice Answer
Determine the result of DELETE with the condition plays less than 20 for Thunderstruck with 20 plays and My Way with 15 plays.
Test Thunderstruck: Its play count is 20, which does not satisfy the condition of being less than 20.
Test My Way: Its play count is 15, which satisfies the condition of being less than 20.
Apply the deletion: DELETE removes My Way and leaves Thunderstruck in the transaction state.
Persist the result: commit() permanently saves the deletion to the database file.
My Way is removed, Thunderstruck remains, and commit() is required to make that removal permanent.
Key Takeaways
- INSERT adds rows to a table, while commit() makes inserted changes permanent in the database file.
- SELECT can request particular columns, such as title and plays, from the Track table.
- A cursor returns rows during iteration and reads them on demand rather than loading the entire result set at once.
- DELETE should use a WHERE clause to identify the rows that should be removed.
- Deletion also requires commit() when the removal must be permanently written to the database file.
- Parameterized queries with question-mark placeholders should be used instead of string concatenation.
Key Takeaways
- INSERT places new rows into a table, and commit() permanently saves them to the database file.
- SELECT chooses columns and lets a cursor provide result rows during iteration.
- Cursors read data on demand, which avoids loading an entire large result set into memory at once.
- DELETE uses WHERE to select rows for removal, and commit() persists the deletion.
- Parameterized queries with question-mark placeholders are safer than string concatenation.