sqlalchemy-spanner: `alembic revision --autogenerate` fails

オープン
#17,648 コメント 1 件 リアクション 0 件 担当者 1 名 GitHub で見る

メンテナーはふだん 2 日以内に返信

@olavloite がすでに取り組んでいます。

2026年7月8日 から。

評価

この issue はまだ評価されていません。

説明

api: spanner priority: p2 type: bug
Determine this is the right repository
  • I determined this is the correct repository in which to report this bug.
Summary of the issue

Context
I attempted to use sqlalchemy-spanner with alembic (as suggested in sqlalchemy-spanner's README) to generate database migrations.

Expected Behavior:
I expect alembic revision --autogenerate -m init to run without error against a new Spanner DB in the Spanner emulator and for a deployed instance.

Actual Behavior:
Migration fails to generate.

API client name and version

sqlalchemy-spanner 1.19.0

Reproduction steps: code

Since a reproduction requires SQLAlchemy models and an Alembic config, I created a repository for simplicity.

https://github.com/cdco-busunkim/spanner-sqlalchemy-issue/tree/main

  1. Follow emulator based instructions in https://github.com/googleapis/google-cloud-python/tree/main/packages/sqlalchemy-spanner#migration.
  2. Then attempt to generate an Alembic migration: poetry run alembic --autogenerate -m init
Reproduction steps: supporting files

N/A, see above

Reproduction steps: actual results

NOTE: I manually inserted a print statement in the sqlalchemy-spanner code before this line https://github.com/googleapis/google-cloud-python/blob/1f93a2b881bce80222ea71bb38adca94195fb0ae/packages/sqlalchemy-spanner/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py#L1151 to see the SQL query.


➜  spanner-reference git:(main) ✗ poetry run alembic revision --autogenerate -m "init"

INFO  [alembic.runtime.migration] Context impl SpannerImpl.

INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.schemas

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.tables

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.types

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.constraints

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.defaults

INFO  [alembic.runtime.plugins] setting up autogenerate plugin alembic.autogenerate.comments



            SELECT col.table_schema, col.table_name, col.column_name,

                col.spanner_type, col.is_nullable, col.generation_expression,

                col.column_default

            FROM information_schema.columns as col

            JOIN information_schema.tables AS t

                USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)

            WHERE

                ()  AND 

                (t.table_type = 'BASE TABLE')  AND 

                 col.table_schema = '' AND 

                col.table_catalog = ''

            ORDER BY

                col.table_catalog,

                col.table_schema,

                col.table_name,

                col.ordinal_position

        

Traceback (most recent call last):

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 149, in error_remapped_callable

    return _StreamingResponseIterator(

        result, prefetch_first_result=prefetch_first

    )

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 71, in __init__

    self._stored_first_result = next(self._wrapped)

                                ~~~~^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/grpc/_channel.py", line 538, in __next__

    return self._next()

           ~~~~~~~~~~^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/grpc/_channel.py", line 956, in _next

    raise self

grpc._channel._MultiThreadedRendezvous: <_MultiThreadedRendezvous of RPC that terminated with:

        status = StatusCode.INVALID_ARGUMENT

        details = "Syntax error: Unexpected \")\" [at 9:18]\n                ()  AND \n                 ^"

        debug_error_string = "INVALID_ARGUMENT:Syntax error: Unexpected \")\" [at 9:18]\n                ()  AND \n                 ^"

>



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



