What are the tradeoffs of reusing a cursor vs. creating a new cursor?
cx-oracle, database, oracle, python, sql
Solution
You can re-use a cx_Oracle cursor as much as you like, no problem. If you're executing thousands of small queries in a small space of time, you might see a slight performance improvement by re-using the cursor, but I doubt it.
I will sometimes create new cursors, and other times re-use an existing one, depending on whether it makes the code easier to read and understand.
For example, if I have a variety of procedures that need to access the database, I might pass an Oracle connection object around, or a cursor created on that connection.
Whatever makes your code more readable and easier to maintain is what I'd go for.
Problem
In cx_Oracle (or Oracle in general), is it possible to allocate a cursor for each query, or to reuse a cursor across several queries. ``` def getSomeData(curs): # case 1: pass in a cursor, which is generally curs.execute('select ...') # reused across queries return curs.fetchall() def getSomeData(conn): # case 2: pass in a connection,allocate curs=conn.cursor() # a cursor for this query curs.execute('select ...') return curs.fetchall() ``` Of course, both approaches return the same data. What are the tradeoffs between the two approaches? Is one particularly more or less efficient? Are there any potential pitfalls to reusing a cursor over many queries?