Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I use multiple cursors on one connection with pyodbc and MS SQL Server?

Tags:

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?

like image 222
Josh Avatar asked Apr 06 '11 22:04

Josh


People also ask

Can you have two cursors in SQL?

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.

Why should we avoid cursor in SQL Server?

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.


1 Answers

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)
like image 141
Ivan Talalaev Avatar answered Sep 24 '22 21:09

Ivan Talalaev