26

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?

Josh
  • 2,039
  • 3
  • 20
  • 25

3 Answers3

25

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)
Ivan Talalaev
  • 6,014
  • 9
  • 40
  • 49
-2

This appears to be supported via multithreading: http://technet.microsoft.com/en-US/library/ms131700(v=sql.90).aspx

steamer25
  • 9,278
  • 1
  • 33
  • 38
  • 1
    I don't think I even need that. One thread could make multiple connections, each with its own cursor. Certainly, two threads could each have their own connection. Or are you suggesting that two threads could share a single connection object, but each have its own cursor? – Josh Feb 22 '13 at 20:46
  • @Josh: Yes, one connection, two cursors. It's working for me, anyway. Threading doesn't really even give you concurrency in CPython due to the GIL but does seem to allow multiple cursors in the native SQL client. – steamer25 Feb 22 '13 at 22:32
  • Were you by any change able to create multiple cursors using a single connection?..I have the same reqt – user1050619 Apr 05 '13 at 15:24
  • 2
    @user1050619. Yes. It's also helpful to enable multiple active record sets in your connection string: ;MARS_Connection=yes – steamer25 Apr 09 '13 at 18:22
  • still no better answer ? – ziddarth Jun 23 '15 at 22:27
  • @steamer25 would you have a source for the string you mentioned (MARS_Connection=yes) ? This solved an issue I was having (thanks!) but I'm confused as the [MS documentation](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-ver15#in-memory-oltp) says "MultipleActiveResultSets=True" but it didn't work for me... – UdonN00dle Feb 23 '21 at 23:45
  • @steamer25 never mind, found a [reference](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-multiple-active-result-sets-mars?view=sql-server-ver15#sql-server-native-client-odbc-driver) it was to do with the ODBC driver ^_^ Thanks. – UdonN00dle Feb 23 '21 at 23:53
-6

I my own practice I have never met necessity to use more than one database cursor. Such problems are used to be solved by sophisticated SQL queries (joins, groups). Or (if you can to ignore perfomance issues) by using several simple queries.

Vasiliy Stavenko
  • 1,174
  • 1
  • 12
  • 29
  • 9
    When I write custom migration scripts to take data from badly designed, poorly documented systems onto new system, I regularly need several cursors open on both databases. – Kinjal Dixit Oct 29 '13 at 06:17