Multithreaded pyodbc connection

Question

I am trying to use a pyodbc connection in multiple threads. I am receieving the following error: Connection is busy with results for another command (0) (SQLExecDirectW)'). I also tried setting MultipleActiveResultSets=yes; and got the same results.

I really don't want to have to create a connection for every thread or query. I also don't want to use a lock to serialize queries from all threads.

I was first using the driver "SQL Server" and then moved to "SQL Server Native Client 11.0" with no luck.

Any suggestions?

Answer 1

Since this question asks for suggestions and not really a strong answer, here's mine:

Firstly, I'd suggest creating a new connection for each thread created.

Or, if you're okay with killing what is currently executing, try cursor.cancel() before reusing your connection.

Also, after a quick search, I find multiple references to Multiple Active Result Sets related to this issue. If your database supports it, try adding "MultipleActiveResultSets=True" to your connection string.

I hope one of these suggestions helps you get where you need to be!