Please don’t mix about connection API and connection pool API.
Enable autocommit by default at DB-API layer would not make it useless for production application, because application will use Pool API, instead of DB-API. It’s very difficult to manage raw DB-API connection from application without the Pool.
Connection pool can provide autocommit APIs and transactional APIs for applications. It may look like this.
# autocommit APIs
result = await pool.query("SELECT * FROM tbl")
# transactional APIs
async with pool.begin() as trx:
result = await trx.query("SELECT * FROM tbl")
await trx.execute("UPDATE tbl2 SET v=? WHERE id=?", (val, id))
On the other hand, the pool will use async DB-API. This is very rough scketch of pool implementation. This is out of scope of DB-API.
class Pool:
...
async def query(self, query, args=None) -> Result:
raw_conn = await self._get_conn() # autocommit is enabled by default.
try:
cursor = raw_conn.cursor()
await cursor.query(query, args)
result = await self._fetch_result(cursor)
except:
self._close_conn(raw_conn)
raise
else:
self._put_conn(raw_conn)
return result
....
async def begin(self) -> Transaction:
return Transaction(self, await self._get_conn())
....
class Transaction:
def __init__(self, pool, conn):
self._pool = pool
self._conn = conn
async def query(self, query, args) -> Result: ...
async def execute(self, query, args): ...
async def __aenter__(self):
await conn.begin() # transaction is start here
return self
async def __aexit__(self, exc_type, exc, tb):
if exc:
await self.rollback()
self._pool._close_conn(self._conn)
else:
await self.commit()
self._pool._put_conn(self._conn)
self._conn = None
async def commit(self): ...
async def rollback(self): ...
Additionally, note that I don’t propose enable autocommit by default. I’m proposing adding a standard way to enable autocommit by default. For example, mysqlclient has autocommit=False option. Connection pool can use autocommit=True option if this option is standardized.
I don’t have a strong opinion about which should be the default value. In async application, using DB-API directly is too difficult to human being. There should be a Pool. Async DB-API is for Pool, not for human. So the default value of this option is not important for usability.