Trying to use a completely new (non-async) engine and session triggers the MissingGreenlet error

[gwerbin]

Describe the bug

I created an async engine and async sessionmaker for global use in the async areas of my application.

I also need to interact with the database from a non-async region of my application. However, creating a non-async session or connection invariably results in the MissingGreenlet: greenlet_spawn has not been called ... exception.

This seems like a bug because it seems to be entirely obstructing the use of an entirely distinct engine and session that happens to connect to the same database. I would not expect the Greenlet spawn context to be globally associated with the database URI.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.15

DBAPI (i.e. the database driver)

asyncpg == 0.27.0

Database Vendor and Major Version

PostgreSQL 15 (TimescaleDB)

Python Version

3.10.11

Operating system

MacOS 12.4

To Reproduce

python -m venv .venv
.venv/bin/pip install -U wheel
.venv/bin/pip install sqlalchemy==2.0.15 asyncpg==0.27.0

.venv/bin/python mydb.py

Contents of mydb.py:

from datetime import datetime as Datetime
from typing import TypeAlias

from sqlalchemy import TIMESTAMP, create_engine
from sqlalchemy.ext.asyncio import AsyncEngine
from sqlalchemy.ext.asyncio import AsyncSession as _AsyncSession
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    type_annotation_map = {
        Datetime: TIMESTAMP(timezone=True),
    }

_db_uri = 'postgres+asyncpg://localhost:5432/postgres'

async_engine: AsyncEngine = create_async_engine(
    _db_uri,
    pool_pre_ping=True,
    pool_size=20,
    pool_recycle=5,
    max_overflow=5,
)

AsyncSession: async_sessionmaker[_AsyncSession] = async_sessionmaker(bind=async_engine)

# Trigger the exception

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
engine = create_engine(_db_uri, poolclass=NullPool)
with engine.connect():
    pass

Error

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/Users/XXX/XXX/software/my-project/source/XXX/datascience_api_service/db/core.py", line 35, in <module>
    with engine.connect():
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3269, in connect
    return self._connection_cls(self)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3293, in raw_connection
    return self.pool.connect()
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1268, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 283, in _do_get
    return self._create_connection()
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 895, in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
  File "/Users/XXX/XXX/software/my-project/.venv/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 117, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/20/xd2s)

Additional context

No response

[zzzeek]

hey there -

the asyncpg dialect only supports async engines:

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.asyncpg

This dialect should normally be used only with the create_async_engine() engine creation function

to use "asyncpg" with regular create_engine, there's a special mode you can use ?async_fallback=true, like this:

e = create_engine(_db_url + "?async_fallback=true")

however, this wont run very efficiently. for create_engine() I would stick with psycopg2 or psycopg.

[gwerbin]

Yikes, of course. I knew there was something obvious I was missing, thank you.

[CaselIT]

A better way is to use run_sync from an async session or connection