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?

10/14/2016 6:15:27 PM

Accepted Answer

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.

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()]

This can be useful for smaller result sets, but can have 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.

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()
10/15/2016 8:47:11 PM

My preferred way is the cursor iterator, but setting first the arraysize property of the cursor.

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

In this example, cx_Oracle will fetch rows from Oracle 256 rows at a time, reducing the number of network round trips that need to be performed

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow