- [Cloud Spanner product documentation](https://cloud.google.com/spanner/docs)

- [SQLAlchemy product documentation](https://www.sqlalchemy.org/)

In order to use this package, you first need to go through the following steps:

3. [Enable the Google Cloud Spanner API.](https://cloud.google.com/spanner)

4. [Setup Authentication.](https://googleapis.dev/python/google-api-core/latest/auth.html)

To install an in-development version of the package, clone its Git-repository:

During setup the dialect will be registered with entry points.

from sqlalchemy import (

Column,

metadata.create_all(engine)

from sqlalchemy import (

MetaData,

connection.execute(user.insert(), {"user_id": 1, "user_name": "Full Name"})

from sqlalchemy import MetaData, Table, create_engine, select

print(row)

SQLAlchemy uses [Alembic](https://alembic.sqlalchemy.org/en/latest/#) tool to organize database migrations.

**Warning!**

A migration script can produce a lot of DDL statements. If each of the statements are executed separately, performance issues can occur. To avoid these, it's highly recommended to use the [Alembic batch context](https://alembic.sqlalchemy.org/en/latest/batch.html) feature to pack DDL statements into groups of statements.

Cloud Spanner dialect includes two dialect-specific arguments for `Table` constructor, which help to define interleave relations:

`spanner_interleave_in` - a parent table name

`spanner_inverleave_on_delete_cascade` - a flag specifying if `ON DELETE CASCADE` statement must be used for the interleave relation

client.create(engine)

Cloud Spanner doesn't support direct UNIQUE constraints creation. In order to achieve column values uniqueness UNIQUE indexes should be used.

Instead of direct UNIQUE constraint creation:

Index("uix_1", "col1", unique=True),

)

Spanner dialect supports both `SERIALIZABLE` and `AUTOCOMMIT` isolation levels. `SERIALIZABLE` is the default one, where transactions need to be committed manually. `AUTOCOMMIT` mode corresponds to automatically committing of a query right in its execution time.

Isolation level change example:

autocommit_engine = eng.execution_options(isolation_level="AUTOCOMMIT")

By default, transactions produced by a Spanner connection are in ReadWrite mode. However, some applications require an ability to grant ReadOnly access to users/methods; for these cases Spanner dialect supports the `read_only` execution option, which switches a connection into ReadOnly mode:

with engine.connect().execution_options(read_only=True) as connection:

ReadOnly/ReadWrite mode of a connection can't be changed while a transaction is in progress - first you must commit or rollback it.

DDL statements are executed outside the regular transactions mechanism, which means DDL statements will not be rolled back on normal transaction rollback.

Cloud Spanner, by default, doesn't drop tables, which have secondary indexes and/or foreign key constraints. In Spanner dialect for SQLAlchemy, however, this restriction is omitted - if a table you are trying to delete has indexes/foreign keys, they will be dropped automatically right before dropping the table.

Data types table mapping SQLAlchemy types to Cloud Spanner types:

| SQLAlchemy | Spanner |

| NUMERIC | NUMERIC |

- WITH RECURSIVE statement is not supported.

- Named schemas are not supported.

- Temporary tables are not supported, real tables are used instead.

- Numeric type dimensions (scale and precision) are constant. See the [docs](https://cloud.google.com/spanner/docs/data-types#numeric_types).

When a SQLAlchemy function is called, a new connection to a database is established and a Spanner session object is fetched. In case of connectionless execution these fetches are done for every `execute()` call, which can cause a significant latency. To avoid initiating a Spanner session on every `execute()` call it's recommended to write code in connection-bounded fashion. Once a `Connection()` object is explicitly initiated, it fetches a Spanner session object and uses it for all the following calls made on this `Connection()` object.

Non-optimal connectionless use:

Connectionless way of use is also deprecated since SQLAlchemy 2.0 and soon will be removed (see in [SQLAlchemy docs](https://docs.sqlalchemy.org/en/14/core/connections.html#connectionless-execution-implicit-execution)).

Spanner dialect includes a compliance, migration and unit test suite. To run the tests the `nox` package commands can be used:

The dialect test suite can be runned on [Spanner emulator](https://cloud.google.com/spanner/docs/emulator). Several tests, relating to `NULL` values of data types, are skipped when executed on emulator.

Contributions to this library are welcome and encouraged. Please report issues, file feature requests, and send pull requests. See [CONTRIBUTING](https://github.com/cloudspannerecosystem/python-spanner-sqlalchemy/blob/main/contributing.md) for more information on how to get

started.