SQLAlchemy Version 2.0.0 breaks reflected tables

[ArielZamparini-REISys]

I am having an issue since SQLAlchemy was updated yesterday as part of dependency discovery, since then it appears to install version 2.0.0 of the main library

sqlalchemy-2.0.0 
sqlalchemy-aurora-data-api-0.4.1 

vs what it was doing a day ago

sqlalchemy-1.4.46 
sqlalchemy-aurora-data-api-0.4.1 

After this we have been forced to update from the old table reflection method to the new

OLD

metadata = MetaData(bind=None)
firm_table = Table(
    'firm',
    metadata,
    autoload=True,
    autoload_with=engine
)

NEW (from what i could gather from the docs:

metadata = MetaData()
metadata.reflect(engine)
firm_table = Table(
     'firm',
     metadata,
     autoload_with=engine
 )

However its causing this big long error:

chalice local --host=0.0.0.0 --no-autoreload
Found credentials in shared credentials file: ~/.aws/credentials
Traceback (most recent call last):
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1410, in execute
    meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/cli/__init__.py", line 636, in main
    return cli(obj={})
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/root/helloworld/venv/lib/python3.8/site-packages/click/decorators.py", line 26, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/cli/__init__.py", line 135, in local
    run_local_server(factory, host, port, stage)
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/cli/__init__.py", line 154, in run_local_server
    server = create_local_server(factory, host, port, stage)
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/cli/__init__.py", line 143, in create_local_server
    app_obj = config.chalice_app
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/config.py", line 141, in chalice_app
    app = v()
  File "/root/helloworld/venv/lib/python3.8/site-packages/chalice/cli/factory.py", line 277, in load_chalice_app
    app = importlib.import_module('app')
  File "/usr/local/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 975, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 671, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/root/helloworld/app.py", line 4, in <module>
    from chalicelib import firm_private, firm_public, common, solicitation_public, award_private, award_public, comm_private, user_private
  File "/root/helloworld/chalicelib/firm_private.py", line 1, in <module>
    from chalicelib.rei_utils import *
  File "/root/helloworld/chalicelib/rei_utils.py", line 18, in <module>
    metadata.reflect(engine)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 5452, in reflect
    with inspection.inspect(bind)._inspection_context() as insp:
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/inspection.py", line 111, in inspect
    ret = reg(subject)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 304, in _engine_insp
    return Inspector._construct(Inspector._init_engine, bind)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 237, in _construct
    init(self, bind)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/reflection.py", line 248, in _init_engine
    engine.connect().close()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3245, in connect
    return self._connection_cls(self)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 3269, in raw_connection
    return self.pool.connect()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 1255, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
    self._dec_overflow()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 166, in _do_get
    return self._create_connection()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 914, in __connect
    pool.dispatch.connect.for_modify(
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 473, in _exec_w_sync_on_first_run
    self(*args, **kw)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 487, in __call__
    fn(*args, **kw)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 1912, in go
    return once_fn(*arg, **kw)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 746, in first_connect
    dialect.initialize(c)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/base.py", line 2748, in initialize
    self._connection_charset = self._detect_charset(connection)
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy_aurora_data_api/__init__.py", line 138, in _detect_charset
    return connection.execute("SHOW VARIABLES LIKE 'character_set_client'").fetchone()[1]
  File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
    raise exc.ObjectNotExecutableError(statement) from err
sqlalchemy.exc.ObjectNotExecutableError: Not an executable object: "SHOW VARIABLES LIKE 'character_set_client'"

Any ideas as to why it fails now? Are we performing the table reflections incorrectly or is there some kind of library issue with the new version?

Docs:
https://docs.sqlalchemy.org/en/20/core/reflection.html#reflecting-all-tables-at-once
https://docs.sqlalchemy.org/en/14/changelog/migration_20.html

[2904nando]

I faced the same issue with my team, until we go through the migration docs, I created a Fork for this repo (https://github.com/2904nando/sqlalchemy-aurora-data-api-1-4-46) which blocks the SQLAchemy version on setup.py to < 2.

This is just a workaround, but it gives us time to review the 2.0 migration docs before going forward.

[JPocalyko-REISys]

I faced the same issue with my team, until we go through the migration docs, I created a Fork for this repo (https://github.com/2904nando/sqlalchemy-aurora-data-api-1-4-46) which blocks the SQLAchemy version on setup.py to < 2.

This was the same workaround our team did, though we just set an explicit dependency of "sqlalchemy<2" in requirements.txt of our app.

[2904nando]

I am having an issue since SQLAlchemy was updated yesterday as part of dependency discovery, since then it appears to install version 2.0.0 of the main library
Traceback (most recent call last):
File "/root/helloworld/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1410, in execute
meth = statement._execute_on_connection
AttributeError: 'str' object has no attribute '_execute_on_connection'

To fix this specific error, I guess you should add a text on any String direct SQL command sent on your code.

Example:

From:

connection.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')

To:

from sqlalchemy import text
connection.execute(text(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp";`))

This applies to any direct SQL statement sent by the DB connection.

"Passing a string to Connection.execute() is deprecated and will be removed in version 2.0. Use the text() construct"

from the migration docs (https://docs.sqlalchemy.org/en/14/changelog/migration_20.html)