Make sure that only one cursor possible per connection

[jettify]

We allow multiple cursors per connection, I guess we need to forbid such behaviour and make sure only one cursor possible per connection. There is test that failing on multiple cursors https://github.com/aio-libs/aiomysql/blob/master/tests/test_nextset.py#L53-L73 , should be fixed too.

[asvetlov]

Sure, by DB API spec there is the only cursor is allowed for connection object in the same time.

[pedrudehuere]

Hello, I've been playing around with asyncio and used aiomysql for database access, I noticed that coroutines cannot create cursors from a shared connection (like the title of this issue states).
Why is this? because PyMYSQL was not designed for asyncio? will this change in the future or is it just a fact that I should accept?

[asvetlov]

It's by DB-API design, not specifically PyMYSQL. So yes, you should live with it.

[pedrudehuere]

Sorry but I cannot find anything that says that, the only thing is this section in the DB API, but it does not mention that only one cursor per connection can exist at any time

[asvetlov]

Well. It's not stated explicitly. But using non-isolated cursors is dangerous idea at first.
Second, Postgres has the same limitation.
I believe nobody should try to use several cursors for the same connection in parallel.

[pedrudehuere]

Except for libraries with threadsafety 2 or 3 I guess? (I'm talking about threads here)
But I guess threadsafety does not apply to asyncio

Edit: I see now that PyMSQL has thread-safety level 1

[asvetlov]

I think it's orthogonal concept.
SQL queries are performed on SQL server side. Usually server uses a thread per connection model and doesn't allow executing several requests for the same connection in parallel internally.

So it's mostly server limitation, not client one.

[pedrudehuere]

OK I see, thanks

[tvoinarovskyi]

Should we just add a stub, with an exception if we call connection.cursor() on a connection with active cursor? Will this be enough?

[methane]

I think guard like following is enough.

if self._guard:
    raise OperationError("Connection cannot be used in parallel")
self._guard = True
await self._execute_command(...)
await self._read_ok_packet(...)
self._guard = False

[pedro2555]

If I need to wait for 100 queries. Does this mean I need to call connect() 100 times?