SQLAlchemy dialect integrated into Cloud Spanner database

raw JSON →
1.17.3 verified Tue May 12 auth: no python install: verified

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.

pip install sqlalchemy-spanner

Common errors

error sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:spanner ↓
cause The `sqlalchemy-spanner` package is not installed or not accessible in the current Python environment, preventing SQLAlchemy from finding the 'spanner' dialect.
fix
pip install sqlalchemy-spanner
error sqlalchemy.exc.OperationalError: (google.api_core.exceptions.NotFound: 404 Not found: projects/PROJECT_ID/instances/INSTANCE_ID/databases/DATABASE_ID) ↓
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.
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.
error sqlalchemy.exc.CompileError: Spanner does not support auto-incrementing 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.
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.
error sqlalchemy.exc.CompileError: Spanner does not support the RETURNING clause. ↓
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.
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.

Warnings

gotcha 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:///...`. ↓
fix Adjust the connection string based on your SQLAlchemy version. E.g., `create_engine("spanner+spanner:///projects/...")` for newer SQLAlchemy versions.
gotcha Cloud Spanner does not support DDL (Data Definition Language) statements within transactions. Any DDL operations performed will not be rolled back upon transaction failure. ↓
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.
gotcha The dialect and the underlying DB API driver do not support Spanner mutations directly; only DML (Data Manipulation Language) statements are supported for updates. ↓
fix Utilize SQLAlchemy's ORM or Core DML statements (INSERT, UPDATE, DELETE) which are translated into Spanner-compatible DML.
gotcha 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. ↓
fix In your `env.py` or Alembic configuration, ensure `version_table_pk = False` for Spanner migrations.
gotcha 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. ↓
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.
gotcha 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. ↓
fix Reflect all necessary `Table` objects upfront, once, before your program begins processing requests, rather than dynamically reflecting them within hot code paths.
gotcha 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=...)`. ↓
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.
breaking 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. ↓
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
gotcha 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. ↓
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`.

Install compatibility verified last tested: 2026-05-12

python os / libc status wheel install import disk
3.10 alpine (musl) wheel - 0.51s 112.5M
3.10 alpine (musl) - - 0.53s 111.3M
3.10 slim (glibc) wheel 9.8s 0.38s 108M
3.10 slim (glibc) - - 0.53s 107M
3.11 alpine (musl) wheel - 0.76s 122.6M
3.11 alpine (musl) - - 0.83s 121.3M
3.11 slim (glibc) wheel 8.7s 0.68s 118M
3.11 slim (glibc) - - 0.66s 117M
3.12 alpine (musl) wheel - 0.86s 113.0M
3.12 alpine (musl) - - 0.88s 111.8M
3.12 slim (glibc) wheel 7.3s 0.81s 109M
3.12 slim (glibc) - - 0.89s 108M
3.13 alpine (musl) wheel - 0.81s 112.3M
3.13 alpine (musl) - - 0.85s 111.0M
3.13 slim (glibc) wheel 7.3s 0.75s 108M
3.13 slim (glibc) - - 0.90s 107M
3.9 alpine (musl) wheel - 0.44s 111.5M
3.9 alpine (musl) - - 0.48s 110.4M
3.9 slim (glibc) wheel 11.3s 0.42s 108M
3.9 slim (glibc) - - 0.43s 107M

Imports

Quickstart last tested: 2026-04-24

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.

import os
from sqlalchemy import create_engine, text, MetaData, Table, Column, String, Integer

# Set these environment variables or replace directly
project_id = os.environ.get('SPANNER_PROJECT_ID', 'your-gcp-project-id')
instance_id = os.environ.get('SPANNER_INSTANCE_ID', 'your-spanner-instance-id')
database_id = os.environ.get('SPANNER_DATABASE_ID', 'your-spanner-database-id')

# For SQLAlchemy 1.4+ and 2.0, use 'spanner+spanner:///'
# For SQLAlchemy 1.3, use 'spanner:///'
db_url = f"spanner+spanner:///projects/{project_id}/instances/{instance_id}/databases/{database_id}"

try:
    engine = create_engine(db_url)
    
    # Example: Define a table for demonstration
    metadata = MetaData()
    users = Table(
        'users',
        metadata,
        Column('user_id', String(36), primary_key=True),
        Column('name', String(255), nullable=False),
        Column('age', Integer)
    )
    
    # Create tables (DDL operations are not transactional in Spanner)
    with engine.connect() as connection:
        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)"))
        connection.commit()
        print("Table 'users' ensured.")

    # Insert data
    with engine.begin() as connection:
        connection.execute(users.insert(), {"user_id": "user1", "name": "Alice", "age": 30})
        connection.execute(users.insert(), {"user_id": "user2", "name": "Bob", "age": 24})
        print("Data inserted.")

    # Query data
    with engine.connect() as connection:
        result = connection.execute(users.select().where(users.c.age > 25)).fetchall()
        print("Users older than 25:", result)

except Exception as e:
    print(f"An error occurred: {e}")
    print("Ensure Cloud Spanner project, instance, and database are correctly configured and `GOOGLE_APPLICATION_CREDENTIALS` is set or default credentials are available.")
last verified Tue May 12
next check Sat Jun 27