Concepts / Creating a Table with CREATE TABLE

Creating a Table with CREATE TABLE

INSERT adds a new row to a table. The command identifies the table and columns, while the supplied values fill the corresponding positions.

  • Programming

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.

Key point

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.

sql
Tracktabletitle, playscolumnsvalue pairtitle value, plays valuenew rowtitle and playsreceivesdefine positionsfill positions
How do the selected table, columns, and supplied values combine to create one 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.

Code walkthrough
cursor.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?)",
    ("Morning Walk", 12)
)
  1. 1
    The INSERT command targets the Track table.
  2. 2
    The columns are title and plays.
  3. 3
    The tuple value Morning Walk is supplied for title.
  4. 4
    The tuple value 12 is supplied for plays.
  • The SQL text names the table and columns and contains two placeholders.
  • The tuple supplies two values in the same order as the placeholders.
  • The first tuple value is matched to the first question mark; the second is matched to the second.
?titleMorning Walktuple position 1titleMorning Walk?plays12tuple position 2plays12position 1position 2fillsfills
Which tuple value is inserted into each question-mark placeholder and corresponding table column?

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.

python
INSERT commandnew rowin memorycommit()new rowon diskexecutesawaitswrites permanently
What happens to a newly inserted row before and after commit(), and when is it persisted to disk?
Best practice

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 queryString concatenation
SQL structure contains question-mark placeholdersSQL structure is combined with data to form one text command
Values are passed separately in a tupleData is placed directly into the SQL text
Keeps SQL commands separate from dataCan allow crafted data to alter the meaning of the command
Helps prevent SQL injection and improves code clarityDoes not provide the same protection against SQL injection

Parameterized queries separate command structure from values.

SQL with ?command structurecombined SQL textcommand plus datatuple valuesdatadatabase driverone SQL text inputdatabase driverseparate inputscommandvaluescombined text
How does data move differently through a parameterized query compared with SQL text built by string concatenation, and why is one safer?

Mistakes Beginners Make

  • Common mistake

    Putting values directly into SQL text instead of using placeholders.

    What it looks like

    Building an INSERT command by combining a value with the SQL string.

    Why it fails

    Crafted data can alter the meaning of the SQL command, creating a SQL injection risk.

    Do this instead

    Use question marks in the SQL command and pass the values separately in a tuple.

  • Common mistake

    Passing tuple values in the wrong order.

    What it looks like

    Using (12, "Morning Walk") for the columns (title, plays).

    Why it fails

    The database matches values to placeholders in order, so each value can be associated with the wrong column.

    Do this instead

    Make the tuple order match the question-mark and column order.

  • Common mistake

    Executing INSERT without calling commit().

    What it looks like

    Running execute() and then closing the connection without commit().

    Why it fails

    If the program crashes or the connection closes unexpectedly, the INSERT commands can be lost.

    Do this instead

    Call commit() after the INSERT when the change should be finalized and written permanently to disk.

  • Common mistake

    Assuming that successful execution alone proves that the data was persisted.

    What it looks like

    Not checking the table after the INSERT.

    Why it fails

    The row may exist in the current transaction state but not yet be permanently written.

    Do this instead

    Call commit(), then use SELECT to verify that the inserted row is present.

Practice the Complete Pattern

Worked example

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.

  1. 1
    Write the SQL structure

    Name the Track table and the title and plays columns. Use one question mark for each value.

  2. 2
    Prepare the tuple

    Place Evening Study first and 7 second because the columns and placeholders are ordered title first and plays second.

  3. 3
    Execute the command

    Pass the SQL command as the first execute() argument and the tuple as the second argument.

  4. 4
    Commit the transaction

    Call commit() so the inserted row is finalized and the change is written permanently to disk.

  5. 5
    Verify the result

    Use a SELECT query to retrieve rows from Track and check that the inserted row appears.

Result

The Track table receives a row with title Evening Study and plays 7, and the change is persisted after commit().

EASY

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

  1. 1
    Use INSERT to add a new row by specifying the table, columns, and values.
  2. 2
    Use question-mark placeholders in the SQL command and pass actual values in a tuple.
  3. 3
    Tuple values match placeholders in order, so the tuple order must match the column order.
  4. 4
    Call commit() to finalize the transaction and persist the inserted data to disk.
  5. 5
    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.

Related Topics

Related concepts

More concepts starting with C