时间戳

首页 > 解决方案 > 尝试使用python在mysql上异步调用存储过程，无法这样做

python - 尝试使用python在mysql上异步调用存储过程，无法这样做

问题描述

#尝试使用python异步调用mysql db上的存储过程。

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import scoped_session, sessionmaker
import pymysql
import aiomysql
import asyncio
loop = asyncio.get_event_loop()
conn = await aiomysql.connect(host='blah', port=1234, user='user', password='abcd', db='DB', loop=loop)

async with conn.cursor() as cur:
  await cur.callproc("test_async")
#test_async is a stored_procedure on mysql db.

#存储过程

CREATE PROCEDURE test_async()
BEGIN
    set @p = 0;
    simple_loop: LOOP
        set @p=@p+1;
        insert into test_async values (@p);
        IF @p=100 THEN
            LEAVE simple_loop;
        END IF;
    end loop simple_loop;
END;

标签： pythonmysqlsqlalchemy

解决方案

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import scoped_session, sessionmaker
import pymysql
import aiomysql
import asyncio
db_connection_string = "mysql+aiomysql://{0}:{1}@{2}:{3}/{4}".format("user", "pwd", "localhost", "1234", "adult_match_db")
engine = create_async_engine(db_connection_string, future=True, echo=True, pool_pre_ping=True)
async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)
async with async_session.begin() as session:
  await session.execute("CALL test_async")
await session.commit()

推荐阅读