0 votes
1 view
in Python by (47.8k points)

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

1 Answer

0 votes
by (107k points)
  • 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.