Exception has occurred: MissingGreenlet whe making connection query in sql alchemy 1.4

user_1116654 0
Viewed 8412

I'm trying to do a simple query with SQLAlchemy 1.4.17 from within pytest

def test_first():
    engine = create_engine(settings.SQLALCHEMY_DATABASE_URI)
    result = engine.execute(text("SELECT email FROM user"))

but am getting this error

Exception has occurred: MissingGreenlet
greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: http://sqlalche.me/e/14/xd2s)
  File "/Users/mattc/Development/inference/server/inference_server/app/tests/test_01_user.py", line 27, in test_first
    result = engine.execute(text("SELECT email FROM user"))

and do not know why? Any suggestions?

sqlalchemy python
1 Answers

You are trying to use an async connector package in the same way as a synchronous connector

>>> import sqlalchemy as sa
>>> engine = sa.create_engine('postgresql+asyncpg:///')
>>> res = engine.execute(sa.text('SELECT 1'))
<stdin>:1: RemovedIn20Warning: The Engine.execute() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
Traceback (most recent call last):
...
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: http://sqlalche.me/e/14/xd2s)

You need to either use a synchronous connector, for example psycopg2, pg8000, or write async code:

import sqlalchemy as sa

import asyncio

from sqlalchemy.ext.asyncio import create_async_engine


async def async_main():
    engine = create_async_engine(
        "postgresql+asyncpg:///test", echo=True,
    )

    async with engine.connect() as conn:

        # select a Result, which will be delivered with buffered
        # results
        result = await conn.execute(sa.text('select email from users'))

        print(result.fetchall())
    await engine.dispose()


asyncio.run(async_main())
user_53209060
Related