15

I write asynchronous telegram bot using the aiogram library. I decided to use SQLite as a database for storing immutable values. How do I implement asynchronous reads from my database?

rdfsx
  • 152
  • 1
  • 1
  • 7
  • 2
    The first part of the question is clearly opinion-based, but the second (and more important) part is a useful question and can be answered. Given that the OP is a new contributor, I have elected to answer the question instead of voting to close. – user4815162342 Oct 06 '18 at 20:26

1 Answers1

27

A wrapper for the sqlite3 module already exists, and you should use it in your async code.

Alternatively, you can turn synchronous calls into asynchronous ones by wrapping them in run_in_executor. For example:

async def fetchall_async(conn, query):
    loop = asyncio.get_event_loop()
    return await loop.run_in_executor(
        None, lambda: conn.cursor().execute(query).fetchall())

That gives you a coroutine you can call from your async code without worrying that a long-running execution will block the whole event loop:

async def some_task():
    ...
    students = await fetchall_async(conn, "select * from students")

But it is a much better idea to leave this to a well-tested package.

user4815162342
  • 141,790
  • 18
  • 296
  • 355
  • Thank you. Can I pass arguments to execute()? execute(query, (arg,)) does not work. – rdfsx Oct 07 '18 at 14:36
  • @dydoser There should be no problem padsing arguments to [`execute`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute). What exactly doesn't work when doing so? – user4815162342 Oct 07 '18 at 14:58
  • The one mentioned already uses an executor and single threaded at that. Is there anything closer to the metal using libuv? – Samantha Atkins Mar 11 '19 at 17:50
  • @SamanthaAtkins I don't know - perhaps you should ask a question, preferably with details why existing solutions don't satisfy your requirements. – user4815162342 Mar 11 '19 at 18:23
  • The [`databases` package](https://www.encode.io/databases/) also provides this capability for SQLite and a number of other databases. `databases` is more popular than `aiosqlite` (as measured by GitHub stars), but they are both mature and actively maintained. – David Bruce Borenstein Jul 25 '20 at 16:11
  • 4
    PS: Turns out `databases` uses `aiosqlite` as a back-end for SQLite support... – David Bruce Borenstein Jul 25 '20 at 16:17