Creating and Connecting to a Database
INSERT adds rows to a database table; commit() must be called to permanently save changes to the database file.
From Program to Table
A database program moves information through a small sequence of connected parts: the application sends a SQL command through a database connection, a cursor executes that command, and the database table is changed or read. In this lesson, the sequence uses a music database and a Track table. The program inserts two tracks, reads them, and then deletes them so the same program can be run again without accumulating duplicate rows.
Adding Rows Before Saving
INSERT adds rows to a database table. The source program inserts two tracks into the Track table. Each INSERT uses a parameterized query: question-mark placeholders appear in the SQL statement, and the actual values are supplied separately as a tuple to execute(). This keeps the SQL structure separate from the data being inserted.
Making Changes Permanent
Calling commit() after INSERT is essential. The inserted rows exist in the transaction buffer until commit() is called; they are not yet written permanently to the database file. commit() forces the database to save those changes. If the program crashes before commit(), the inserted rows are lost.
cursor.execute("INSERT INTO Track (title, plays) VALUES (?, ?)", ("Thunderstruck", 20))
connection.commit()- 1The first line sends an INSERT command through the cursor.
- 2The second line commits the transaction so the inserted row is saved.
- INSERT creates a pending row using separate parameter values.
commit()permanently saves the pending change to the database file.
Selecting Only What You Need
SELECT retrieves data from a table. The source program selects the title and plays columns from Track, rather than describing every column in the table. After the query executes, the cursor provides the matching rows for iteration. Each returned row is a tuple containing the selected values, so a row in this example contains two values: the track title and its play count.
('Thunderstruck', 20)
('My Way', 15)Reading Results on Demand
A cursor does not immediately load every row returned by SELECT into memory. It reads rows on demand as the loop advances. In the source explanation, the cursor fetches one row at a time, or in small batches, as the for loop iterates. This matters especially for a table containing millions of rows: loading every row at once would consume enormous amounts of memory, while on-demand reading limits how much result data must be held at one time.
What do you think happens?
A SELECT query matches many rows. Does the cursor have to load all of them into memory before the for loop can begin?
Reveal answer
No, rows are read on demand during iteration.
The cursor supplies rows as the loop advances, reading one row at a time or in small batches rather than loading the entire result set at once.
Deleting Matching Rows
DELETE removes rows from a table. The WHERE clause determines which rows match the deletion condition. In the music example, WHERE plays < 100 matches both Thunderstruck with 20 plays and My Way with 15 plays, so both rows are removed. A WHERE clause is important because it limits the deletion to rows satisfying the condition and helps prevent accidentally deleting all data.
Safe Query Parameters
Use parameterized queries with question-mark placeholders instead of building SQL by concatenating strings. In the source program, the SQL statement contains placeholders and the actual values are passed as a tuple to execute(). This practice should always be used to help prevent SQL injection vulnerabilities.
| Operation | Purpose | Important detail |
|---|---|---|
| INSERT | Adds rows to a table | Call commit() to save the changes permanently |
| SELECT | Retrieves data from a table | A cursor reads rows during iteration |
| DELETE | Removes rows from a table | Use a WHERE clause to choose the rows |
The three database operations used in the source program
Common Database Mistakes
Common mistake Assuming INSERT alone permanently saves a row.
What it looks likeRows are inserted, but the program ends or crashes before connection.commit() is called.
Why it failsThe rows remain only in the transaction buffer and are not permanently written to the database file.
Do this insteadCall commit() after the INSERT operations.Common mistake Reading a SELECT result as though every row is loaded immediately.
What it looks likeTreating the cursor as one large in-memory collection before iteration begins.
Why it failsThe cursor reads rows on demand as the loop advances.
Do this insteadUse cursor iteration and understand that rows are fetched one at a time or in small batches.
Common mistake Deleting without a WHERE clause.
What it looks likeUsing a DELETE operation without specifying which rows match.
Why it failsThe operation can accidentally delete all data from the table.
Do this insteadAdd a WHERE condition that identifies the intended rows.
Common mistake Combining user or program values into SQL through string concatenation.
What it looks likeConstructing an SQL command by joining values directly into the SQL text.
Why it failsString concatenation can create SQL injection vulnerabilities.
Do this insteadUse question-mark placeholders and pass the values separately to execute().
Common mistake Forgetting to commit a DELETE operation.
What it looks likeRows appear removed during the transaction, but DELETE is not followed by commit().
Why it failsThe deletion is not permanently written to the database file.
Do this insteadCall commit() after DELETE.
Trace the Full Cycle
Insert, read, and remove two tracks
Trace what happens when a program inserts Thunderstruck and My Way, selects their titles and play counts, and then deletes rows where plays is less than 100.
- 1Insert
Two INSERT statements add Thunderstruck with 20 plays and My Way with 15 plays to the Track table.
- 2Commit
commit()permanently saves both inserted rows to the database file. - 3Select
SELECT title, plays FROM Track asks for the title and play count columns. The cursor visits the returned rows during the for loop.
- 4Delete
DELETE with WHERE plays < 100 matches both tracks because 20 and 15 are both less than 100.
- 5Commit again
A second
commit()permanently saves the deletions, leaving the table empty of the rows created by this program.
The program creates two rows, saves them, reads them as two-value tuples, deletes both matching rows, and saves those deletions so the program can be run again without accumulating duplicate data.
A Track table contains a row with the title Blue Moon and 120 plays. Would DELETE with WHERE plays < 100 remove this row? Explain what the WHERE condition checks before deletion.
Hints
- Compare the row's play count with 100.
- Only rows satisfying the WHERE condition are selected for deletion.
Practice the Sequence
Put these actions in the correct order for the source program: read rows with a cursor, insert two tracks, commit the inserts, delete rows matching a condition, and commit the deletions.
Hints
- Rows must exist before SELECT can retrieve them.
- The source program commits after INSERT and again after DELETE.
- The cursor iterates through the SELECT result.
- Identify which SQL operation changes the table and which operation reads it.
- For every INSERT or DELETE, ask whether
commit()follows it. - For every DELETE, identify the WHERE condition and the rows it matches.
- For every SELECT, identify the requested columns and explain how the cursor supplies rows during iteration.
Key Takeaways
- INSERT adds rows, but
commit()is required to permanently save those changes to the database file. - SELECT can request particular columns, and a cursor provides the resulting rows during iteration.
- Cursors read query results on demand, one row at a time or in small batches, rather than loading the entire result set at once.
- DELETE should always include a WHERE clause so only intended rows are removed.
- Parameterized queries with question-mark placeholders should be used instead of string concatenation to help prevent SQL injection vulnerabilities.