Use of python-spanner-alchemy breaks sqlalchemy-sqlite

[aderk]

Environment details

• Programming language: Python 3.8.8 (default, Apr 1 2021, 17:00:06)
• OS: gLinux
• Language runtime version: Python 3.8.8 (default, Apr 1 2021, 17:00:06)
• Package version:
  SQLAlchemy==1.3.23
  sqlalchemy-spanner==1.0.0

Steps to reproduce

1. Create a SQLAlchemy Engine backed by python-spanner-sqlalchemy
2. Create a new sqlalchemy engine backed by an in-memory sqlite DB

There's something going on with global SA state which python-spanner-sqlalchemy is polluting. I think it is related to this line: https://github.com/googleapis/python-spanner-sqlalchemy/blob/main/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py#L51

"""python-spanner-sqlalchemy causes sqlite-sqlalchemy to fail

Run emulator with:
    gcloud emulators spanner start

Run this script as:
    SPANNER_EMULATOR_HOST=localhost:9010 python test_case.py

"""

from sqlalchemy import Column, Integer, select, MetaData, Text, Table, create_engine
import google.cloud.spanner as spanner
import google.auth.credentials as gac

project_id = 'foo'
instance_id = 'bar'
database_name = 'baz'

def create_spanner_table() -> None:
    client = spanner.Client(project=project_id, credentials=gac.AnonymousCredentials())
    try:
        instance = client.instance(instance_id)
        instance.create().result(120)
    except Exception as e:
        print(e)
    
    try:
        database = instance.database(database_name)
        database.create().result(120)
    except Exception as e:
        print(e)

    spanner_uri = f'spanner:///projects/{project_id}/instances/{instance_id}/databases/{database_name}'

    engine = create_engine(spanner_uri)
    metadata = MetaData(bind=engine)
    _t = Table("TestTable1", metadata, Column("test_column1", Text, nullable=False))
    metadata.create_all(engine)
    print('Done creating spanner table')

def create_sqlite_table_and_select(uri: str = 'sqlite:///') -> None:
    engine = create_engine(uri)
    metadata = MetaData(bind=engine)
    t = Table("TestTable2", metadata, Column("test_column2", Text, nullable=False))
    metadata.create_all(engine)
    print('Done creating sqlite table')
    with engine.begin() as conn:
        _results = conn.execute(select([t.c.test_column2])).fetchall()
    print('sqlite SELECT complete.')

# Example 0:
#   create_sqlite_table_and_select()
#
# Done creating sqlite table
# sqlite SELECT complete.

# Example 1:
#   create_spanner_table()
#   create_sqlite_table_and_select('sqlite:///testdb.sqlite') # File-backed sqlite
#
# Exception during reset or similar
# Traceback (most recent call last):
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 697, in _finalize_fairy
#     fairy._reset(pool)
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 874, in _reset
#     pool.dispatch.reset(self, self._connection_record)
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 261, in __call__
#     fn(*args, **kw)
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 51, in reset_connection
#     dbapi_conn.connection.staleness = None
# AttributeError: 'sqlite3.Connection' object has no attribute 'staleness'
# sqlite SELECT complete.


# Example 2:
#   create_spanner_table()
#   create_sqlite_table_and_select('sqlite:///') # Memory-backed sqlite
#
# Traceback (most recent call last):
#   File "test_case.py", line 70, in <module>
#     create_sqlite_table_and_select('sqlite:///')
#   File "test_case.py", line 50, in create_sqlite_table_and_select
#     _results = conn.execute(select([t.c.test_column2])).fetchall()
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
#     return meth(self, multiparams, params)
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
#     return connection._execute_clauseelement(self, multiparams, params)
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
#     ret = self._execute_context(
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
#     self._handle_dbapi_exception(
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
#     util.raise_(
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
#     raise exception
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
#     self.dialect.do_execute(
#   File "/usr/local/google/home/aderk/.virtualenvs/pyle/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
#     cursor.execute(statement, parameters)
# sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: TestTable2
# [SQL: SELECT "TestTable2".test_column2 
# FROM "TestTable2"]
# (Background on this error at: http://sqlalche.me/e/13/e3q8)

[IlyaFaer]

@aderk, we're using a reset mechanism to drop staleness option when a connection is returned to a pool. The reset mechanism is bounded to the Pool() class, globally (in order to catch all the use cases without forcing users to add special code). It appears SQLite executed aside is also affected. Not a big deal, I suppose, let's just add an if-statement to avoid touching non-Spanner connections.

[aderk]

Awesome, thank you! A 1.0.1 release including #188 would be very helpful. Is this done automatically, or will I need to wait? Thanks again