python - cx_Oracle: मैं परिणाम सेट पर पुनरावृति कैसे करूं?

original title: "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?



परिणाम सेट पर पुनरावृति करने के कई तरीके हैं। प्रत्येक के ट्रेडऑफ क्या हैं?

यह अनुवाद के बाद का सारांश है, अगर आपको पूरा अनुवाद देखने की आवश्यकता है, तो कृपया 'अनुवाद' आइकन पर क्लिक करें


सभी उत्तर
  • 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?