[aeros] Kyle Stanley Profile - aeros - Discussions on Python.org aeros CPython
core developer
July 18
Apologies for the late reply on this, but hopefully my answer can
provide some useful information to someone out there.
No problem - I appreciate the input.
It works by creating a separate thread to act as a db handler.
Is there a particular reason as to why you need to use a thread as the
DB handler? This really doesn’t seem like a situation where you would
need to use threads, unless you’re using an uncommon DBMS that doesn’t
have an async library.
My project supports 3 databases - PostgreSQL, Sql Server, and sqlite3.
To my knowledge only the first one has native async support.
Is there any problem with this approach?
[…]
Also, in general, if you can avoid using threads, coroutines are
significantly more lightweight and have faster context switching;
although it does come at the cost of some degree of added complexity.
When using asyncio, it’s typically better to avoid using threads unless
you have to (such as when you need to call IO-bound functions from a
library that doesn’t have async support). And even in those situations,
it’s much better to use |loop.run_in_executor()| or the upcoming
asyncio.to_thread()| (in 3.9) than it is to try passing the event loop
instance to other threads. It’s suggested to only use one event loop
instance per thread, and obtain access to it using
asyncio.get_running_loop()| when needed.
I tried using run_in_executor(). It worked, but did not give me what I
wanted.
If you SELECT rows from a database, you have a choice of fetchall(),
fetchmany(), or fetchone(). Or you can use the cursor as an iterator and
fetch the rows one at a time in a for loop.
I definitely want to use the iterator approach. fetchall() would not
block the event loop, but it would effectively block the caller until
all rows were received. With an iterator this does not happen.
I could not see a way to combine that with run_in_executor(). That is
why I came up with my own threaded approach. I found that returning each
row with call_soon_threadsafe() ran slowly, so I changed it so that the
thread returns rows in blocks of 50 at a time, and it runs very smoothly.
I did not know about to_thread(). I just had a quick look, but at first
glance I do not see how it would help in my situation.
By the way, I noticed a typo in the docs for to_thread() -
s/propogate/propagate. Maybe this can be fixed before final release.
Frank
Having said all of that, I realise that I never experimented with using run_in_executor() to fetch 50 rows at a time using fetchmany(50).
That may well give me the same functionality. I will give it a try and let you know. If it works and performance is similar, I will switch to this.
It works, but it runs about 20% slower on retrieving 32k rows. I will stick with my method for now, but will keep this for Plan B in case I run into any dragons!