Updating and Deleting Data: Modifying Existing Records
SELECT retrieves specific columns and rows from a database table using WHERE to filter and ORDER BY to sort results.
From Table to Result Set
A database table may contain many rows and columns, but a query does not have to return everything. A SELECT statement chooses the columns to retrieve, names the table, and can optionally restrict which rows appear. WHERE removes rows that do not match a condition, while ORDER BY controls the order of the rows that remain.
Reading Query Structure
The basic structure of a SELECT statement is SELECT columns FROM table. The columns part identifies the values to retrieve, and the table part identifies where those values come from. An asterisk means all columns. WHERE is optional and filters rows by a condition. ORDER BY is optional and sorts the matching rows in ascending or descending order.
This query retrieves every column from the Track table, but only for rows whose title equals My Way. If you need only particular values, list column names separated by commas instead of using the asterisk. Conditions can use comparison operators such as equals, less than, greater than, less than or equal to, greater than or equal to, and not equal to.
In this generated example, SELECT chooses title and plays, WHERE keeps rows whose title is not My Way, and ORDER BY requests a descending sort by plays. The important sequence is conceptual: choose columns, identify the table, filter rows when needed, and sort the result when needed.
Following Rows Through a Cursor
After a SELECT statement is executed, the result is represented by a cursor. A cursor is a pointer to the result set. It does not immediately place every matching row in memory. Instead, it can fetch rows on demand as your program moves through the result.
for row in cursor: title, plays = row print(title, plays)
Tuples and Column Order
Each row fetched from the cursor arrives as a Python tuple. The tuple is an ordered sequence, and its values appear in the same left-to-right order as the columns in the SELECT statement. If the query selects title and plays, the first tuple element is title and the second is plays.
| SELECT column order | Tuple position | Access method |
|---|---|---|
| title | 0 | row[0] |
| plays | 1 | row[1] |
A row returned from SELECT title, plays follows the same order as the SELECT list.
You can retrieve tuple elements by index, starting at index 0, or unpack the tuple into separate variables. Unpacking is useful when the meaning of each selected column is clear. Index access is useful when you need to refer to a particular position directly.
Why On-Demand Retrieval Matters
On-demand retrieval means the program can begin processing rows without first loading the entire result set. This keeps memory usage low, which is especially important when a query may return thousands or millions of rows. The program processes the current tuple, then the cursor supplies the next row.
Sorting also belongs in the query when possible. ORDER BY lets the database sort the result instead of making the Python program sort the rows after retrieval. This can be more efficient, especially for a large result set.
Mistakes with Queries and Cursors
Selecting every column when only a few are needed
The asterisk retrieves all columns, even when the program needs only particular values.
Fix:
List the required columns, such as SELECT title, plays FROM Track;Expecting WHERE to sort rows
WHERE filters rows but does not specify their order.
Fix:
Use ORDER BY when the result must be sorted.Assuming a fetched row is a single value
A database row arrives as a tuple containing the selected column values.
Fix:
Use tuple unpacking or index access, such as title, plays = row or row[0].Loading all rows without considering result size
Fetching all rows at once places the complete result set in memory.
Fix:
Iterate through the cursor so rows are fetched and processed on demand.
Practice the Retrieval Flow
From Query to Python Variables
Suppose a query selects title and plays from Track, filters rows by a WHERE condition, and sorts the matching rows with ORDER BY. Explain what Python receives during cursor iteration.
Choose columns: Because the SELECT list contains title followed by plays, every returned tuple places the title value first and the plays value second.
Filter rows: The WHERE clause allows only rows that satisfy its condition to enter the result set.
Sort rows: The ORDER BY clause determines the order in which the matching rows appear in the cursor.
Iterate: A Python for loop receives one tuple at a time from the cursor and can unpack each tuple into title and plays.
The program processes only matching rows, in database-defined order, with each row represented as a tuple whose positions follow the SELECT column order.
Describe what changes when SELECT title, plays is changed to SELECT plays, title. Identify which value appears at index 0, and explain why the order of rows may still be controlled separately with ORDER BY.
Hints
- Tuple positions follow the order of columns in SELECT.
- Filtering and column selection are separate from row sorting.
- ORDER BY names the column or columns used to sort the result.
Key Takeaways
- SELECT chooses columns and identifies the table from which rows are retrieved.
- WHERE filters rows, while ORDER BY sorts the rows that remain.
- A cursor represents the result set and supplies rows on demand instead of loading everything at once.
- A Python for loop can process each cursor row as it arrives.
- Each returned row is a tuple whose values follow the SELECT column order.