> My guess is that the GIL is causing your problem here. The other thing you probably want to do is run the profiler on your code and see where the time is actually being spent.
> Remember that the Python GIL will only allow one thread at a time run Python Code, and underlying C libraries must release the GIL at appropriate times if they want to allow other
> things to happen while they are waiting on C code. Of course this is the whole reason that asyncio I was written in the first place, to allow a single thread to do packets of work based
> on when data arrives asynchronously.
>
> If you are using asyncio and want to actually run things asynchronously you will need to use aiopg or something you roll your self. remember asyncio is a wrapper around select and poll. > There is documentation in psycopg2 about making things asynchronous
Thanks for the reply, Michael.
I suspect that the GIL is causing the problem. I am wondering if psycopg2 is not releasing the GIL at the appropriate time, while pyodbc and sqlite3 are. The symptoms seem to suggest that.
Regarding profiling, I use exactly the same program while testing with pyodbc and sqlite3 – the only difference is setting up the database connection. So I don’t think that anything in my code is causing the holdup.
I was trying to avoid aiopg, simply because I am trying to maintain generic code to support all three databases as much as possible, and the other two do not seem to have asynchronous support at all.
I still think that my theory holds water. Run the database in its own thread, pass requests to it using a queue.Queue(), and pass results back using an asyncio.Queue(). It works brilliantly for the other two databases, and I cannot see any reason why it should not work with psycopg2.