Creating a Table with CREATE TABLE
INSERT adds new rows to a table; specify the table, columns, and values.
From Empty Table to Stored Data
Once a table exists, the next step is to populate it with actual data. An INSERT command adds a new row, but inserting data involves more than placing values in a file. The database must match the supplied values to the table structure and track the change so it can be safely written to disk. The reliable pattern is to specify the table and columns, pass the values separately with parameters, and call commit() when the change should become persistent.
INSERT adds a new row to a table. The command identifies the table and columns, while the supplied values fill the corresponding positions.
Mapping Columns to a New Row
An INSERT describes three connected parts: the table receiving the row, the columns that will receive values, and the values themselves. For a Track table with title and plays columns, an INSERT can provide one title value and one plays value. The database uses the specified column order to place each value in the new row.
Keeping SQL Separate from Data
A parameterized query keeps the SQL command separate from the data values. The first argument passed 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 (?, ?)", ("Morning Walk", 12) )
From Execution to Persistence
Executing an INSERT creates the row in the database's current transaction state. The inserted row is stored in memory and is ready to be written to disk. Calling commit() finalizes the transaction and writes the changes permanently to disk. Without commit(), an unexpected crash or connection closure can cause the INSERT commands to be lost.
Treat commit() as the point that makes the inserted data persistent. After committing, use a SELECT query to retrieve the rows and verify that the insertion worked correctly.
Why Concatenation Is Risky
| Parameterized query | String concatenation |
|---|---|
| SQL structure contains question-mark placeholders | SQL structure is combined with data to form one text command |
| Values are passed separately in a tuple | Data is placed directly into the SQL text |
| Keeps SQL commands separate from data | Can allow crafted data to alter the meaning of the command |
| Helps prevent SQL injection and improves code clarity | Does not provide the same protection against SQL injection |
Mistakes Beginners Make
Putting values directly into SQL text instead of using placeholders.
Crafted data can alter the meaning of the SQL command, creating a SQL injection risk.
Fix:
Use question marks in the SQL command and pass the values separately in a tuple.Passing tuple values in the wrong order.
The database matches values to placeholders in order, so each value can be associated with the wrong column.
Fix:
Make the tuple order match the question-mark and column order.Executing INSERT without calling commit().
If the program crashes or the connection closes unexpectedly, the INSERT commands can be lost.
Fix:
Call commit() after the INSERT when the change should be finalized and written permanently to disk.Assuming that successful execution alone proves that the data was persisted.
The row may exist in the current transaction state but not yet be permanently written.
Fix:
Call commit(), then use SELECT to verify that the inserted row is present.
Practice the Complete Pattern
Insert and Save a Track
Add a track titled Evening Study with 7 plays to the Track table using a parameterized INSERT, then finalize the change.
Write the SQL structure: Name the Track table and the title and plays columns. Use one question mark for each value.
Prepare the tuple: Place Evening Study first and 7 second because the columns and placeholders are ordered title first and plays second.
Execute the command: Pass the SQL command as the first execute() argument and the tuple as the second argument.
Commit the transaction: Call commit() so the inserted row is finalized and the change is written permanently to disk.
Verify the result: Use a SELECT query to retrieve rows from Track and check that the inserted row appears.
The Track table receives a row with title Evening Study and plays 7, and the change is persisted after commit().
Write the parameterized execute() call and the commit() call needed to add a track titled Coding Time with 15 plays to the Track table. Then identify which tuple value maps to each question mark.
Hints
- Use the column order title, plays.
- The tuple should contain the title first and the play count second.
- Call commit() after execute().
Reliable Insert Routine
- Use INSERT to add a new row by specifying the table, columns, and values.
- Use question-mark placeholders in the SQL command and pass actual values in a tuple.
- Tuple values match placeholders in order, so the tuple order must match the column order.
- Call commit() to finalize the transaction and persist the inserted data to disk.
- Use SELECT after committing to verify that the expected rows were inserted.
Key Takeaways
- INSERT adds new rows to an existing table.
- Parameterized queries separate SQL structure from data by using question marks and tuples.
- The database driver matches tuple values to placeholders in order.
- commit() finalizes the transaction and writes changes permanently to disk.
- SELECT provides a practical way to verify that inserted rows were saved.