Inserting Data with INSERT: Adding Rows to a Table

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.

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.

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.

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.

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

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.

for row in cursor:
    title = row[0]
    plays = row[1]
    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.

Avoiding Retrieval Errors

• Selecting columns in one order and unpacking them in another

  Tuple values follow the SELECT order, so the first value is the title and the second is the plays count.

  Fix: Keep the unpacking order aligned with the SELECT list: for title, plays in cursor:

• Assuming a cursor loads the complete result set into memory immediately

  A cursor fetches rows on demand as the program iterates through it.

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

• Sorting a large result in Python instead of using ORDER BY

  The source recommends using ORDER BY in the database, which can often sort more efficiently, especially for large result sets.

  Fix: Put the required sorting rule in the SELECT statement with ORDER BY.

• Using an asterisk when only a few columns are needed

  The asterisk retrieves every column, while a specific column list communicates and retrieves only the needed fields.

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

Practice the Retrieval Path

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.