CODEWITHSUNDEEP
pythonsqlpyodbc
dave
dave

Reputation: 12806

Iterating over PyoDBC result without fetchall()

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?

Upvotes: 15

Views: 24400

Answers (3)

Peter DeGlopper
Peter DeGlopper

Reputation: 37344

Sure - use a while loop with fetchone.

http://code.google.com/p/pyodbc/wiki/Cursor#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.

Upvotes: 19

Daniel
Daniel

Reputation: 131

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)

Upvotes: 13

Brad
Brad

Reputation: 1367

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

http://code.google.com/p/pyodbc/wiki/Cursor#fetchmany

Upvotes: 8

Related Questions