Concepts / Querying Data with SELECT and WHERE Clauses

Querying Data with SELECT and WHERE Clauses

Think of the sequence as create, save, read, and remove. INSERT creates rows, commit() saves the transaction to the database file, SELECT reads data, and DELETE removes matching rows.

  • Programming
Interactive lab

Try it: INSERT, UPDATE and DELETE

How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.

How it works

  1. The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
  2. INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
  3. UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
  4. UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
  5. cur.rowcount is the number of rows inserted, changed or removed.
  6. The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.

Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.

Educational simulation

Loading the simulation…

From New Rows to Cleanup

A database operation is not complete merely because a command has run. In the music-database example, the program inserts two tracks, commits those changes, reads the tracks with SELECT, and then deletes the rows it created. Following that sequence reveals three important ideas: changes need to be committed, SELECT can retrieve only the columns you need, and WHERE controls which rows are affected.

Key point

Think of the sequence as create, save, read, and remove. INSERT creates rows, commit() saves the transaction to the database file, SELECT reads data, and DELETE removes matching rows.

Saving Inserted Rows

INSERT adds rows to a database table. In the source program, two tracks are inserted into the Track table. The INSERT operations use parameterized queries: question-mark placeholders represent values, and the actual values are supplied separately to execute(). This separates the query structure from the values being inserted.

ProgramDatabase connectionDatabase fileINSERT rowscommit()save changes
What happens between inserting rows and saving them permanently in the database file?
Worked example

Inserting and Committing Two Tracks

Trace what happens when the program inserts Thunderstruck with 20 plays and My Way with 15 plays.

  1. 1
    Insert

    The program executes two INSERT statements for the Track table. Each statement uses question-mark placeholders, with the track values supplied separately.

  2. 2
    Transaction buffer

    After INSERT runs, the new rows exist in the transaction buffer. They have not yet been permanently written to the database file.

  3. 3
    Commit

    The program calls commit(). This forces the inserted rows to be permanently saved to the database file.

  4. 4
    Failure before commit

    If the program crashes before commit() is called, the inserted rows are lost.

Result

After commit(), the two inserted tracks are permanently saved in the database file.

Selecting Columns with a Cursor

SELECT retrieves data from a table. The source program selects the title and plays columns from the Track table, so each returned row contains those two values. The cursor then supplies rows during iteration. It does not immediately load the entire result set into memory; instead, it reads data on demand as the loop proceeds.

SELECTtitle, playsCursoron-demand readingThunderstruck20My Way15Loop endresult rowsread nextread nextno more rows
How does a cursor retrieve result rows one at a time without loading the entire result set into memory?
In the real world

For the source program, the cursor returns each row as a tuple of two values. A printed row can appear as ('Thunderstruck', 20), where the first value is the track title and the second is the play count. The next row contains the title My Way and the play count 15.

Filtering Results and Deletions

A SELECT operation identifies the columns to retrieve and the table to read. A WHERE clause identifies rows that match a condition. The same filtering idea is used by DELETE: DELETE removes only rows that satisfy its WHERE condition. Without a WHERE clause, DELETE can remove all data, so the condition should be chosen deliberately.

Track tableall table dataWHERE conditionmatching rowstitle, playschosen columnsResult rowscursor iterationinspect rowskeep matchesreturn data
How do SELECT choose specific columns while WHERE filters which rows appear in the result?
Worked example

Deleting Tracks Below the Play Limit

Determine which rows are removed when DELETE uses the condition plays < 100.

  1. 1
    Check Thunderstruck

    Thunderstruck has 20 plays. Since 20 is less than 100, this row matches the WHERE condition.

  2. 2
    Check My Way

    My Way has 15 plays. Since 15 is less than 100, this row also matches the WHERE condition.

  3. 3
    Delete matches

    DELETE removes both matching rows.

  4. 4
    Commit deletion

    The program calls commit() after DELETE so the removals are permanently written to the database file.

Result

Both source-program tracks are deleted, leaving the table empty so the program can be run again without accumulating duplicate data.

Thunderstruck20 playsMy Way15 playsplays < 100matching conditionEmpty tableafter DELETE20 < 10015 < 100delete matches
How does a WHERE condition determine which table rows are removed and which remain?

Safe Query Habits

Best practice

Use parameterized queries with question-mark placeholders instead of building SQL through string concatenation. The source identifies parameterized queries as the safer approach because they help prevent SQL injection vulnerabilities.

  • Common mistake

    Assuming INSERT permanently saves a row without commit().

    What it looks like

    The program inserts rows and then stops before commit() runs.

    Why it fails

    The rows remain in the transaction buffer and are not permanently written to the database file. A crash before commit() causes the inserted rows to be lost.

    Do this instead

    Call commit() after the INSERT operations when the changes should be permanently saved.

  • Common mistake

    Assuming SELECT loads every result row into memory immediately.

    What it looks like

    Treating the cursor as though it already contains the entire result set.

    Why it fails

    The cursor reads rows on demand during iteration rather than loading all rows at once.

    Do this instead

    Understand the loop as incremental cursor reading: the next row is obtained as iteration proceeds.

  • Common mistake

    Running DELETE without a WHERE clause.

    What it looks like

    Deleting from the table without identifying a condition.

    Why it fails

    The operation can remove all data from the table.

    Do this instead

    Use a WHERE clause that identifies the rows intended for removal, then call commit() to persist the deletion.

  • Common mistake

    Using string concatenation instead of parameterized queries.

    What it looks like

    Combining input values directly into a query string.

    Why it fails

    The source identifies this practice as a risk because it can lead to SQL injection vulnerabilities.

    Do this instead

    Use question-mark placeholders and pass the actual values separately to execute().

Trace the Operations

MEDIUM

Trace the source program in order. Explain what happens to the Track table after the two INSERT operations, after commit(), after SELECT iterates through the rows, after DELETE uses plays < 100, and after the final commit(). Then explain why the cursor approach is more memory-efficient than loading every result row at once.

Hints
  • Track the two rows separately: Thunderstruck has 20 plays and My Way has 15.
  • Distinguish the transaction buffer from the permanently saved database file.
  • For DELETE, test the WHERE condition against each row.
  • Remember that the cursor reads during iteration.

Key Takeaways

  1. 1
    INSERT adds rows, but commit() is required to permanently save those changes to the database file.
  2. 2
    SELECT can retrieve specific columns, such as title and plays, from the Track table.
  3. 3
    A cursor reads result rows on demand during iteration instead of loading the entire result set into memory at once.
  4. 4
    DELETE removes rows that match its WHERE condition, and a WHERE clause is essential to avoid deleting all data.
  5. 5
    Parameterized queries with question-mark placeholders should be used instead of string concatenation.

At a glance

  • INSERT creates rows, while commit() makes the changes permanent in the database file.
  • SELECT chooses the columns to retrieve, and a cursor supplies result rows incrementally during iteration.
  • WHERE identifies the rows that match a condition and therefore controls which rows DELETE removes.
  • Parameterized queries are safer than string concatenation, and DELETE should always include a deliberate WHERE clause.

Related Topics

Related concepts

More concepts starting with Q