Retrieving Data with SELECT

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.

The SELECT Pipeline

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.

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

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.

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

Tuple Positions and Selected Columns

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

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

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

• Building SQL by concatenating dynamic values

  Crafted data could change the meaning of the SQL command and create a SQL injection vulnerability.

  Fix: Use question-mark placeholders and pass the values separately in a tuple.

• Forgetting to call commit() after INSERT

  The changes may be lost if the program crashes or the connection closes unexpectedly before the transaction is finalized.

  Fix: Call commit() after the changes that should be persisted.

• Assuming a returned row is a dictionary

  The source model returns each row as a tuple whose positions follow the SELECT column order.

  Fix: Use index access such as row[0], or unpack the tuple into variables.

• Loading every result before processing it

  Putting all rows into memory can be inefficient for large results.

  Fix: Iterate through the cursor directly so rows are fetched on demand.

• Sorting retrieved rows in Python when SQL can sort them

  The database can often sort more efficiently, especially for large result sets.

  Fix: Use ORDER BY in the SELECT statement.

Practice the Retrieval Path

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.