Traceback (most recent call last):

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/bin/alembic", line 8, in <module>

    sys.exit(main())

             ~~~~^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 1047, in main

    CommandLine(prog=prog).main(argv=argv)

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 1037, in main

    self.run_cmd(cfg, options)

    ~~~~~~~~~~~~^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/config.py", line 971, in run_cmd

    fn(

    ~~^

        config,

        ^^^^^^^

        *[getattr(options, k, None) for k in positional],

        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

        **{k: getattr(options, k, None) for k in kwarg},

        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    )

    ^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/command.py", line 309, in revision

    script_directory.run_env()

    ~~~~~~~~~~~~~~~~~~~~~~~~^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/script/base.py", line 545, in run_env

    util.load_python_file(self.dir, "env.py")

    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file

    module = load_module_py(module_id, path)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py

    spec.loader.exec_module(module)  # type: ignore

    ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^

  File "<frozen importlib._bootstrap_external>", line 762, in exec_module

  File "<frozen importlib._bootstrap>", line 491, in _call_with_frames_removed

  File "/Users/busunkim/dev/spanner-reference/src/migrations/env.py", line 94, in <module>

    run_migrations_online()

    ~~~~~~~~~~~~~~~~~~~~~^^

  File "/Users/busunkim/dev/spanner-reference/src/migrations/env.py", line 88, in run_migrations_online

    context.run_migrations()

    ~~~~~~~~~~~~~~~~~~~~~~^^

  File "<string>", line 8, in run_migrations

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/runtime/environment.py", line 969, in run_migrations

    self.get_context().run_migrations(**kw)

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/runtime/migration.py", line 614, in run_migrations

    for step in self._migrations_fn(heads, self):

                ~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/command.py", line 285, in retrieve_migrations

    revision_context.run_autogenerate(rev, context)

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/api.py", line 587, in run_autogenerate

    self._run_environment(rev, migration_context, True)

    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/api.py", line 634, in _run_environment

    compare._populate_migration_script(

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

        autogen_context, migration_script

        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    )

    ^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/__init__.py", line 39, in _populate_migration_script

    _produce_net_changes(autogen_context, upgrade_ops)

    ~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/__init__.py", line 48, in _produce_net_changes

    autogen_context.comparators.dispatch(

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        "autogenerate", qualifier=autogen_context.dialect.name

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    )(autogen_context, upgrade_ops)

    ~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/langhelpers.py", line 420, in go

    result = fn(*arg, **kw)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/schema.py", line 51, in _produce_net_changes

    autogen_context.comparators.dispatch(

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        "schema", qualifier=autogen_context.dialect.name

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    )(autogen_context, upgrade_ops, schemas)

    ~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/util/langhelpers.py", line 420, in go

    result = fn(*arg, **kw)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/tables.py", line 69, in _autogen_for_tables

    insp.pre_cache_tables(schema_name, tablenames, available)

    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/util.py", line 275, in pre_cache_tables

    self._pre_cache(

    ~~~~~~~~~~~~~~~^

        schema,

        ^^^^^^^

    ...<3 lines>...

        meth,

        ^^^^^

    )

    ^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/alembic/autogenerate/compare/util.py", line 169, in _pre_cache

    elements = inspector_method(

        schema=schema, filter_names=optimized_filter_names

    )

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/sqlalchemy/engine/reflection.py", line 933, in get_multi_columns

    self.dialect.get_multi_columns(

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

        conn,

        ^^^^^

    ...<5 lines>...

        **kw,

        ^^^^^

    )

    ^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 185, in wrapper

    return function(self, connection, *args, **kwargs)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/sqlalchemy_spanner/sqlalchemy_spanner.py", line 1152, in get_multi_columns

    columns = list(snap.execute_sql(sql))

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/streamed.py", line 177, in __iter__

    self._consume_next()

    ~~~~~~~~~~~~~~~~~~^^

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/streamed.py", line 154, in _consume_next

    response = self._response_iterator.__next__()

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/snapshot.py", line 179, in _restart_on_unavailable

    raise _augment_error_with_request_id(exc, current_request_id)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/snapshot.py", line 135, in _restart_on_unavailable

    iterator = CrossSync._Sync_Impl.run_if_async(

        method, request=request, metadata=call_metadata

    )

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/aio/_cross_sync/cross_sync.py", line 336, in run_if_async

    return func(*args, **kwargs)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/cloud/spanner_v1/services/spanner/client.py", line 1540, in execute_streaming_sql

    response = rpc(

        request,

    ...<2 lines>...

        metadata=metadata,

    )

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/gapic_v1/method.py", line 128, in __call__

    return wrapped_func(*args, **kwargs)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/timeout.py", line 130, in func_with_timeout

    return func(*args, **kwargs)

  File "/Users/busunkim/Library/Caches/pypoetry/virtualenvs/spanner-reference-JLA5SxLo-py3.14/lib/python3.14/site-packages/google/api_core/grpc_helpers.py", line 153, in error_remapped_callable

    raise exceptions.from_grpc_error(exc) from exc

google.api_core.exceptions.InvalidArgument: 400 Syntax error: Unexpected \")\" [at 9:18]\n                ()  AND \n                 ^, request_id = 1.10489449469928568989.1.1.6.1 [locale: "en-US"

message: "Syntax error: Unexpected \")\" [at 9:18]\n                ()  AND \n                 ^"

]

Failed to export metrics to Cloud Monitoring: 400 One or more TimeSeries could not be written: timeSeries[8] (metric.type="spanner.googleapis.com/internal/client/operation_latencies", metric.labels={"client_name": "spanner-python/3.68.0", "status": "", "client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@[email protected]"}, resource.type="spanner_instance_client", resource.labels={"location": "global", "instance_config": "unknown", "client_hash": "00023f"}): the set of resource labels is incomplete, missing (instance_id); timeSeries[4] (metric.type="spanner.googleapis.com/internal/client/operation_count", metric.labels={"client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@[email protected]", "client_name": "spanner-python/3.68.0", "status": ""}, resource.type="spanner_instance_client", resource.labels={"client_hash": "00023f", "instance_config": "unknown", "location": "global"}): the set of resource labels is incomplete, missing (instance_id); timeSeries[12] (metric.type="spanner.googleapis.com/internal/client/attempt_count", metric.labels={"client_name": "spanner-python/3.68.0", "client_uid": "670026d9-5e8e-4f3c-ac19-f983e7c17a1d@[email protected]"}, resource.type="spanner_instance_client", resource.labels={"client_hash": "00023f", "instance_config": "unknown", "location": "global"}): the set of resource labels is incomplete, missing (instance_id) [type_url: "type.googleapis.com/google.monitoring.v3.CreateTimeSeriesSummary"

value: "\010\017\020\014\032\006\n\002\010\003\020\003"

]
Reproduction steps: expected results

