我注意到即使我在"上下文管理器"中执行SQL语句,请求完成后,该表查询仍然锁定,并且我无法在其上执行"截断",直到我停止活动循环。
这是我的代码的示例:
import logging
import asyncio
import aiomysql
from aiohttp import web
from aiomysql.cursors import DictCursor
logging.basicConfig(level=logging.DEBUG)
async def index(request):
async with request.app["mysql"].acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT * FROM my_table")
lines = await cur.fetchall()
return web.Response(text='Hello Aiohttp!')
async def get_mysql_pool(loop):
pool = await aiomysql.create_pool(
host="localhost",
user="test",
password="test",
db="test",
cursorclass=DictCursor,
loop=loop
)
return pool
if __name__ == "__main__":
loop = asyncio.get_event_loop()
mysql = loop.run_until_complete(get_mysql_pool(loop))
app = web.Application(loop=loop, debug=True)
app["mysql"] = mysql
app.router.add_get("/", index)
web.run_app(app)
执行curl'http://localhost:8080/',我将使用MySQL CLI连接到MySQL Server,并尝试执行" Truncate My_table" - 直到我停止AioHTTP,它才能完成。如何改变这种行为?
锁定锁定,因为默认情况下连接不在 autocommit 模式下。添加autocommit=True
应该解决问题。
pool = await aiomysql.create_pool(
host="localhost",
user="test",
password="test",
db="test",
autocommit=True,
cursorclass=DictCursor,
loop=loop)
另外,可以通过explicit命令发布交易:
await cur.execute("COMMIT;")
上下文经理的主要目的是关闭光标,不要进行交易。
aiomysql 具有sqlalchemy.core扩展名,并在上下文管理器上支持交易,请参见示例:
https://github.com/aio-libs/aiomysql/blob/93aa3e5f77d77d77ad5592c3e9519cfc9587bf9587bf9ac/tests/tests/tests/tests/pep492/pep492/