[Python] How to use asyncio gather with sqlalchemy session and the workaround

I am trying to make an asynchronous call using asyncio gather to call multiple database query. I am getting error

sqlalchemy.exc.IllegalStateChangeError: Method 'close()' can't be called here; method '_connection_for_bind()' is already in progress and this would cause an unexpected state change to <SessionTransactionState.CLOSED: 5> (Background on this error at: https://sqlalche.me/e/20/isce)


Based on this github https://github.com/sqlalchemy/sqlalchemy/discussions/9312 Its not possible to share the same session and doing asyncio.gather(func(session), func2(session)

Is there any solution that will make us able to do some db queries simultaneously?

Code:

async def handler(
app_db: AsyncSession = Depends(get_async_session_app),
):

# some other codes

conversation_task = get_one_app(
app_db,
query=text("SELECT data_schema FROM conversations WHERE id = :conversation_id"),
params={"conversation_id": conversation_id},
)
messages_task = get_all_app(
app_db,
query=text("""
SELECT id, role, content FROM raw_messages WHERE conversation_id = :conversation_id
ORDER BY created_at ASC, id ASC
"""),
params={"conversation_id": conversation_id},
)

conversation, messages_result = await asyncio.gather(conversation_task, messages_task)

#rest of the code


database config

SQLALCHEMY_DATABASE_URL = os.getenv("APP_DB_URL")

engine = create_async_engine(SQLALCHEMY_DATABASE_URL, echo=True)
AsyncSessionLocal = sessionmaker(
autocommit=False, autoflush=False, bind=engine, class_=AsyncSession
)

async def get_async_session():
async with AsyncSessionLocal() as session:
try:
yield session
finally:
await session.close()

Continue reading...