Concepts / Deleting Data with DELETE

Deleting Data with DELETE

Executing a database-changing command is not the same as permanently saving its result.

  • Programming

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.

SQL commandquestion-mark placeholdersDatabase drivermatches values in orderDatabaseexecutes the commandValues tupleactual data valuescommand structuredata valuesmatched query
How do question-mark placeholders and tuple values connect before the database executes the query?
Worked example

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?

  1. 1
    First placeholder

    The database driver uses the first tuple value for the first question mark.

  2. 2
    Second placeholder

    The database driver uses the second tuple value for the second question mark.

  3. 3
    Execution

    The driver passes the command and its separately supplied values to the database.

Result

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.

Existing databaseCommand executedchange held in memorycommit() calledtransaction finalizedPersistent databasechange written to diskexecute commandcall commit()write permanently
What changes between executing a database command and calling commit(), and when does the change become persistent on disk?
Best practice

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.

Database statebefore DELETEDELETE commandparameterized valuesUpdated stateafter execution, beforecommit()Persistent stateafter commit()executetemporary database changecommit()
What can be shown safely from the source about a DELETE operation: the command is executed as a parameterized database change and becomes persistent after commit(). The source pack does not specify which row-selection condition is used.

Mistakes to Avoid

  • Common mistake

    Concatenating dynamic data directly into a SQL command.

    What it looks like

    Building one SQL string by joining user-provided text to the command.

    Why it fails

    The source warns that malicious data could alter the meaning of the SQL command, creating a SQL injection vulnerability.

    Do this instead

    Use question-mark placeholders in the SQL command and pass the actual values separately in a tuple.

  • Common mistake

    Treating execution as permanent storage.

    What it looks like

    Executing a database-changing command and closing the connection without calling commit().

    Why it fails

    The source states that changes can be lost if the program crashes or the connection closes unexpectedly before commit().

    Do this instead

    Call commit() to finalize the transaction and write the changes permanently to disk.

  • Common mistake

    Assuming that a successful INSERT needs no verification.

    What it looks like

    Never using SELECT after inserting rows.

    Why it fails

    An INSERT can execute without an error while the expected rows are still absent if the change was not committed.

    Do this instead

    Use SELECT to retrieve the rows and check that the insertion and commit() worked correctly.

  • Common mistake

    Using a different value-passing habit for DELETE than for other dynamic queries.

    What it looks like

    Parameterizing INSERT but concatenating values for DELETE.

    Why it fails

    The source says the parameterized-query rule applies to INSERT, UPDATE, DELETE, and SELECT queries containing user-provided or dynamic data.

    Do this instead

    Use question marks and tuples consistently for all of these query types.

Practice the Transaction Pattern

MEDIUM

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.
Worked example

Checking a Change

A database-changing command ran without an error, but a later SELECT returns no expected rows. What should you investigate first?

  1. 1
    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.

  2. 2
    Check the transaction

    Confirm that commit() was called after the command was executed.

  3. 3
    Check the result

    Run SELECT again to verify whether the expected rows are present.

Result

Investigate the parameter mapping and whether commit() was called before treating the command as successfully persisted.

Key Takeaways

  1. 1
    Parameterized queries keep SQL commands separate from their data values.
  2. 2
    Question-mark placeholders are matched with tuple values in order by the database driver.
  3. 3
    commit() finalizes a transaction and writes database changes permanently to disk.
  4. 4
    The same parameterized-query habit applies to dynamic INSERT, UPDATE, DELETE, and SELECT statements.
  5. 5
    SELECT provides a practical way to verify that inserted rows were committed successfully.

At a glance

  • 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.

Related Topics

Related concepts

More concepts starting with D