Iterating over PyoDBC result without fetchall()

Question

I'm trying to process a very large query with pyodbc and I need to iterate over the rows without loading them all at once with fetchall().

Is there a good and principled way to do this?

I think you can also do `for row in cursor:` – BenDundee Mar 19 '14 at 16:15 — BenDundee, Mar 19 '14 at 16:15

Peter DeGlopper · Accepted Answer · 2023-06-17T02:30:25.693

19

Sure - use a while loop with fetchone.

row = cursor.fetchone()
while row is not None:
    # do something
    row = cursor.fetchone()

edit In fact, doing it using the cursor directly as an iterator as shown in https://stackoverflow.com/a/59738011/2337736 is more idiomatic.

edited Jun 17 '23 at 02:30

answered Jul 17 '13 at 18:36

Peter DeGlopper

1

I would prefer to not repeat the fetchone line. You can do while True: row = cursor.fetchOne(); if row is None: break; # do something – JoelFan May 26 '17 at 03:09
Use `for row in cursor:` loop, as advised by @Daniel – Sergey Nudnov Jun 10 '20 at 21:06

score 12 · Answer 2 · answered Jan 14 '20 at 16:30

According to official documentation the cursor is apparently an iterator. Therefore, you shouldn't need to create a custom iterator/generator.

If you are going to process the rows one at a time, you can use the cursor itself as an iterator:

cursor.execute("select user_id, user_name from users"):
for row in cursor:
    print(row.user_id, row.user_name)

score 8 · Answer 3 · answered Jul 17 '13 at 18:56

8

you could also use cursor.fetchmany() if you want to batch up the fetches (defaults to 1 if you don't override it)

answered Jul 17 '13 at 18:56

Brad

3 Answers3