I am trying to do the asynchron equivalent of
engine = create_engine('sqlite:///./test.db')
stmt = session.query(MyTable)
data = pd.read_sql(stmt, engine)
but it fails with the error AttributeError: 'AsyncConnection' object has no attribute 'cursor'.
What would be the right way to make this work?
asyn_engine = create_async_engine('sqlite+aiosqlite:///./test.db')
stmt = select(MyTable)
data = pd.read_sql(stmt, async_engine)
This code in principle is working...
# Making pd.read_sql_query connection the first argument to make it compatible
# with conn.run_syn()
def read_sql_query(con, stmt):
return pd.read_sql(stmt, con)
async def get_df(stmt, engine):
async with engine.begin() as conn:
data = await conn.run_sync(_read_sql, stmt)
return data
asyn_engine = create_async_engine('sqlite+aiosqlite:///./test.db')
stmt = select(MyTable)
data = get_df(stmt, asyn_engine )
pd.read_sql raise error with asyncpg, use pd.read_sql_query instead of that.use run_sync and a partial
from functools import partial
from sqlalchemy.ext.asyncio import create_async_engine
engine_async = create_async_engine("sqlite+aiosqlite:///:memory:")
async with engine_async.connect() as conn:
await conn.run_sync(partial(pd.DataFrame({"a": [0, 1, 2, 3]}).to_sql,"table_toto"))
rst = await conn.run_sync(partial(pd.read_sql,"SELECT * FROM table_toto"))
print(rst.to_dict())
output
{'index': {0: 0, 1: 1, 2: 2, 3: 3}, 'a': {0: 0, 1: 1, 2: 2, 3: 3}}
