Connecting to a Database and Creating Tables

From Table to Result

A SELECT query does more than ask for data. It describes the shape of the result: which columns to return, which table to read, which rows to keep, and how to order those rows. After Python executes the query, the database provides a cursor that lets the program process the result one row at a time.

Building a SELECT Pipeline

A SELECT statement has three main parts: the columns to retrieve, the table to retrieve them from, and, optionally, the rows to include. The basic structure is SELECT columns FROM table. WHERE adds a condition so that only matching rows are returned. ORDER BY sorts the matching results by one or more columns, in ascending or descending order.

SELECT title, plays
FROM Track
WHERE plays > 10
ORDER BY plays;

In this generated example, SELECT requests title and plays rather than every column. WHERE keeps rows whose plays value is greater than 10. ORDER BY asks the database to sort the remaining rows by plays. The exact rows depend on the contents of the Track table; the important point is how each clause changes the requested result.

Walking Through the Cursor

After a SELECT statement is executed in Python, the result is represented by a cursor. A cursor is a pointer to the result set. It does not immediately load every matching row into memory. When a Python for loop iterates through the cursor, the cursor provides the next row for each iteration.

cursor.execute("SELECT title, plays FROM Track ORDER BY plays") for row in cursor: title, plays = row print(title, plays)

The loop does not need a separate instruction to request every row at once. Iteration over the cursor repeatedly obtains the next row until the result has been processed. This lets the program begin working before the entire result set has been placed in memory.

Reading Tuple Rows

Efficient Retrieval Choices

On-demand retrieval is the default behavior of database cursors and is generally the efficient choice. The program processes one row at a time, so memory usage stays low even when a query returns many rows. It can also begin processing results immediately rather than waiting for the entire result set.

Fetching every row at once with cursor.fetchall() can be appropriate when the result set is known to be small and all rows are needed in memory simultaneously. For large or uncertain result sets, iterating through the cursor preserves the on-demand behavior.

Common Query Mistakes

• Selecting every column when only a few are needed

  The asterisk requests all columns, even when the program needs only selected values.

  Fix: List the required columns, such as SELECT title, plays FROM Track.

• Treating WHERE as a sorting clause

  WHERE filters rows by a condition; it does not determine their order.

  Fix: Use ORDER BY to sort the rows that remain after filtering.

• Assuming a cursor loads the entire result immediately

  A cursor retrieves rows on demand as the program iterates.

  Fix: Use a Python for loop to process rows one at a time.

• Reading tuple positions without checking SELECT order

  Tuple elements follow the order of the selected columns.

  Fix: Match row indexes or unpacked variables to the exact SELECT order.

Practice the Trace

1. A SELECT statement chooses columns and a table, while WHERE optionally filters rows and ORDER BY optionally sorts them. Executing the query produces a cursor rather than requiring all rows to be loaded at once. A Python for loop advances through that cursor one row at a time. Every row arrives as a tuple, and its positions follow the order of the columns in SELECT.