asyncio-compatible dialect

[cancan101]

SQLAlchemy 1.4 now supports Python asyncio. In order to take advantage of this, the dialect must be "asyncio-compatible". It would be great to have a version of the base Dialect that can be used for this.

A discussion for what that entails: sqlalchemy/sqlalchemy#7854.

Currently the APSWDialect class subclasses SQLiteDialect, which is not async. There is a SQLiteDialect_aiosqlite that could potentially be used. The goal here is not necessarily to have async operations vis a vis sqlite but rather to allow async operations when connecting to the APIs.

This might be tricky as you would want this to be an async iterator:

shillelagh/src/shillelagh/backends/apsw/db.py

Line 221 in 97197bd

def _convert(self, cursor: "apsw.Cursor") -> Iterator[Tuple[Any, ...]]:

and then likewise have the get_rows call be an async iterator:

shillelagh/src/shillelagh/backends/apsw/vt.py

Lines 439 to 441 in 97197bd

	self.data = (
	tuple(row.get(name) for name in ["rowid", *column_names]) for row in rows
	)

I filed this upstream though I am not 100% sure this is the right way to solve the ask: rogerbinns/apsw#325

[betodealmeida]

Oof, this sounds hard. I'm going to try to implement this in another dialect (https://github.com/DataJunction/datajunction/blob/main/src/datajunction/sql/sqlalchemy/dialect.py) first to understand better what it entails.

[cancan101]

Yea this might be a pipe dream . Not sure it's possible in SQLite. Might be possible using postgres as engine with fdw