Mark Harrison Mark Harrison - 11 months ago 51
SQL Question

cx_Oracle: How do I iterate over a result set?

There are several ways to iterate over a result set. What are the tradeoff of each?

Answer Source

There are several ways to iterate over a result set.

The canonical way is to use the built-in cursor iterator.

curs.execute('select * from people')
for row in curs:
    print row

You can use fetchall() to get all rows at once. This can be useful for smaller result sets, but can have some bad side effects if the result set is large.

  • You have to wait for the entire result set to be returned to your client process.

  • You may eat up a lot of memory in your client to hold the built-up list.

  • It may take a while for Python to construct and deconstruct the list which you are going to immediately discard anyways.

    for row in curs.fetchall(): print row

It can be convenient to use this to create a Python list containing the values returned:

curs.execute('select first_name from people')
names = [row[0] for row in curs.fetchall()]

If you know there's a single row being returned in the result set you can call fetchone() to get the single row.

curs.execute('select max(x) from t')
maxValue = curs.fetchone()[0]

Finally, you can loop over the result set fetching one row at a time. In general, there's no particular advantage in doing this over using the iterator.

row = curs.fetchone()
while row:
    print row
    row = curs.fetchone()