python - cx_Oracle: How do I iterate over a result set?


Translate

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


Alle Antworten
  • Translate

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

  • Translate

    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


  • Translate

    There's also the way psyco-pg seems to do it... From what I gather, it seems to create dictionary-like row-proxies to map key lookup into the memory block returned by the query. In that case, fetching the whole answer and working with a similar proxy-factory over the rows seems like useful idea. Come to think of it though, it feels more like Lua than Python.

    Also, this should be applicable to all PEP-249 DBAPI2.0 interfaces, not just Oracle, or did you mean just fastest using Oracle?