Python second loop over results is empty: generators and iterators are one-shot

Finance noticed it first: the nightly invoice export landed in the shared bucket on time, the job summary in Slack said exported 18,412 invoices, and the CSV had a header row and nothing else. It had been like this for three nights. No exceptions, no failed tasks, exit code 0.

2026-07-08T01:00:04Z INFO export_invoices: fetched batch for 2026-07-07
2026-07-08T01:00:09Z INFO export_invoices: total_amount=1904551.20 currency=EUR rows=18412
2026-07-08T01:00:09Z INFO export_invoices: wrote /tmp/invoices-2026-07-07.csv (1 lines)

The row count and the total were correct. The file was not.

Reproducing it

Running the job locally against a staging snapshot reproduced it every time. The interesting part was that removing the summary log line made the CSV correct again. Logging was changing the output.

What we ruled out

• An empty query. The total and count were computed from the same rows, so the data was there.
• A CSV writer bug or buffering. We called fh.flush() and checked the file size before upload; the writer genuinely received zero rows.
• The upload step overwriting the file. The local file was already header-only before upload.

Root cause

Three nights earlier a refactor had made the repository function lazy to save memory. It used to return a list:

def fetch_invoices(day):
    rows = db.execute(QUERY, {"day": day}).mappings().all()
    return [Invoice.from_row(r) for r in rows]

and now it returned a generator expression:

def fetch_invoices(day):
    rows = db.execute(QUERY, {"day": day}).mappings()
    return (Invoice.from_row(r) for r in rows)

The caller iterated twice: once to compute the summary, once to write the file.

invoices = fetch_invoices(day)
total = sum(inv.amount for inv in invoices)
count = sum(1 for _ in invoices)  # always 0: sum() above drained it
log.info("total_amount=%s currency=EUR rows=%s", total, len(seen_ids))
write_csv(path, invoices)

The sum drained the generator. Everything after it saw an exhausted iterator and looped zero times. The count in the log came from a separate set of IDs collected inside Invoice.from_row for deduplication, which is why it looked right and hid the problem.

What is happening under the hood

Python separates two ideas:

• An iterable is anything with __iter__ that can hand out a fresh iterator: list, tuple, dict, set, range, str, and dict views like d.items().
• An iterator is the object that produces values, with __next__. Its __iter__ returns itself. Once __next__ raises StopIteration, it stays exhausted forever.

A for loop calls iter() on whatever it is given. For a list that creates a new cursor each time, so you can loop as often as you like. For an iterator, iter() returns the same object, already at the end, so the loop body never runs. No error is raised, because "no more items" is exactly what StopIteration means.

Things that are iterators, and therefore one-shot:

Partial consumption is just as quiet. any(), next(), in, and a for with break all advance the iterator, so a later loop starts in the middle.

Environment

Minimal reproduction

def squares(n):
    return (i * i for i in range(n))

values = squares(5)
print(sum(values))   # 30
print(list(values))  # []

pairs = map(str.upper, ["a", "b"])
print("A" in pairs)  # True, and it consumed "A"
print(list(pairs))   # ['B']

Who is affected

Any code that receives a sequence-looking value from somewhere else and loops over it more than once: computing a summary then writing details, validating then processing, logging a preview with next(), or checking if not items: (a generator object is always truthy, so this check never fires). The bug usually appears during a "make it lazy" refactor where the producer changes and the consumers do not.

The fix

We had three options, and picked based on the data size.

1. Materialise once at the boundary. The export is about 20,000 rows per night. A list is a few megabytes. The simplest correct fix:

 invoices = fetch_invoices(day)
+invoices = list(invoices)
 total = sum(inv.amount for inv in invoices)
-count = sum(1 for _ in invoices)
+count = len(invoices)
 log.info("total_amount=%s currency=EUR rows=%s", total, count)
 write_csv(path, invoices)

len() also gives a real count now, instead of a side channel.

2. Single pass for large data. For the yearly export, which is about 4 million rows, we kept the generator and computed the summary while writing:

