Deleting Data with DELETE
INSERT adds new rows to a table; specify the table, columns, and values.
What the Source Covers
The supplied material connects this topic to database changes, parameterized queries, and commit(). Its concrete command examples describe INSERT rather than the row-selection syntax of DELETE. Therefore, this lesson focuses on the database mechanism that the source fully explains: safely supplying data values, executing a change, and committing that change. The source does establish that the same parameterized-query habit applies to INSERT, UPDATE, DELETE, and SELECT statements when they contain user-provided or dynamic data.
Separate Commands from Values
A parameterized query keeps two things separate. The SQL command contains the operation and question-mark placeholders. The values are supplied separately in a tuple. The database driver matches the first question mark with the first tuple value, the second question mark with the second value, and so on. This separation prevents data from being interpreted as a change to the SQL command itself.
Mapping Placeholders to Values
A query has two question-mark placeholders, and the tuple contains a title value followed by a plays value. How are the values assigned?
First placeholder: The database driver uses the first tuple value for the first question mark.
Second placeholder: The database driver uses the second tuple value for the second question mark.
Execution: The driver passes the command and its separately supplied values to the database.
Values are matched to placeholders in order, while remaining separate from the SQL command.
From Execution to Persistence
Executing a database-changing command is not the same as permanently saving its result. The source explains that an INSERT creates a row and stores it in memory. Calling commit() finalizes the transaction and writes the changes permanently to disk. Without commit(), a program crash or unexpected connection closure can cause the INSERT commands to be lost.
Treat commit() as the step that makes a successful database change durable. After inserting data, use a SELECT query to retrieve the rows and verify that the insertion worked. If the INSERT executed without an error but SELECT returns no rows, the source identifies failure to call commit() as one possible problem before the data was persisted.
Applying the Pattern to DELETE
The source explicitly includes DELETE among the query types that should use question marks and tuples whenever they contain user-provided or dynamic data. The safe pattern is therefore consistent: keep the DELETE command separate from its values, let the database driver match placeholders to tuple values in order, execute the command, and commit the transaction when the change should become persistent.
Mistakes to Avoid
Concatenating dynamic data directly into a SQL command.
The source warns that malicious data could alter the meaning of the SQL command, creating a SQL injection vulnerability.
Fix:
Use question-mark placeholders in the SQL command and pass the actual values separately in a tuple.Treating execution as permanent storage.
The source states that changes can be lost if the program crashes or the connection closes unexpectedly before commit().
Fix:
Call commit() to finalize the transaction and write the changes permanently to disk.Assuming that a successful INSERT needs no verification.
An INSERT can execute without an error while the expected rows are still absent if the change was not committed.
Fix:
Use SELECT to retrieve the rows and check that the insertion and commit() worked correctly.Using a different value-passing habit for DELETE than for other dynamic queries.
The source says the parameterized-query rule applies to INSERT, UPDATE, DELETE, and SELECT queries containing user-provided or dynamic data.
Fix:
Use question marks and tuples consistently for all of these query types.
Practice the Transaction Pattern
Explain the safe sequence for a database-changing query that receives a dynamic value. Your answer should name the SQL command with question-mark placeholders, the tuple of values, the database driver's matching step, execute(), commit(), and a verification step using SELECT.
Hints
- Keep the SQL structure and data values separate.
- The driver matches placeholders and tuple values in order.
- Distinguish a change held in memory from a change written permanently to disk.
Checking a Change
A database-changing command ran without an error, but a later SELECT returns no expected rows. What should you investigate first?
Check the parameter passing: Confirm that the SQL command uses question-mark placeholders and that the tuple supplies the intended values in the matching order.
Check the transaction: Confirm that commit() was called after the command was executed.
Check the result: Run SELECT again to verify whether the expected rows are present.
Investigate the parameter mapping and whether commit() was called before treating the command as successfully persisted.
Key Takeaways
- Parameterized queries keep SQL commands separate from their data values.
- Question-mark placeholders are matched with tuple values in order by the database driver.
- commit() finalizes a transaction and writes database changes permanently to disk.
- The same parameterized-query habit applies to dynamic INSERT, UPDATE, DELETE, and SELECT statements.
- SELECT provides a practical way to verify that inserted rows were committed successfully.
Key Takeaways
- Keep SQL commands separate from dynamic data by using question marks and tuples.
- The database driver matches tuple values to placeholders in order.
- Executing a change and committing it are distinct steps.
- Call commit() to make the transaction persistent on disk.
- Use parameterized queries consistently for dynamic DELETE and other SQL statements.