Concepts / Connecting to a Database and Creating Tables

Connecting to a Database and Creating Tables

SELECT retrieves specific columns and rows from a database table using WHERE to filter and ORDER BY to sort results.

  • Programming

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.

What do you think happens?

A query selects title and plays, filters rows, and sorts by plays. Which operation determines the order of the columns in each returned row?

  • The WHERE clause

  • The ORDER BY clause

  • The order of the columns in SELECT

  • The Python for loop

Reveal answer
Answer:

The order of the columns in SELECT

Each returned row is a tuple whose elements appear in the same left-to-right order as the selected columns.

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.

Track tableSelected columnstitle, playsMatching rowsWHERE conditionSorted rowsORDER BYCursor resulttupleschoosekeep matching rowsorderprovide
How does a database transform a table into a result by choosing columns, filtering rows, and sorting them?
sql

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 resultordered rowsRow 1tupleRow 2tupleNext rowcursor advancesEnd of resultfirst iterationnext iterationrepeatno rows remain
What happens to the cursor and the next row each time the Python loop repeats?
Code walkthrough
cursor.execute("SELECT title, plays FROM Track ORDER BY plays")
for row in cursor:
    title, plays = row
    print(title, plays)
  1. 1
    The execute call sends the SELECT statement through the database connection and produces a cursor result.
  2. 2
    The for loop advances through the cursor one row at a time.
  3. 3
    Each row is a tuple, so tuple unpacking assigns its first element to title and its second element to plays.
  4. 4
    The loop body processes the current row before the cursor supplies the next one.
  • Execute a SELECT query that requests title and plays and sorts by plays.
  • Ask the cursor for each row in sequence.
  • Unpack the current tuple into title and plays.
  • Process the values from the current row.

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

Definition

A returned database row is a Python tuple: an ordered, immutable sequence of values. Its elements are arranged from left to right in the same order as the columns in SELECT.

titleSELECT position 1row[0]title valueplaysSELECT position 2row[1]plays valuemaps tomaps to
How do positions in a returned tuple correspond to the selected database columns?
Worked example

Matching SELECT Order to Tuple Access

A query selects title and plays. How can the loop read those two values?

  1. 1
    Read the SELECT order

    title is selected first and plays is selected second.

  2. 2
    Use index access

    The first tuple element is row[0], which corresponds to title. The second is row[1], which corresponds to plays.

  3. 3
    Use unpacking

    The same tuple can be unpacked as title, plays = row.

Result

The selected column order controls both the tuple order and the variables assigned during unpacking.

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.

Best practice

Let the database sort with ORDER BY instead of retrieving unsorted results and sorting them in Python. The source material notes that the database can often sort more efficiently, especially when the result set is large.

Common Query Mistakes

  • Common mistake

    Selecting every column when only a few are needed

    What it looks like

    SELECT * FROM Track

    Why it fails

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

    Do this instead

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

  • Common mistake

    Treating WHERE as a sorting clause

    What it looks like

    WHERE plays > 10

    Why it fails

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

    Do this instead

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

  • Common mistake

    Assuming a cursor loads the entire result immediately

    What it looks like

    Expecting every matching row to already be stored in memory after execution

    Why it fails

    A cursor retrieves rows on demand as the program iterates.

    Do this instead

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

  • Common mistake

    Reading tuple positions without checking SELECT order

    What it looks like

    Using row[0] as plays after selecting plays, title

    Why it fails

    Tuple elements follow the order of the selected columns.

    Do this instead

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

Practice the Trace

MEDIUM

Write a SELECT statement for the Track table that retrieves title and plays, keeps only rows where plays is greater than 10, and sorts the result by plays. Then describe what the Python for loop receives on each iteration.

Hints
  • Begin with SELECT and list the two required columns.
  • Use FROM Track to identify the table.
  • Add WHERE with a greater-than condition.
  • Finish with ORDER BY plays.
  • Each iteration receives one tuple whose first element corresponds to title and whose second element corresponds to plays.
  1. 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.

Key Takeaways

  • SELECT identifies the columns and table for a query.
  • WHERE filters rows, while ORDER BY sorts the rows that remain.
  • A cursor provides query results on demand instead of loading all rows at once.
  • A Python for loop can process the cursor one row at a time.
  • Each returned row is a tuple whose positions match the SELECT column order.

Related Topics

Related concepts

More concepts starting with C