Fetch All Rows from One Result

Run begins with four available catalog rows:

1 | IT-100 | Cordless drill   | tools       | 7
2 | IT-200 | Folding table    | events      | 3
4 | IT-400 | Projector screen | electronics | 2
5 | IT-500 | Repair kit       | tools       | 5

The query already finds them. Now look at the two Python objects involved in getting them back.

Your workspace has three visible files. schema.sql recreates the items, members, and loans tables you already know; leave it unchanged. You will build the reusable functions in catalog_db.py. When you press Run, run_boundary.py creates temporary sample data, makes the exact call shown here, and prints what your function actually returned. It does not save a database file in your project.

Open catalog_db.py. The current function returns the value from execute() directly:

def list_available_items(connection):
    return connection.execute("""
        SELECT items.id, items.asset_tag, items.name, items.category, items.loan_days
        FROM items
        LEFT JOIN loans
            ON loans.item_id = items.id AND loans.returned_on IS NULL
        WHERE loans.id IS NULL
        ORDER BY items.asset_tag
    """)

That value is a Cursor: SQLite’s result object for this execution. It can produce rows, but it is not itself the list of rows. Name it first, then ask it for all remaining rows:

result = connection.execute("""complete query here""")
rows = result.fetchall()
return result, rows

The pair is temporary. Returning both objects lets you see the distinction before you simplify the function’s public result. Do not run fetchone() first: that would consume one row, so fetchall() would receive only the remainder.

Press Run. It calls list_available_items(connection) once. Your output will identify the outer tuple, the Cursor, the separate list length, and the first tuple’s five values. You should also see the four rows in asset-tag order.

Submit checks different catalog values and insertion order, including an active loan, returned history, and an item never loaned. It calls the function twice through the same supplied connection. Return the actual result and fetched rows; leave the connection open for its caller.