Support pysqlite3 for newer sqlite versions

[boulderwebdev]

Description

Currently aiosqlite relies upon importing the standard sqlite3 module from the python library. For many users, that is OK, but if you want to deploy your code on some platforms, the SQLite version installed by default cannot be easily upgraded through a package manager and some features are not available to these users (e.g. using WITHOUT ROWID in a table).

One avenue for fixing this issue is providing support for using pysqlite3 instead of the standard sqlite3 module. This can be done by either

1. Just using pysqlite3 by default
2. Update the setup script to add this feature as an optional installation requirement

For 2, I mean there should be the following two variations for installing aiosqlite

pip install aiosqlite
pip install aiosqlite[pysqlite3]

similar to how uvicorn gives the standard option while installing.

Details

• OS: Amazon Linux 2
• Python version: 3.7.9
• aiosqlite version: 0.16.0
• sqlite version installed: 3.7.17 but pysqlite3 supplies version 3.34.0
• Can you repro on 'main' branch? Yes
• Can you repro in a clean virtualenv? Yes

[boulderwebdev]

Another alternative is to pass in a sqlite variable into the connect method so people can inject their own sqlite adapter if needed.

[amyreese]

I'd be happy to review a PR that attempts to import pysqlite as sqlite3, assuming the interfaces are otherwise identical.

[boulderwebdev]

@jreese Unfortunately, there are some limitations with pysqlite over sqlite3 since not all features are present. In particular, multiple queries, multiple connections, and iterdump are not supported/implemented by pysqlite. You can see the changes I made here. If this is acceptable (which I think is reasonable), I'll submit it as a pull request.

[amyreese]

I forgot just how many modules import pieces of sqlite3. I think that doing your approach right would require both moving all of those imports into something like a aiosqlite.db module that handles the logic to import pysqlite when available, and then having everything else import from that module without needing a conditional. Also, for test cases, I would rather see use of something like unittest.skipIf(sqlite3.__name__ == "pysqlite3", "not supported on pysqlite") to get more informative test results.

All that said, I think it would also be nice to have an option on whether pysqlite is used (the limited functionality is the biggest concern IMO), so that there's a way to test against both sqlite3 and pysqlite3 when available, otherwise having pysqlite installed to test pysqlite will prevent testing against sqlite3 or any of the features that pysqlite doesn't support.

Maybe the more correct direction would start with something like #106, and have a pysqlite3-based connector that the connector can instantiate when requested, which would allow both connection types to exist simultaneously for testing.

[coleifer]

@boulderwebdev - sorry for asking on something 2+ years old, but can you clarify where multiple queries/connections are not supported? As far as I know only iterdump (which was janky and should not be used, anyways) is the only thing missing:

multiple queries, multiple connections, and iterdump are not supported/implemented by pysqlite