Inserting Data with INSERT: Adding Rows to a Table

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

Programming

Reading the Result

Although this article title mentions INSERT, the supplied lesson material focuses on what happens when a program retrieves table data with SELECT. The important path is from a table, through a filtered and sorted query, into a cursor, and finally into Python one row at a time.

What high-level table change does the article title describe, and which part of the supplied lesson is actually explained in detail?

Shaping a SELECT Query

A SELECT statement has three main parts: the columns to retrieve, the table to retrieve them from, and, optionally, the rows to include. Its basic structure is SELECT columns FROM table. WHERE can then limit the rows by applying a condition, while ORDER BY can arrange the matching rows 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 generated example, title and plays specify the selected columns. Track specifies the table. WHERE keeps only rows whose plays value is greater than 100. ORDER BY plays DESC places the remaining rows from highest plays count to lowest. The database performs the filtering and sorting as part of the query rather than requiring Python to do those tasks afterward.

How do selected columns, a WHERE condition, and an ORDER BY rule transform a table into the result set?

sql

SELECT *
FROM Track
WHERE title = 'My Way';

SELECT *
FROM Track
WHERE title = 'My Way';

The asterisk means all columns. The source example retrieves every column from Track, but only for rows whose title equals My Way. Listing column names instead of using the asterisk is more specific when the program needs only certain values.

Following the Cursor

Executing a SELECT does not immediately place every matching row into Python memory. The database prepares a cursor, which acts as a pointer to the result set. When Python iterates through that cursor, the cursor fetches rows on demand, one at a time.

Code walkthrough

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

for row in cursor:
    print(row)

1
The first line executes a SELECT and obtains a cursor rather than loading every matching row into Python at once.
2
The for statement begins iterating over the cursor.
3
On each iteration, the cursor supplies one row as a tuple.
4
The print statement processes the current tuple before the loop requests the next row.

The SELECT execution produces a cursor for the result set.
The for loop asks the cursor for the next row on each iteration.
row refers to the tuple delivered for the current iteration.

Output

(First song, 120)
(Second song, 85)

How does the cursor move through the result set, and when does the loop receive each row?

How does data move from the database to Python only as the cursor retrieves it?

Interpreting Python Tuples

Each row fetched from the cursor arrives as a Python tuple. Its values are ordered from left to right in the same order as the columns in the SELECT statement. If the query selects title, plays, then the first tuple element is title and the second is plays.

python

for row in cursor:
    title = row[0]
    plays = row[1]
    print(title, plays)

for row in cursor:
    title = row[0]
    plays = row[1]
    print(title, plays)

python

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

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

The two Python examples extract the same two selected values in different ways. Index access uses positions beginning at 0, so row[0] is the first selected column. Tuple unpacking assigns the tuple positions directly to variables. Both approaches depend on keeping the Python names in the same order as the SELECT columns.

What does one database row look like in Python, and how do tuple positions correspond to selected columns?

Avoiding Retrieval Errors

Common mistake
Selecting columns in one order and unpacking them in another
What it looks like
```
SELECT title, plays FROM Track, followed by for plays, title in cursor:
```
Why it fails
Tuple values follow the SELECT order, so the first value is the title and the second is the plays count.
Do this instead
Keep the unpacking order aligned with the SELECT list: for title, plays in cursor:
Common mistake
Assuming a cursor loads the complete result set into memory immediately
What it looks like
Treating a cursor as if it were already a list of every matching row
Why it fails
A cursor fetches rows on demand as the program iterates through it.
Do this instead
Use a Python for loop to process rows one at a time.
Common mistake
Sorting a large result in Python instead of using ORDER BY
What it looks like
Retrieving unsorted rows and planning to sort all of them in application code
Why it fails
The source recommends using ORDER BY in the database, which can often sort more efficiently, especially for large result sets.
Do this instead
Put the required sorting rule in the SELECT statement with ORDER BY.
Common mistake
Using an asterisk when only a few columns are needed
What it looks like
SELECT * FROM Track when the program needs only title and plays
Why it fails
The asterisk retrieves every column, while a specific column list communicates and retrieves only the needed fields.
Do this instead
List the required columns explicitly, such as SELECT title, plays FROM Track.

Practice the Retrieval Path

MEDIUM

Suppose a query selects title and plays from Track, filters with WHERE plays > 100, and sorts with ORDER BY plays DESC. Describe what each part contributes, then write the Python loop that unpacks each returned row into title and plays.

Hints

The SELECT list determines the tuple order.
The WHERE condition determines which rows remain.
DESC places larger values before smaller values.
Tuple unpacking can use for title, plays in cursor:

Your attempt (not saved)

Worked example

Tracing one query

Explain the result of SELECT title, plays FROM Track WHERE plays > 100 ORDER BY plays DESC;

1
Choose columns
Each returned tuple contains title first and plays second because that is the order in the SELECT list.
2
Filter rows
The WHERE condition keeps only rows whose plays value is greater than 100.
3
Sort rows
ORDER BY plays DESC arranges the matching rows from the greatest plays value to the smallest.
4
Process rows
A Python for loop receives one tuple at a time from the cursor and can unpack it as for title, plays in cursor:

Result

The program processes only matching rows, in descending plays order, with each row represented as a two-value Python tuple.

Key Takeaways

1
SELECT specifies the columns and table used to retrieve data.
2
WHERE filters rows, and ORDER BY sorts the matching result.
3
A cursor represents the result set and supplies rows on demand.
4
A Python for loop receives one database row per iteration.
5
Each row is a tuple whose positions follow the SELECT column order.
6
Tuple unpacking and index access both allow Python to extract column values.

At a glance

Use SELECT to choose columns and retrieve rows from a table.
Use WHERE for filtering and ORDER BY for database-side sorting.
Iterate over the cursor with a Python for loop instead of assuming all rows are already in memory.
Treat each returned row as a tuple ordered according to the SELECT statement.
Use tuple unpacking or zero-based index access to read individual values.

More concepts starting with I

Reading the Result

Shaping a SELECT Query

Following the Cursor

Interpreting Python Tuples

Avoiding Retrieval Errors

Practice the Retrieval Path

Key Takeaways

At a glance

Related Topics