{"id":1028,"library":"sqlalchemy-spanner","title":"SQLAlchemy dialect integrated into Cloud Spanner database","description":"sqlalchemy-spanner is a Python SQLAlchemy dialect that enables applications to connect to and interact with Google Cloud Spanner databases. It leverages Cloud Spanner's scale, strong consistency, and high availability. The library is actively maintained by Google and sees regular releases with new features and bug fixes.","status":"active","version":"1.17.3","language":"python","source_language":"en","source_url":"https://github.com/googleapis/python-spanner-sqlalchemy","tags":["SQLAlchemy","Google Cloud Spanner","ORM","database","dialect","cloud"],"install":[{"cmd":"pip install sqlalchemy-spanner","lang":"bash","label":"Install stable version"}],"dependencies":[{"reason":"This package provides a dialect for SQLAlchemy, requiring SQLAlchemy itself. Compatible with SQLAlchemy 1.x (>=1.1.13) and 2.0.","package":"SQLAlchemy","optional":false},{"reason":"The dialect is built on top of the Google Cloud Spanner DB API (PEP-249 compliant).","package":"google-cloud-spanner","optional":false},{"reason":"Recommended for database migrations with SQLAlchemy and Spanner.","package":"Alembic","optional":true}],"imports":[{"note":"The Spanner dialect is typically loaded via the connection string, not direct import of a dialect class.","symbol":"create_engine","correct":"from sqlalchemy import create_engine"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text, MetaData, Table, Column, String, Integer\n\n# Set these environment variables or replace directly\nproject_id = os.environ.get('SPANNER_PROJECT_ID', 'your-gcp-project-id')\ninstance_id = os.environ.get('SPANNER_INSTANCE_ID', 'your-spanner-instance-id')\ndatabase_id = os.environ.get('SPANNER_DATABASE_ID', 'your-spanner-database-id')\n\n# For SQLAlchemy 1.4+ and 2.0, use 'spanner+spanner:///'\n# For SQLAlchemy 1.3, use 'spanner:///'\ndb_url = f\"spanner+spanner:///projects/{project_id}/instances/{instance_id}/databases/{database_id}\"\n\ntry:\n    engine = create_engine(db_url)\n    \n    # Example: Define a table for demonstration\n    metadata = MetaData()\n    users = Table(\n        'users',\n        metadata,\n        Column('user_id', String(36), primary_key=True),\n        Column('name', String(255), nullable=False),\n        Column('age', Integer)\n    )\n    \n    # Create tables (DDL operations are not transactional in Spanner)\n    with engine.connect() as connection:\n        connection.execute(text(\"CREATE TABLE IF NOT EXISTS users (user_id STRING(36) NOT NULL, name STRING(255) NOT NULL, age INT64) PRIMARY KEY (user_id)\"))\n        connection.commit()\n        print(\"Table 'users' ensured.\")\n\n    # Insert data\n    with engine.begin() as connection:\n        connection.execute(users.insert(), {\"user_id\": \"user1\", \"name\": \"Alice\", \"age\": 30})\n        connection.execute(users.insert(), {\"user_id\": \"user2\", \"name\": \"Bob\", \"age\": 24})\n        print(\"Data inserted.\")\n\n    # Query data\n    with engine.connect() as connection:\n        result = connection.execute(users.select().where(users.c.age > 25)).fetchall()\n        print(\"Users older than 25:\", result)\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Ensure Cloud Spanner project, instance, and database are correctly configured and `GOOGLE_APPLICATION_CREDENTIALS` is set or default credentials are available.\")","lang":"python","description":"This quickstart demonstrates how to establish a connection to a Google Cloud Spanner database, define a table, insert data, and query it using the SQLAlchemy ORM. Ensure you have a GCP project, Spanner instance, and database set up, and that your application has appropriate authentication (e.g., via `GOOGLE_APPLICATION_CREDENTIALS`). The example shows direct DDL for table creation, as DDL is not transactional in Spanner."},"warnings":[{"fix":"Adjust the connection string based on your SQLAlchemy version. E.g., `create_engine(\"spanner+spanner:///projects/...\")` for newer SQLAlchemy versions.","message":"The database URL format for `sqlalchemy-spanner` differs between SQLAlchemy 1.3 and 1.4+/2.0. For SQLAlchemy 1.3, use `spanner:///...`. For SQLAlchemy 1.4+ and 2.0, use `spanner+spanner:///...`.","severity":"gotcha","affected_versions":"<1.4.0 (for older URL style)"},{"fix":"Be mindful that DDL statements are implicitly committed and irreversible in the context of a transaction. Consider using `connection.execute(text('DDL_STATEMENT'))` followed by `connection.commit()` outside of explicit transaction blocks for DDL.","message":"Cloud Spanner does not support DDL (Data Definition Language) statements within transactions. Any DDL operations performed will not be rolled back upon transaction failure.","severity":"gotcha","affected_versions":"All"},{"fix":"Utilize SQLAlchemy's ORM or Core DML statements (INSERT, UPDATE, DELETE) which are translated into Spanner-compatible DML.","message":"The dialect and the underlying DB API driver do not support Spanner mutations directly; only DML (Data Manipulation Language) statements are supported for updates.","severity":"gotcha","affected_versions":"All"},{"fix":"In your `env.py` or Alembic configuration, ensure `version_table_pk = False` for Spanner migrations.","message":"When using Alembic for migrations, setting `version_table_pk` to `False` in your Alembic environment configuration is highly recommended to avoid issues with Spanner's primary key restrictions on the `alembic_versions` table.","severity":"gotcha","affected_versions":"All"},{"fix":"Consider using thread pools or synchronous execution within an async application, or explore third-party async wrappers. A native async SpannerSessionService is a feature request for the underlying `google-cloud-spanner` client.","message":"The `sqlalchemy-spanner` dialect is currently synchronous only. There is no official asynchronous dialect available, which can be a blocker for projects requiring an async engine with SQLAlchemy 2.0+'s async features.","severity":"gotcha","affected_versions":"All"},{"fix":"Reflect all necessary `Table` objects upfront, once, before your program begins processing requests, rather than dynamically reflecting them within hot code paths.","message":"Using `autoload=True` with `MetaData` in a highly concurrent environment can lead to `sqlalchemy.exc.CompileError: Unconsumed column names` due to race conditions during table reflection. It's an anti-pattern to reflect tables on every statement.","severity":"gotcha","affected_versions":"All"},{"fix":"Be aware of the default long timeout. For specific timeout control, you may need to apply it at the underlying `google-cloud-spanner` client level if direct access is available, or await a future update to the dialect.","message":"The dialect does not currently propagate the `timeout` execution option to the underlying gRPC client, causing all queries to use the default gRPC timeout of 3600 seconds regardless of `execution_options(timeout=...)`.","severity":"gotcha","affected_versions":"All current versions (as of 1.17.3)"},{"fix":"Set up Application Default Credentials (ADC) in your environment (e.g., `gcloud auth application-default login` if using the gcloud CLI) or ensure the `GOOGLE_APPLICATION_CREDENTIALS` environment variable points to a valid service account key file. Consult Google Cloud's authentication documentation for detailed setup instructions: https://cloud.google.com/docs/authentication/external/set-up-adc","message":"The `sqlalchemy-spanner` dialect relies on Google Cloud Application Default Credentials (ADC) or explicit credentials (e.g., via `GOOGLE_APPLICATION_CREDENTIALS`) to authenticate with Spanner. Without proper authentication, connection attempts will fail.","severity":"breaking","affected_versions":"All"},{"fix":"Ensure Application Default Credentials are set up in your environment. Refer to `https://cloud.google.com/docs/authentication/external/set-up-adc` for guidance. This typically involves setting the `GOOGLE_APPLICATION_CREDENTIALS` environment variable to the path of a service account key file or authenticating via `gcloud auth application-default login`.","message":"The `sqlalchemy-spanner` dialect, like all Google Cloud client libraries, requires Application Default Credentials (ADC) to authenticate. If not configured, you will encounter 'Your default credentials were not found' errors.","severity":"gotcha","affected_versions":"All"}],"env_vars":null,"last_verified":"2026-05-12T22:47:38.037Z","next_check":"2026-06-27T00:00:00.000Z","problems":[{"fix":"pip install sqlalchemy-spanner","cause":"The `sqlalchemy-spanner` package is not installed or not accessible in the current Python environment, preventing SQLAlchemy from finding the 'spanner' dialect.","error":"sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:spanner"},{"fix":"Verify the project ID, instance ID, and database name in your connection string are correct, and ensure the authenticated user or service account has appropriate IAM roles (e.g., `roles/spanner.databaseUser`) for the Spanner resource.","cause":"The Spanner project, instance, or database specified in the connection string does not exist, or the authenticated service account/user lacks the necessary IAM permissions to access it.","error":"sqlalchemy.exc.OperationalError: (google.api_core.exceptions.NotFound: 404 Not found: projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID)"},{"fix":"Remove `autoincrement=True` from primary key column definitions; instead, generate unique primary key values client-side (e.g., using UUIDs) before insertion, or configure Spanner's `GENERATE AS ROW ID` for `BYTES(16)` primary keys.","cause":"Cloud Spanner does not natively support auto-incrementing integer primary keys, and the `sqlalchemy-spanner` dialect prevents their definition in SQLAlchemy models.","error":"sqlalchemy.exc.CompileError: Spanner does not support auto-incrementing primary keys."},{"fix":"Remove the `.returning()` clause from your SQLAlchemy `insert()` or `update()` statements. If you need generated IDs, generate them client-side before insertion or query the table separately afterward.","cause":"The `sqlalchemy-spanner` dialect does not support the `RETURNING` clause in `INSERT` or `UPDATE` statements, as Cloud Spanner DML does not provide immediate return of affected rows or generated values.","error":"sqlalchemy.exc.CompileError: Spanner does not support the RETURNING clause."}],"ecosystem":"pypi","meta_description":null,"install_score":100,"install_tag":"verified","quickstart_score":null,"quickstart_tag":null,"pypi_latest":"1.18.0","cli_name":"","install_checks":{"last_tested":"2026-05-12","tag":"verified","tag_description":"installs cleanly on critical runtimes, fast import, recently tested","results":[{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.51,"mem_mb":15.5,"disk_size":"112.5M"},{"runtime":"python:3.10-alpine","python_version":"3.10","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.53,"mem_mb":15.5,"disk_size":"111.3M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":9.8,"import_time_s":0.38,"mem_mb":15.5,"disk_size":"108M"},{"runtime":"python:3.10-slim","python_version":"3.10","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.53,"mem_mb":15.5,"disk_size":"107M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.76,"mem_mb":17.6,"disk_size":"122.6M"},{"runtime":"python:3.11-alpine","python_version":"3.11","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.83,"mem_mb":17.6,"disk_size":"121.3M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":8.7,"import_time_s":0.68,"mem_mb":17.6,"disk_size":"118M"},{"runtime":"python:3.11-slim","python_version":"3.11","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.66,"mem_mb":17.6,"disk_size":"117M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.86,"mem_mb":17.4,"disk_size":"113.0M"},{"runtime":"python:3.12-alpine","python_version":"3.12","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.88,"mem_mb":17.4,"disk_size":"111.8M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":7.3,"import_time_s":0.81,"mem_mb":17.4,"disk_size":"109M"},{"runtime":"python:3.12-slim","python_version":"3.12","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.89,"mem_mb":17.4,"disk_size":"108M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.81,"mem_mb":17.6,"disk_size":"112.3M"},{"runtime":"python:3.13-alpine","python_version":"3.13","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.85,"mem_mb":17.6,"disk_size":"111.0M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":7.3,"import_time_s":0.75,"mem_mb":17.6,"disk_size":"108M"},{"runtime":"python:3.13-slim","python_version":"3.13","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.9,"mem_mb":17.6,"disk_size":"107M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":null,"import_time_s":0.44,"mem_mb":15.1,"disk_size":"111.5M"},{"runtime":"python:3.9-alpine","python_version":"3.9","os_libc":"alpine (musl)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.48,"mem_mb":15.1,"disk_size":"110.4M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":"wheel","failure_reason":null,"install_time_s":11.3,"import_time_s":0.42,"mem_mb":15.1,"disk_size":"108M"},{"runtime":"python:3.9-slim","python_version":"3.9","os_libc":"slim (glibc)","variant":"default","exit_code":0,"wheel_type":null,"failure_reason":null,"install_time_s":null,"import_time_s":0.43,"mem_mb":15.1,"disk_size":"107M"}]},"quickstart_checks":{"last_tested":"2026-04-24","tag":null,"tag_description":null,"results":[{"runtime":"python:3.10-alpine","exit_code":0},{"runtime":"python:3.10-slim","exit_code":0},{"runtime":"python:3.11-alpine","exit_code":0},{"runtime":"python:3.11-slim","exit_code":0},{"runtime":"python:3.12-alpine","exit_code":0},{"runtime":"python:3.12-slim","exit_code":0},{"runtime":"python:3.13-alpine","exit_code":0},{"runtime":"python:3.13-slim","exit_code":0},{"runtime":"python:3.9-alpine","exit_code":0},{"runtime":"python:3.9-slim","exit_code":0}]}}