Python - SQLite SELECT query usable only once?

Question

I have an sqlite query that looks like this:

db_rows = db_cursor.execute(
    "SELECT * FROM my_table"
)

# check1
for row in db_rows:
    print(row)

# check2
for row in db_rows:
    print(row)

What happens is that the first for loop outputs all the rows, and the second does not output anythng at all which I find strange. Why is that? Is there a way for me to re-use db_rows multiple times? Perhaps this is a bug?

I need this because I have some nested code (for's and if's) which is supposed to re-use db_rows and believe it should be faster to just re-use db_rows that should be already in memory instead of executing the query again and again for each itteration of an inner loop.

Thank you.

Ignacio Vazquez-Abrams · Accepted Answer · 2011-07-28 08:43:12Z

4

... it should be faster to just re-use db_rows that should be already in memory ...

Except that it's not in memory; it's being pulled from the database one row at a time. Convert to a list if you want to pull the results into memory.

row_list = list(db_rows)

for row in row_list:
   ...

answered Jul 28, 2011 at 8:43

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Keith · Accepted Answer · 2011-07-28 08:42:46Z

3

The returned object is an iterator. The first loop consumes the iterator. The second one gets it already exhausted. If you want to retain the whole result, do this:

rows = list(db_rows)

answered Jul 28, 2011 at 8:42

Keith

43.2k11 gold badges62 silver badges77 bronze badges

Collectives™ on Stack Overflow

Python - SQLite SELECT query usable only once?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related