Successful Alembic migration with a new file in migrations/ that can be applied through alembic upgrade head

OS & version + platform

macOS Tahoe 26.5.1

Python environment

3.14.6

Python dependencies
Package                            Version
---------------------------------- ---------
alembic                            1.18.5
ast_serialize                      0.5.0
black                              26.5.1
certifi                            2026.6.17
cffi                               2.0.0
charset-normalizer                 3.4.7
click                              8.4.2
cryptography                       49.0.0
flake8                             7.3.0
google-api-core                    2.31.0
google-auth                        2.55.0
google-cloud-core                  2.6.0
google-cloud-monitoring            2.31.0
google-cloud-spanner               3.69.0
googleapis-common-protos           1.75.0
grpc-google-iam-v1                 0.14.4
grpc-interceptor                   0.15.4
grpcio                             1.81.1
grpcio-status                      1.81.1
idna                               3.18
iniconfig                          2.3.0
librt                              0.11.0
Mako                               1.3.12
MarkupSafe                         3.0.3
mccabe                             0.7.0
mmh3                               5.2.1
mypy                               2.1.0
mypy_extensions                    1.1.0
opentelemetry-api                  1.43.0
opentelemetry-resourcedetector-gcp 1.12.0a0
opentelemetry-sdk                  1.43.0
opentelemetry-semantic-conventions 0.64b0
packaging                          26.2
pathspec                           1.1.1
pip                                26.1.2
platformdirs                       4.10.0
pluggy                             1.6.0
proto-plus                         1.28.0
protobuf                           7.35.1
pyasn1                             0.6.3
pyasn1_modules                     0.4.2
pycodestyle                        2.14.0
pycparser                          3.0
pyflakes                           3.4.0
Pygments                           2.20.0
pytest                             9.1.1
pytokens                           0.4.1
requests                           2.34.2
SQLAlchemy                         2.0.51
sqlalchemy-spanner                 1.19.0
sqlparse                           0.5.5
typing_extensions                  4.15.0
urllib3                            2.7.0
Additional context

I also have an active Google Cloud support ticket, but was instructed to also open an issue in this repository. The support engineer was able to reproduce the issue and provided some workarounds. I've included those in a separate branch https://github.com/cdco-busunkim/spanner-sqlalchemy-issue/tree/with-workarounds. The diff is in https://github.com/cdco-busunkim/spanner-sqlalchemy-issue/pull/1.

Here is the analysis from the support engineer about the two issues discovered:

As we discussed, there are two issues chained together. First, the dialect fails with a syntax error when inspecting a completely empty database. We can bypass this by ensuring a table exists. However, when inspecting that existing table, the dialect currently omits the "name" tag in the Primary Key dictionary. By placing a small snippet of Python code in your Alembic setup file, we can intercept the dialect's response on the fly, inject the missing "name": None tag, and deliver the corrected dictionary to Alembic so it can proceed without crashing.

Step-by-Step Implementation:

Step 1: Bypass the initial empty-database Syntax Error

For your very first migration, the database cannot be completely empty, or you will hit the initial WHERE () AND syntax error.
Manually create a temporary table in your Spanner database via your Cloud Console (e.g., CREATE TABLE dummy_table (id INT64) PRIMARY KEY(id);).

Step 2: Apply the patch in env.py

Now that the database is not empty, Alembic will try to read the dummy_table and will hit the KeyError. To fix this on the fly, open your env.py file. Right at the top, immediately after your initial import statements and before the config = context.config line, please paste the following code block:

# --- sqlalchemy-spanner workaround for KeyError: 'name' ---
from google.cloud.sqlalchemy_spanner.sqlalchemy_spanner import SpannerDialect


original_get_multi_pk = SpannerDialect.get_multi_pk_constraint


def patched_get_multi_pk_constraint(self, connection, **kw):
    """Intercepts the PK constraint dictionary to inject the missing 'name' key."""
    result = original_get_multi_pk(self, connection, **kw)
    for key, pk_dict in result.items():
        if "name" not in pk_dict:
            pk_dict["name"] = None  
    return result


SpannerDialect.get_multi_pk_constraint = patched_get_multi_pk_constraint
# --- End of workaround ---
主要言語
Python
スター
5.4k
フォーク
1.8k
平均マージ
2日 20時間
マージ済み PR(30日)
140

環境構築

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

googleapis/google-cloud-python のほかの issue

googleapis/google-cloud-python の issue をすべて見る

似ている issue

Python の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。