cx_Oracle:How do I iterate over a result set?

Question

There are many different ways to iterate over a result set. What is the tradeoff of each? How will we do this?

Answer 1

• The best way is to use the built-in cursor iterator.
• To handle a result set inside a stored procedure, you use a cursor. A cursor allows you to iterate a set of rows returned by a query and process each row accordingly.

curs.execute('select * from people') 

for row incurs: 

    print row

• You can also use the fetchall() method to fetch data from the result set and to get all the rows at once.

for row in curs.fetchall(): 

         print row

• It is convenient to use the fetchall() method to create a Python list contains  the values returned:

curs.execute('select first_name from people') 

names = [row[0] for row in curs.fetchall()]

• This can be also useful for smaller result sets but can have bad side effects if the result set is large.
• You will have to wait for the entire result set to be returned.
• It may eat up a lot of memory to hold the built-up list.
• It may take a while for Python to construct and deconstruct the list which you are going to discard anyway.