Retrieving Data with SELECT

The central path is: SELECT defines the requested data, WHERE reduces the rows, ORDER BY arranges them, the cursor supplies them on demand, and the Python loop processes them.

Programming

Interactive lab

Try it: SELECT, WHERE, ORDER BY, LIMIT

How a SELECT query tests every row against the WHERE condition (keeping only rows where it is TRUE, not FALSE or NULL), then picks columns, removes duplicates with DISTINCT, sorts with ORDER BY and cuts with LIMIT.

How it works

FROM: start with every row of the table.
WHERE: evaluate the condition on each row. Comparisons with NULL give NULL (unknown); NOT, AND and OR follow three-valued logic, with AND binding tighter than OR. Only rows where the whole condition is TRUE are kept.
SELECT picks the listed columns; DISTINCT then drops result rows that repeat an earlier one.
ORDER BY sorts by each key in turn, ASC or DESC; SQLite puts NULL first when ascending and last when descending.
LIMIT n keeps only the first n rows of the sorted result.

Default run (13 steps): SELECT title, plays FROM track WHERE plays >= 95 AND rating >= 4 ORDER BY plays DESC LIMIT 2. FROM: start with all 8 rows of track. … Result: 2 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Ties in ORDER BY are broken by id here (by the selected columns with DISTINCT), and DISTINCT without ORDER BY keeps first-occurrence order; SQLite itself promises no order for ties. LIKE is case-insensitive for ASCII letters, as in SQLite.

Educational simulation

Loading the simulation…

From Stored Rows to Useful Results

A database becomes useful when a program can retrieve exactly the information it needs. SELECT is the SQL command for retrieving rows and columns from a table. Instead of loading an entire table into your program, you can choose particular columns, filter rows with WHERE, and sort the results with ORDER BY. Python receives the result through a cursor and can process each returned row one at a time.

Key point

The central path is: SELECT defines the requested data, WHERE reduces the rows, ORDER BY arranges them, the cursor supplies them on demand, and the Python loop processes them.

The SELECT Pipeline

How does a SELECT query filter rows, choose columns, and sort the resulting data?

A SELECT statement has three essential ideas: the columns to retrieve, the table to retrieve them from, and the rows to include. The basic structure is SELECT columns FROM table. WHERE is optional and restricts the result to rows matching a condition. ORDER BY is also optional and sorts the returned rows by one or more columns, in ascending or descending order.

sql

SELECT title, plays
FROM Track
WHERE plays > 100
ORDER BY plays DESC;

SELECT title, plays
FROM Track
WHERE plays > 100
ORDER BY plays DESC;

In this example, the database considers rows in Track, keeps only rows whose plays value is greater than 100, returns only title and plays, and sorts those results from higher plays to lower plays. The asterisk can be used when every column is needed, as in SELECT * FROM Track WHERE title = 'My Way'. Listing columns is more precise when the program needs only part of each row.

Rows Inside the Cursor

How does each row move from the database cursor into the Python loop, and what happens on each iteration?

Executing SELECT produces a cursor, which is a pointer to the result set. The cursor does not immediately place every matching row into memory. When a Python for loop advances through the cursor, the cursor supplies the next row. This on-demand behavior lets a program begin processing immediately and keeps memory usage low even when a result set is large.

Code walkthrough

cursor = connection.execute(
    "SELECT title, plays FROM Track ORDER BY plays DESC"
)

for row in cursor:
    title, plays = row
    print(title, plays)

1
The execute call runs the SELECT statement and produces a cursor.
2
The for loop asks the cursor for one row at a time.
3
Each row is unpacked into title and plays.
4
The program processes the current row before the loop requests the next one.

Tuple Positions and Selected Columns

How do tuple positions map to the selected database columns in each returned row?

Definition

A returned database row is a Python tuple. Its values are ordered from left to right according to the columns in the SELECT list. If the query selects title and plays, row[0] is the title and row[1] is the plays value.

python

cursor = connection.execute("SELECT title, plays FROM Track")

for row in cursor:
    print(row[0])
    print(row[1])

cursor = connection.execute("SELECT title, plays FROM Track")

for row in cursor:
    print(row[0])
    print(row[1])

The same tuple can be handled with tuple unpacking, as in title, plays = row. Index access is useful when you need one position directly; unpacking makes the intended column names clearer. The important rule is that the positions follow the order of the SELECT statement, not necessarily the physical order of columns in the table.

Safe Values and Saved Changes

Approach	How values are supplied	Result
Parameterized query	Question marks in SQL and a tuple passed separately	SQL command and data remain separate
String concatenation	Values are joined directly into the SQL text	Data can alter the meaning of the command

Parameterized queries are the recommended approach for dynamic data.

python

title = "My Way"
plays = 240

connection.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?)",
    (title, plays)
)
connection.commit()

title = "My Way"
plays = 240

connection.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?)",
    (title, plays)
)
connection.commit()

The first argument to execute is the SQL command with question-mark placeholders. The second argument is a tuple containing the actual values. The database driver matches each question mark with the corresponding tuple value in order. This keeps the command separate from its data and prevents SQL injection, in which crafted data could alter the meaning of the SQL command. Use this pattern for dynamic INSERT, UPDATE, DELETE, or SELECT values.

A Complete Retrieval Check

Worked example

Insert, commit, then retrieve

Add a Track row and verify it with a SELECT query.

1
Insert
Use question-mark placeholders and pass the title and plays values in a tuple.
2
Commit
Call commit() so the inserted row is finalized and written permanently to disk.
3
Select
Retrieve title and plays for rows whose plays value is greater than 100, sorting from highest to lowest.
4
Iterate
Loop through the cursor. Each iteration receives one tuple whose first value is title and second value is plays.

Result

The program verifies the inserted data while processing rows one at a time through the cursor.

python

connection.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?)",
    ("My Way", 240)
)
connection.commit()

cursor = connection.execute(
    "SELECT title, plays FROM Track WHERE plays > ? ORDER BY plays DESC",
    (100,)
)

for title, plays in cursor:
    print(title, plays)

connection.execute(
    "INSERT INTO Track (title, plays) VALUES (?, ?)",
    ("My Way", 240)
)
connection.commit()

cursor = connection.execute(
    "SELECT title, plays FROM Track WHERE plays > ? ORDER BY plays DESC",
    (100,)
)

for title, plays in cursor:
    print(title, plays)

The INSERT uses a parameterized query, and commit() makes the change persistent. The SELECT also uses a parameterized value for the WHERE condition. The cursor then supplies matching rows in descending plays order. If the inserted row meets the condition, it appears in the loop's output. Using SELECT after an INSERT is a practical way to check whether the insertion and commit worked correctly.

Mistakes That Hide Retrieval Problems

Common mistake
Building SQL by concatenating dynamic values
What it looks like
Joining a user-provided value directly into the SQL command text.
Why it fails
Crafted data could change the meaning of the SQL command and create a SQL injection vulnerability.
Do this instead
Use question-mark placeholders and pass the values separately in a tuple.
Common mistake
Forgetting to call commit() after INSERT
What it looks like
Executing an INSERT and immediately closing the connection.
Why it fails
The changes may be lost if the program crashes or the connection closes unexpectedly before the transaction is finalized.
Do this instead
Call commit() after the changes that should be persisted.
Common mistake
Assuming a returned row is a dictionary
What it looks like
Trying to retrieve the title with row["title"].
Why it fails
The source model returns each row as a tuple whose positions follow the SELECT column order.
Do this instead
Use index access such as row[0], or unpack the tuple into variables.
Common mistake
Loading every result before processing it
What it looks like
Using fetchall() for a result set that may contain many rows.
Why it fails
Putting all rows into memory can be inefficient for large results.
Do this instead
Iterate through the cursor directly so rows are fetched on demand.
Common mistake
Sorting retrieved rows in Python when SQL can sort them
What it looks like
Retrieving a large unsorted result and sorting it after the loop.
Why it fails
The database can often sort more efficiently, especially for large result sets.
Do this instead
Use ORDER BY in the SELECT statement.

Practice the Retrieval Path

MEDIUM

Write a Python database operation that selects title and plays from Track where plays is greater than a supplied value, orders the results by plays in descending order, and prints each row by unpacking the cursor result. Use a question-mark placeholder for the supplied value.

Hints

Put title and plays in the SELECT list.
Place the condition in a WHERE clause and the sorting instruction in ORDER BY.
Pass the threshold as a one-value tuple and iterate directly through the cursor.

Your attempt (not saved)

1
SELECT retrieves database data by combining a column list, a table name, and optional WHERE and ORDER BY clauses. Executing SELECT produces a cursor, and a Python for loop obtains rows from that cursor on demand. Every returned row is a tuple whose positions match the order of the selected columns. For dynamic values, parameterized queries keep SQL commands separate from data. When adding rows with INSERT, call commit() to finalize the transaction and persist the changes.

Key Takeaways

Use SELECT to choose columns and retrieve rows from a table.
Use WHERE to filter rows and ORDER BY to sort the result.
A cursor supplies rows on demand, so direct iteration avoids loading the entire result set into memory.
Each returned row is a Python tuple ordered according to the SELECT statement.
Use parameterized queries for dynamic values, and call commit() after INSERT to persist changes.

Related concepts

Database Transactions

More concepts starting with R

Try it: SELECT, WHERE, ORDER BY, LIMIT

From Stored Rows to Useful Results

The SELECT Pipeline

Rows Inside the Cursor

Tuple Positions and Selected Columns

Safe Values and Saved Changes

A Complete Retrieval Check

Mistakes That Hide Retrieval Problems

Practice the Retrieval Path

Key Takeaways

Related Topics

Related concepts