I'm using pyodbc on python 2.6 to connect to Microsoft SQL Server 2005. I open a connection, create a couple of cursors:
c1 = connection.cursor()
c2 = connection.cursor()
and then run a query on the first cursor.
c1.execute("select * from foo")
Now I run a query on the second cursor:
c2.execute("select * from bar")
...and I get an error: "Connection is busy with results for another hstmt."
After I do a c1.fetchall()
or c1.close()
then I can use c2.
My question is: Why am I even allowed to create multiple cursors on a connection, if I'm only allowed to use one at a time, and the same one can always be reused? And, if I want to run a query for each row of the results of another query, like this:
for x in c1.execute(...):
for y in c2.execute(...):
do I really have to create multiple connections to the same database?
You can have the cursors nested but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.
Cursors could be used in some applications for serialized operations as shown in example above, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.
According to this guy
Cursor objects are used to execute SQL statements. ODBC and pyodbc allow multiple cursors per connection, but not all databases support this.
and you can determine concurrent cursors can be supported with:
import pyodbc
connection = pyodbc.connect(...)
how_many = connection.getinfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES)
print(how_many)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With