In the provided code snippet, I demonstrate an easy and concise approach to access SQLite3 databases using the library I made in an asynchronous Python environment. Will there be a chance for sqlite3 to implement changes like this in a synchronous environment?
import asyncio
from asqlite3 import asqlite3
async def main():
db = await asqlite3.connect(':memory:')
# create table 'user'
db.query = """
CREATE TABLE "user" (
"id" INTEGER,
"name" TEXT,
PRIMARY KEY("id")
);
"""
db.value = None
await db.execute()
# insert items
db.query = "INSERT INTO user(id, name) VALUES (?, ?);"
db.value = [(1, 'John'), (2, 'Peter'), (3, 'Samuel')]
await db.execute()
# check if row count is 3
db.query = "SELECT * FROM user;"
db.value = None
r = await db.fetch(0)
assert len(r) == 3
# fetching an item
db.query = "SELECT * FROM user WHERE id=?;"
db.value = 2
r = await db.fetch(1)
assert r['name'] == 'Peter'
# close database connection
await db.close()
asyncio.run(main())
This API seems harder, not simpler. Instead of getting a cursor and running a single method to make a query, you need to set two attributes and call a method.
This API has global state on the DB level (the query and value parameters), which means it’s not usable from within multiple threads, and it might get messy with async as well.
This API makes it too easy to mix up queries and parameters by asking users to provide them in two different statements. Reusing the same query with different sets of parameters is quite rare (but it might be useful for INSERTing a bunch of things); with sqlite3, you can use cur.executemany(), and there is no confusing global state.
The API implemented by the standard library sqlite3 module is standardized across all Python DB access libraries out there (like psycopg2 or mysqlclient), and it’s simple, straightforward, and does not have global state. Your library should follow that standard (DB-API 2.0), not the other way around.
