AttributeError: 'CMySQLConnection' object has no attribute 'inside_transaction' when using spanner + mysql dialects in separate engines

[JoudZouzou]

Environment details

• Programming language: Python
• OS: Linux
• Language runtime version: 3.7.12
• Package version: SQLAlchemy==1.4.26 sqlalchemy-spanner==1.2.0 mysql-connector-python==8.0.26

Steps to reproduce

1. Setup an engine for spanner, then setup an engine for mysql using mysqlconnector.
2. Run a query on spanner engine, works fine
3. Run a query on mysql engine, the query is executed, but throws an error

spanner_engine = sqlalchemy.create_engine("spanner+spanner:///projects/PROJECT/instances/INSTANCE/databases/DB")
mysql_engine = sqlalchemy.create_engine('mysql+mysqlconnector://USER:PASS@HOST/')
print(spanner_engine.execute('SELECT 1').scalar())
print(mysql_engine.execute('SELECT 1').scalar())

Output:

1
Exception during reset or similar
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 739, in _finalize_fairy
    fairy._reset(pool)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/pool/base.py", line 982, in _reset
    pool.dispatch.reset(self, self._connection_record)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.7/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 51, in reset_connection
    if dbapi_conn.connection.inside_transaction:
AttributeError: 'CMySQLConnection' object has no attribute 'inside_transaction'
1

It seems to be similar to the issue that is fixed in this PR: https://github.com/googleapis/python-spanner-sqlalchemy/pull/188

The issue was mentioned here https://github.com/googleapis/python-spanner-sqlalchemy/issues/192#issuecomment-1150879467, but when using one engine. While using separate engine works, it still throws that error.

I'm currently using this fork to avoid throwing this error, following the comment from @IlyaFaer here https://github.com/googleapis/python-spanner-sqlalchemy/issues/192#issuecomment-1152266653 but I'm not sure if just avoiding that operation is the correct solution.

Thanks!

[IlyaFaer]

@JoudZouzou, you're right, the fix you've did in the fork is good.

Docs don't cover using several databases together much, but it appears SQLAlchemy connection reset mechanism is pretty global. Even if you're using two separate engines, the method is overriden for both of them and treats all connections as Spanner connections. Thus, checking a property is okay, only Spanner connections have this inside_transaction property, so it should not influence other databases.

You can also try this fix: https://github.com/googleapis/python-spanner-sqlalchemy/pull/222 - we're testing it now.