def write_csv_with_summary(path, invoices):
    total = Decimal("0")
    count = 0
    with open(path, "w", newline="", encoding="utf-8") as fh:
        writer = csv.writer(fh)
        writer.writerow(HEADER)
        for inv in invoices:
            writer.writerow(inv.as_row())
            total += inv.amount
            count += 1
    return count, total

One pass, constant memory, and the numbers in the log describe exactly what was written.

3. A re-iterable object when callers genuinely need several passes. If the data should be lazy but re-readable, return an object whose __iter__ starts a new query each time:

class InvoiceQuery:
    def __init__(self, day):
        self.day = day

    def __iter__(self):
        rows = db.execute(QUERY, {"day": self.day}).mappings()
        for r in rows:
            yield Invoice.from_row(r)

Each for calls __iter__, which is a generator function, so every loop gets a fresh generator. The cost is one query per pass, which is explicit in the code.

Alternatives and trade-offs

• itertools.tee(it, 2) gives two independent iterators over one source. It buffers everything one copy has seen and the other has not. If you fully consume one before starting the other, it stores the whole sequence, so it is a list with extra steps. It is useful only when the two consumers advance roughly together.
• Returning list from the repository again. Reverting would fix this caller but bring back the memory issue for the yearly job. We preferred to make the caller explicit.
• Sequence vs Iterable contracts. The real fix for the team was saying in the signature what the consumer needs, below.

How to test the fix

A unit test that would have failed before the fix, using a generator on purpose:

def test_export_writes_all_rows(tmp_path):
    invoices = (make_invoice(amount=Decimal("10.00")) for _ in range(3))
    path = tmp_path / "out.csv"

    count, total = export_invoices(path, invoices)

    lines = path.read_text().splitlines()
    assert count == 3
    assert total == Decimal("30.00")
    assert len(lines) == 4  # header + 3 rows

Passing a generator in tests, rather than a list, is the important part: lists hide this class of bug.

pytest tests/test_export.py -q
mypy --strict src/exports/

Confirming in production

After the deploy we checked that the row count in the log matched the file and the database:

aws s3 cp s3://reports-example/invoices-2026-07-11.csv - | wc -l

18977

That is 18,976 rows plus the header, matching both rows=18976 in the job log and SELECT count(*) for that day. We re-ran the three missing nights from the Airflow UI and finance reconciled them the same morning.

Regression guard

We annotated what each function needs. A consumer that loops twice now declares Sequence, and mypy rejects a generator:

from collections.abc import Iterator, Sequence

def fetch_invoices(day: date) -> Iterator[Invoice]: ...

def export_invoices(path: Path, invoices: Sequence[Invoice]) -> tuple[int, Decimal]: ...

error: Argument 2 to "export_invoices" has incompatible type "Iterator[Invoice]"; expected "Sequence[Invoice]"  [arg-type]

mypy in strict mode is now a required CI check for the exports package, and the review checklist asks: "does this function iterate its argument more than once?"

Security implications

None beyond data integrity. The bug produced silently incomplete financial exports, which is an audit concern rather than a security one: a downstream reconciliation could have marked invoices as unpaid. The fix keeps the data within the same process and bucket; option 1 holds more data in memory, which we sized against the worker limits.

Prevention checklist

• Type parameters as Sequence when you need len() or more than one pass, Iterable when one pass is enough.
• When you change a function from returning a list to yielding, grep its callers for double iteration.
• Feed generators, not lists, into tests for code that accepts iterables.
• Do not use if not items: on something that may be an iterator; materialise or count while iterating.
• Log counts computed from the data you actually wrote.

References

• Python glossary, iterator and iterable: https://docs.python.org/3/glossary.html
• Python tutorial, generators: https://docs.python.org/3/tutorial/classes.html
• itertools.tee: https://docs.python.org/3/library/itertools.html
• collections.abc (Iterable, Iterator, Sequence): https://docs.python.org/3/library/collections.abc.html
• mypy documentation: https://mypy.readthedocs.io/en/stable/