Does asyncmy support sqlalchemy ORM

[wyj-afterpay]

https://docs.sqlalchemy.org/en/14/orm/tutorial.html#creating-a-session

What we do is

  async_engine = create_async_engine(
      "mysql+asyncmy:[url]ssl_ca=[cert]",
      future=True,
      pool_pre_ping=True,
      pool_recycle=1800,
  )
  _async_session = sessionmaker(bind=async_engine,
                                autocommit=False,
                                autoflush=False,
                                class_=AsyncSession,
                                expire_on_commit=False)
  
  @asynccontextmanager
  async def aio_session_scope():
      """ Provide a transactional scope around a series of operations"""
      async with _async_session() as session:
          try:
              yield session
              await session.commit()
          except Exception as e:
              await session.rollback()
              raise e
          finally:
              await session.close() 

    async with aio_session_scope() as session:
          records = (await session.execute(statement.offset(offset).limit(limit))).all()

The error thrown is:

 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/asyncio/session.py\", line 211, in execute\
   return await greenlet_spawn(\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py\", line 134, in greenlet_spawn\
   result = context.throw(*sys.exc_info())\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py\", line 1691, in execute\
   conn = self._connection_for_bind(bind)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py\", line 1532, in _connection_for_bind\
   return self._transaction._connection_for_bind(\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py\", line 747, in _connection_for_bind\
   conn = bind.connect()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/future/engine.py\", line 406, in connect\
   return super(Engine, self).connect()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 3204, in connect\
   return self._connection_cls(self, close_with_result=close_with_result)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 96, in __init__\
   else engine.raw_connection()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 3283, in raw_connection\
   return self._wrap_pool_connect(self.pool.connect, _connection)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py\", line 3250, in _wrap_pool_connect\
   return fn()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 310, in connect\
   return _ConnectionFairy._checkout(self)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 868, in _checkout\
   fairy = _ConnectionRecord.checkout(pool)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 476, in checkout\
   rec = pool._do_get()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py\", line 146, in _do_get\
   self._dec_overflow()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py\", line 70, in __exit__\
   compat.raise_(\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py\", line 207, in raise_\
   raise exception\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py\", line 143, in _do_get\
   return self._create_connection()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 256, in _create_connection\
   return _ConnectionRecord(self)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 371, in __init__\
   self.__connect()\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 666, in __connect\
   pool.logger.debug(\"Error on connect(): %s\", e)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py\", line 70, in __exit__\
   compat.raise_(\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py\", line 207, in raise_\
   raise exception\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py\", line 661, in __connect\
   self.dbapi_connection = connection = pool._invoke_creator(self)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py\", line 590, in connect\
   return dialect.connect(*cargs, **cparams)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py\", line 597, in connect\
   return self.dbapi.connect(*cargs, **cparams)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/dialects/mysql/asyncmy.py\", line 275, in connect\
   await_only(self.asyncmy.connect(*arg, **kw)),\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py\", line 76, in await_only\
   return current.driver.switch(awaitable)\
 File \"/usr/local/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py\", line 129, in greenlet_spawn\
   value = await result\
 File \"asyncmy/connection.pyx\", line 1299, in _connect\
 File \"asyncmy/connection.pyx\", line 555, in connect\
 File \"asyncmy/connection.pyx\", line 533, in asyncmy.connection.Connection.connect\
 File \"asyncmy/connection.pyx\", line 727, in _request_authentication\
 File \"/usr/local/lib/python3.9/asyncio/streams.py\", line 52, in open_connection\
   transport, _ = await loop.create_connection(\
 File \"uvloop/loop.pyx\", line 2069, in create_connection\
 File \"uvloop/loop.pyx\", line 2064, in uvloop.loop.Loop.create_connection\
 File \"uvloop/sslproto.pyx\", line 517, in uvloop.loop.SSLProtocol._on_handshake_complete\
 File \"uvloop/sslproto.pyx\", line 477, in uvloop.loop.SSLProtocol._start_handshake\
AttributeError: 'dict' object has no attribute 'wrap_bio'"

This error will show if we use ssl.

[long2ice]

Looks like problem of ssl

[wyj-afterpay]

@long2ice Yep, that's correct. The trace starts at sqlalchemy/ext/asyncio/session.py so not sure if only using the sessionmaker will trigger this problem.

Only when using SSL will have this exception, there won't be any exception if not using SSL ?ssl_ca=xxx in the connection string.

[zoltan-fedor]

I hit the same issue using the rds-combined-ca-bundle.pem from AWS to connect to AWS RDS instances (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html).

The same SSL CA certificate works fine with pymysql, so it is likely not a problem with the SSL certificate itself.

It works fine without the SSL certificate, so yes, it is something SSL-related, but not the certificate itself or the MySQL server side, as the same works in pymysql.

[zoltan-fedor]

I have figured it out.

For some reason providing the SSL cert in the URI doesn't work, but you can provide the SSL context in the connect_args to the create_async_engine(), as per below:

import ssl
from sqlalchemy.ext.asyncio import create_async_engine

ssl_ctx= ssl.create_default_context(cafile="cert/file")
ssl_ctx.verify_mode = ssl.CERT_REQUIRED
   
async_engine = create_async_engine(
   "mysql+asyncmy:[url]",
   connect_args={"ssl": ssl_ctx}
)