Updating Data with UPDATE
INSERT adds new rows to a table; specify the table, columns, and values.
Adding Data to a Table
This section focuses on adding data to a database table with INSERT. Although the article title mentions UPDATE, the supplied material teaches the INSERT operation: specifying a table, its columns, and the values for a new row. A database must check that the values fit the table structure and must track the change before it is permanently written to disk.
INSERT adds a new row. A complete INSERT operation identifies the table, names the columns receiving data, and supplies the corresponding values.
Tracing a New Row
Suppose a Track table has the columns title and plays. An INSERT command can request a new row containing a title value and a plays value. The database creates that row and holds the change in memory while the transaction is still in progress.
Matching Columns with Values
Add a Track row with the title Example song and a plays value of 12.
Name the table: Use Track as the table that will receive the new row.
Name the columns: Specify title and plays so the database knows where each value belongs.
Provide the values: Pass the title and play count in the same order as the named columns.
The database creates a new Track row containing Example song and 12, initially held in memory until the transaction is committed.
Moving Values into Placeholders
A parameterized query keeps the SQL command separate from the data. The first argument to execute() is the SQL command containing question-mark placeholders. The second argument is a tuple containing the actual values. The database driver matches the first question mark with the first tuple value, the second question mark with the second tuple value, and so on.
cursor.execute( "INSERT INTO Track (title, plays) VALUES (?, ?);", ("Example song", 12) )
Saving the Transaction
Executing INSERT creates the row in the database transaction, but the change is not permanently saved to disk until commit() is called. The commit() method finalizes the transaction and writes the changes permanently to disk.
After committing, verify the result with a SELECT query. The supplied material recommends retrieving the rows you inserted and checking that they are printed. If SELECT returns no rows even though INSERT produced no error, investigate whether commit() was called.
Safer Parameter Handling
| Parameterized query | String concatenation |
|---|---|
| Keeps SQL structure separate from data | Combines command text and data into one SQL string |
| Uses question marks and a tuple | Does not use the parameterized structure |
| Helps prevent SQL injection | Can allow malicious data to alter the meaning of the SQL command |
| Makes code clearer and easier to maintain | Makes the SQL structure and data harder to distinguish |
String concatenation is risky because data can be crafted to change the meaning of the SQL command. Parameterized queries prevent that separation from being lost: the command describes the operation, while the tuple supplies values as data. This also makes the code easier to debug and maintain because the SQL structure is visible at a glance.
Always use parameterized queries for INSERT, UPDATE, DELETE, and SELECT commands that include user-provided or dynamic data. Use question marks in the SQL command and pass the values separately in a tuple.
Mistakes to Avoid
Running INSERT but never calling commit().
Without commit(), the change may be lost if the program crashes or the connection closes unexpectedly.
Fix:
Call connection.commit() after the INSERT operation.Putting dynamic values directly into the SQL command.
A crafted value could alter the meaning of the SQL command and create a SQL injection vulnerability.
Fix:
Use question-mark placeholders and pass the values in a tuple.Passing values in the wrong order.
The database driver matches each question mark to the corresponding tuple value in order.
Fix:
Keep the tuple order aligned with the placeholder order and the named columns.Assuming that a successful INSERT is enough to confirm the data was saved.
The transaction may not have been committed.
Fix:
Call commit() and verify the inserted rows with SELECT.
Practice the Sequence
Write the steps needed to add a row to Track with a title and a plays value. Your solution should use question-mark placeholders, pass the values in a tuple, call commit(), and then describe how you would verify the result.
Hints
- Start with an INSERT command that names Track, title, and plays.
- Use one question mark for each value.
- Pass the title and plays value in the same order as the placeholders.
- Finalize the transaction with commit().
- Use SELECT to retrieve and inspect the inserted rows.
What do you think happens?
A parameterized INSERT contains VALUES (?, ?) and the tuple is ("Example song", 12). Which value fills the second question mark?
Reveal answer
Answer: 12
The database driver matches question marks with tuple values in order. The first tuple item fills the first placeholder, and the second tuple item fills the second placeholder.
Key Takeaways
- INSERT adds a new row by specifying a table, columns, and values.
- Parameterized queries use question marks in the SQL command and a tuple for the corresponding values.
- The driver matches placeholders and tuple values in order.
- commit() finalizes the transaction and writes the change permanently to disk.
- Use parameterized queries for dynamic data because they help prevent SQL injection and keep code clear.
Key Takeaways
- INSERT adds new rows to a table by connecting a table name, column list, and values.
- Question-mark placeholders and tuples keep SQL commands separate from data.
- Tuple values fill placeholders in order.
- commit() makes an inserted change persistent on disk.
- SELECT provides a practical way to verify that the insertion and commit succeeded.