Querying Data with SELECT and WHERE Clauses
INSERT adds rows to a database table; commit() must be called to permanently save changes to the database file.
From New Rows to Cleanup
A database operation is not complete merely because a command has run. In the music-database example, the program inserts two tracks, commits those changes, reads the tracks with SELECT, and then deletes the rows it created. Following that sequence reveals three important ideas: changes need to be committed, SELECT can retrieve only the columns you need, and WHERE controls which rows are affected.
Think of the sequence as create, save, read, and remove. INSERT creates rows, commit() saves the transaction to the database file, SELECT reads data, and DELETE removes matching rows.
Saving Inserted Rows
INSERT adds rows to a database table. In the source program, two tracks are inserted into the Track table. The INSERT operations use parameterized queries: question-mark placeholders represent values, and the actual values are supplied separately to execute(). This separates the query structure from the values being inserted.
Inserting and Committing Two Tracks
Trace what happens when the program inserts Thunderstruck with 20 plays and My Way with 15 plays.
Insert: The program executes two INSERT statements for the Track table. Each statement uses question-mark placeholders, with the track values supplied separately.
Transaction buffer: After INSERT runs, the new rows exist in the transaction buffer. They have not yet been permanently written to the database file.
Commit: The program calls commit(). This forces the inserted rows to be permanently saved to the database file.
Failure before commit: If the program crashes before commit() is called, the inserted rows are lost.
After commit(), the two inserted tracks are permanently saved in the database file.
Selecting Columns with a Cursor
SELECT retrieves data from a table. The source program selects the title and plays columns from the Track table, so each returned row contains those two values. The cursor then supplies rows during iteration. It does not immediately load the entire result set into memory; instead, it reads data on demand as the loop proceeds.
For the source program, the cursor returns each row as a tuple of two values. A printed row can appear as ('Thunderstruck', 20), where the first value is the track title and the second is the play count. The next row contains the title My Way and the play count 15.
Filtering Results and Deletions
A SELECT operation identifies the columns to retrieve and the table to read. A WHERE clause identifies rows that match a condition. The same filtering idea is used by DELETE: DELETE removes only rows that satisfy its WHERE condition. Without a WHERE clause, DELETE can remove all data, so the condition should be chosen deliberately.
Deleting Tracks Below the Play Limit
Determine which rows are removed when DELETE uses the condition plays < 100.
Check Thunderstruck: Thunderstruck has 20 plays. Since 20 is less than 100, this row matches the WHERE condition.
Check My Way: My Way has 15 plays. Since 15 is less than 100, this row also matches the WHERE condition.
Delete matches: DELETE removes both matching rows.
Commit deletion: The program calls commit() after DELETE so the removals are permanently written to the database file.
Both source-program tracks are deleted, leaving the table empty so the program can be run again without accumulating duplicate data.
Safe Query Habits
Use parameterized queries with question-mark placeholders instead of building SQL through string concatenation. The source identifies parameterized queries as the safer approach because they help prevent SQL injection vulnerabilities.
Assuming INSERT permanently saves a row without commit().
The rows remain in the transaction buffer and are not permanently written to the database file. A crash before commit() causes the inserted rows to be lost.
Fix:
Call commit() after the INSERT operations when the changes should be permanently saved.Assuming SELECT loads every result row into memory immediately.
The cursor reads rows on demand during iteration rather than loading all rows at once.
Fix:
Understand the loop as incremental cursor reading: the next row is obtained as iteration proceeds.Running DELETE without a WHERE clause.
The operation can remove all data from the table.
Fix:
Use a WHERE clause that identifies the rows intended for removal, then call commit() to persist the deletion.Using string concatenation instead of parameterized queries.
The source identifies this practice as a risk because it can lead to SQL injection vulnerabilities.
Fix:
Use question-mark placeholders and pass the actual values separately to execute().
Trace the Operations
Trace the source program in order. Explain what happens to the Track table after the two INSERT operations, after commit(), after SELECT iterates through the rows, after DELETE uses plays < 100, and after the final commit(). Then explain why the cursor approach is more memory-efficient than loading every result row at once.
Hints
- Track the two rows separately: Thunderstruck has 20 plays and My Way has 15.
- Distinguish the transaction buffer from the permanently saved database file.
- For DELETE, test the WHERE condition against each row.
- Remember that the cursor reads during iteration.
Key Takeaways
- INSERT adds rows, but commit() is required to permanently save those changes to the database file.
- SELECT can retrieve specific columns, such as title and plays, from the Track table.
- A cursor reads result rows on demand during iteration instead of loading the entire result set into memory at once.
- DELETE removes rows that match its WHERE condition, and a WHERE clause is essential to avoid deleting all data.
- Parameterized queries with question-mark placeholders should be used instead of string concatenation.
Key Takeaways
- INSERT creates rows, while commit() makes the changes permanent in the database file.
- SELECT chooses the columns to retrieve, and a cursor supplies result rows incrementally during iteration.
- WHERE identifies the rows that match a condition and therefore controls which rows DELETE removes.
- Parameterized queries are safer than string concatenation, and DELETE should always include a deliberate WHERE clause.