#!/usr/bin/env python

# Copyright 2016 Google, Inc.

#

# Licensed under the Apache License, Version 2.0 (the "License");

# you may not use this file except in compliance with the License.

# You may obtain a copy of the License at

#

# http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing, software

# distributed under the License is distributed on an "AS IS" BASIS,

# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

# See the License for the specific language governing permissions and

# limitations under the License.

"""This application demonstrates how to do basic operations using Cloud

Spanner.

For more information, see the README.rst under /spanner.

"""

import argparse

import base64

import datetime

import decimal

import json

import logging

import time

from google.cloud import spanner

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

from google.cloud.spanner_v1 import DirectedReadOptions, param_types

from google.cloud.spanner_v1.data_types import JsonObject

from google.protobuf import field_mask_pb2 # type: ignore

from google.protobuf import struct_pb2 # type: ignore

from testdata import singer_pb2

OPERATION_TIMEOUT_SECONDS = 240

# [START spanner_create_instance]

def create_instance(instance_id):

"""Creates an instance."""

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

spanner_client = spanner.Client()

config_name = "{}/instanceConfigs/regional-us-central1".format(

spanner_client.project_name

)

operation = spanner_client.instance_admin_api.create_instance(

parent=spanner_client.project_name,

instance_id=instance_id,

instance=spanner_instance_admin.Instance(

config=config_name,

display_name="This is a display name.",

node_count=1,

labels={

"cloud_spanner_samples": "true",

"sample_name": "snippets-create_instance-explicit",

"created": str(int(time.time())),

},

edition=spanner_instance_admin.Instance.Edition.STANDARD, # Optional

),

)

print("Waiting for operation to complete...")

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Created instance {}".format(instance_id))

# [END spanner_create_instance]

# [START spanner_update_instance]

def update_instance(instance_id):

"""Updates an instance."""

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

spanner_client = spanner.Client()

name = "{}/instances/{}".format(spanner_client.project_name, instance_id)

operation = spanner_client.instance_admin_api.update_instance(

instance=spanner_instance_admin.Instance(

name=name,

labels={

"sample_name": "snippets-update_instance-explicit",

},

edition=spanner_instance_admin.Instance.Edition.ENTERPRISE, # Optional

),

field_mask=field_mask_pb2.FieldMask(paths=["labels", "edition"]),

)

print("Waiting for operation to complete...")

operation.result(900)

print("Updated instance {}".format(instance_id))

# [END spanner_update_instance]

# [START spanner_create_instance_with_processing_units]

def create_instance_with_processing_units(instance_id, processing_units):

"""Creates an instance."""

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

spanner_client = spanner.Client()

config_name = "{}/instanceConfigs/regional-us-central1".format(

spanner_client.project_name

)

request = spanner_instance_admin.CreateInstanceRequest(

parent=spanner_client.project_name,

instance_id=instance_id,

instance=spanner_instance_admin.Instance(

config=config_name,

display_name="This is a display name.",

processing_units=processing_units,

labels={

"cloud_spanner_samples": "true",

"sample_name": "snippets-create_instance_with_processing_units",

"created": str(int(time.time())),

},

edition=spanner_instance_admin.Instance.Edition.ENTERPRISE_PLUS,

),

)

operation = spanner_client.instance_admin_api.create_instance(request=request)

print("Waiting for operation to complete...")

instance = operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Created instance {} with {} processing units".format(

instance_id, instance.processing_units

)

)

# [END spanner_create_instance_with_processing_units]

# [START spanner_get_instance_config]

def get_instance_config(instance_config):

"""Gets the leader options for the instance configuration."""

spanner_client = spanner.Client()

config_name = "{}/instanceConfigs/{}".format(

spanner_client.project_name, instance_config

)

config = spanner_client.instance_admin_api.get_instance_config(name=config_name)

print(

"Available leader options for instance config {}: {}".format(

instance_config, config.leader_options

)

)

# [END spanner_get_instance_config]

# [START spanner_list_instance_configs]

def list_instance_config():

"""Lists the available instance configurations."""

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

spanner_client = spanner.Client()

request = spanner_instance_admin.ListInstanceConfigsRequest(

parent=spanner_client.project_name

)

for config in spanner_client.instance_admin_api.list_instance_configs(

request=request

):

print(

"Available leader options for instance config {}: {}".format(

config.name, config.leader_options

)

)

# [END spanner_list_instance_configs]

# [START spanner_create_instance_partition]

def create_instance_partition(instance_id, instance_partition_id):

"""Creates an instance partition."""

from google.cloud.spanner_admin_instance_v1.types import spanner_instance_admin

spanner_client = spanner.Client()

instance_admin_api = spanner_client.instance_admin_api

config_name = "{}/instanceConfigs/nam3".format(spanner_client.project_name)

operation = spanner_client.instance_admin_api.create_instance_partition(

parent=instance_admin_api.instance_path(spanner_client.project, instance_id),

instance_partition_id=instance_partition_id,

instance_partition=spanner_instance_admin.InstancePartition(

config=config_name,

display_name="Test instance partition",

node_count=1,

),

)

print("Waiting for operation to complete...")

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Created instance partition {}".format(instance_partition_id))

# [END spanner_create_instance_partition]

# [START spanner_list_databases]

def list_databases(instance_id):

"""Lists databases and their leader options."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.ListDatabasesRequest(

parent=database_admin_api.instance_path(spanner_client.project, instance_id)

)

for database in database_admin_api.list_databases(request=request):

print(

"Database {} has default leader {}".format(

database.name, database.default_leader

)

)

# [END spanner_list_databases]

# [START spanner_create_database]

def create_database(instance_id, database_id):

"""Creates a database and tables for sample data."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.CreateDatabaseRequest(

parent=database_admin_api.instance_path(spanner_client.project, instance_id),

create_statement=f"CREATE DATABASE `{database_id}`",

extra_statements=[

"""CREATE TABLE Singers (

SingerId INT64 NOT NULL,

FirstName STRING(1024),

LastName STRING(1024),

SingerInfo BYTES(MAX),

FullName STRING(2048) AS (

ARRAY_TO_STRING([FirstName, LastName], " ")

) STORED

) PRIMARY KEY (SingerId)""",

"""CREATE TABLE Albums (

SingerId INT64 NOT NULL,

AlbumId INT64 NOT NULL,

AlbumTitle STRING(MAX)

) PRIMARY KEY (SingerId, AlbumId),

INTERLEAVE IN PARENT Singers ON DELETE CASCADE""",

],

)

operation = database_admin_api.create_database(request=request)

print("Waiting for operation to complete...")

database = operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Created database {} on instance {}".format(

database.name,

database_admin_api.instance_path(spanner_client.project, instance_id),

)

)

# [END spanner_create_database]

# [START spanner_update_database]

def update_database(instance_id, database_id):

"""Updates the drop protection setting for a database."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.UpdateDatabaseRequest(

database=spanner_database_admin.Database(

name=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

),

enable_drop_protection=True,

),

update_mask={"paths": ["enable_drop_protection"]},

)

operation = database_admin_api.update_database(request=request)

print(

"Waiting for update operation for {}/databases/{} to complete...".format(

database_admin_api.instance_path(spanner_client.project, instance_id),

database_id,

)

)

operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Updated database {}/databases/{}.".format(

database_admin_api.instance_path(spanner_client.project, instance_id),

database_id,

)

)

# [END spanner_update_database]

# [START spanner_create_database_with_encryption_key]

def create_database_with_encryption_key(instance_id, database_id, kms_key_name):

"""Creates a database with tables using a Customer Managed Encryption Key (CMEK)."""

from google.cloud.spanner_admin_database_v1 import EncryptionConfig

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.CreateDatabaseRequest(

parent=database_admin_api.instance_path(spanner_client.project, instance_id),

create_statement=f"CREATE DATABASE `{database_id}`",

extra_statements=[

"""CREATE TABLE Singers (

SingerId INT64 NOT NULL,

FirstName STRING(1024),

LastName STRING(1024),

SingerInfo BYTES(MAX)

) PRIMARY KEY (SingerId)""",

"""CREATE TABLE Albums (

SingerId INT64 NOT NULL,

AlbumId INT64 NOT NULL,

AlbumTitle STRING(MAX)

) PRIMARY KEY (SingerId, AlbumId),

INTERLEAVE IN PARENT Singers ON DELETE CASCADE""",

],

encryption_config=EncryptionConfig(kms_key_name=kms_key_name),

)

operation = database_admin_api.create_database(request=request)

print("Waiting for operation to complete...")

database = operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Database {} created with encryption key {}".format(

database.name, database.encryption_config.kms_key_name

)

)

# [END spanner_create_database_with_encryption_key]

# [START spanner_create_database_with_MR_CMEK]

def create_database_with_multiple_kms_keys(instance_id, database_id, kms_key_names):

"""Creates a database with tables using multiple KMS keys(CMEK)."""

from google.cloud.spanner_admin_database_v1 import EncryptionConfig

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.CreateDatabaseRequest(

parent=database_admin_api.instance_path(spanner_client.project, instance_id),

create_statement=f"CREATE DATABASE `{database_id}`",

extra_statements=[

"""CREATE TABLE Singers (

SingerId INT64 NOT NULL,

FirstName STRING(1024),

LastName STRING(1024),

SingerInfo BYTES(MAX)

) PRIMARY KEY (SingerId)""",

"""CREATE TABLE Albums (

SingerId INT64 NOT NULL,

AlbumId INT64 NOT NULL,

AlbumTitle STRING(MAX)

) PRIMARY KEY (SingerId, AlbumId),

INTERLEAVE IN PARENT Singers ON DELETE CASCADE""",

],

encryption_config=EncryptionConfig(kms_key_names=kms_key_names),

)

operation = database_admin_api.create_database(request=request)

print("Waiting for operation to complete...")

database = operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Database {} created with multiple KMS keys {}".format(

database.name, database.encryption_config.kms_key_names

)

)

# [END spanner_create_database_with_MR_CMEK]

# [START spanner_create_database_with_default_leader]

def create_database_with_default_leader(instance_id, database_id, default_leader):

"""Creates a database with tables with a default leader."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.CreateDatabaseRequest(

parent=database_admin_api.instance_path(spanner_client.project, instance_id),

create_statement=f"CREATE DATABASE `{database_id}`",

extra_statements=[

"""CREATE TABLE Singers (

SingerId INT64 NOT NULL,

FirstName STRING(1024),

LastName STRING(1024),

SingerInfo BYTES(MAX)

) PRIMARY KEY (SingerId)""",

"""CREATE TABLE Albums (

SingerId INT64 NOT NULL,

AlbumId INT64 NOT NULL,

AlbumTitle STRING(MAX)

) PRIMARY KEY (SingerId, AlbumId),

INTERLEAVE IN PARENT Singers ON DELETE CASCADE""",

"ALTER DATABASE {}"

" SET OPTIONS (default_leader = '{}')".format(database_id, default_leader),

],

)

operation = database_admin_api.create_database(request=request)

print("Waiting for operation to complete...")

database = operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Database {} created with default leader {}".format(

database.name, database.default_leader

)

)

# [END spanner_create_database_with_default_leader]

# [START spanner_update_database_with_default_leader]

def update_database_with_default_leader(instance_id, database_id, default_leader):

"""Updates a database with tables with a default leader."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.UpdateDatabaseDdlRequest(

database=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

),

statements=[

"ALTER DATABASE {}"

" SET OPTIONS (default_leader = '{}')".format(database_id, default_leader)

],

)

operation = database_admin_api.update_database_ddl(request)

operation.result(OPERATION_TIMEOUT_SECONDS)

print(

"Database {} updated with default leader {}".format(database_id, default_leader)

)

# [END spanner_update_database_with_default_leader]

# [START spanner_get_database_ddl]

def get_database_ddl(instance_id, database_id):

"""Gets the database DDL statements."""

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

ddl = database_admin_api.get_database_ddl(

database=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

)

)

print("Retrieved database DDL for {}".format(database_id))

for statement in ddl.statements:

print(statement)

# [END spanner_get_database_ddl]

# [START spanner_query_information_schema_database_options]

def query_information_schema_database_options(instance_id, database_id):

"""Queries the default leader of a database."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

results = snapshot.execute_sql(

"SELECT OPTION_VALUE AS default_leader "

"FROM INFORMATION_SCHEMA.DATABASE_OPTIONS "

"WHERE SCHEMA_NAME = '' AND OPTION_NAME = 'default_leader'"

)

for result in results:

print("Database {} has default leader {}".format(database_id, result[0]))

# [END spanner_query_information_schema_database_options]

# [START spanner_insert_data]

def insert_data(instance_id, database_id):

"""Inserts sample data into the given database.

The database and table must already exist and can be created using

`create_database`.

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.batch() as batch:

batch.insert(

table="Singers",

columns=("SingerId", "FirstName", "LastName"),

values=[

(1, "Marc", "Richards"),

(2, "Catalina", "Smith"),

(3, "Alice", "Trentor"),

(4, "Lea", "Martin"),

(5, "David", "Lomond"),

],

)

batch.insert(

table="Albums",

columns=("SingerId", "AlbumId", "AlbumTitle"),

values=[

(1, 1, "Total Junk"),

(1, 2, "Go, Go, Go"),

(2, 1, "Green"),

(2, 2, "Forever Hold Your Peace"),

(2, 3, "Terrified"),

],

)

print("Inserted data.")

# [END spanner_insert_data]

# [START spanner_batch_write_at_least_once]

def batch_write(instance_id, database_id):

"""Inserts sample data into the given database via BatchWrite API.

The database and table must already exist and can be created using

`create_database`.

"""

from google.rpc.code_pb2 import OK

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.mutation_groups() as groups:

group1 = groups.group()

group1.insert_or_update(

table="Singers",

columns=("SingerId", "FirstName", "LastName"),

values=[

(16, "Scarlet", "Terry"),

],

)

group2 = groups.group()

group2.insert_or_update(

table="Singers",

columns=("SingerId", "FirstName", "LastName"),

values=[

(17, "Marc", ""),

(18, "Catalina", "Smith"),

],

)

group2.insert_or_update(

table="Albums",

columns=("SingerId", "AlbumId", "AlbumTitle"),

values=[

(17, 1, "Total Junk"),

(18, 2, "Go, Go, Go"),

],

)

for response in groups.batch_write():

if response.status.code == OK:

print(

"Mutation group indexes {} have been applied with commit timestamp {}".format(

response.indexes, response.commit_timestamp

)

)

else:

print(

"Mutation group indexes {} could not be applied with error {}".format(

response.indexes, response.status

)

)

# [END spanner_batch_write_at_least_once]

# [START spanner_delete_data]

def delete_data(instance_id, database_id):

"""Deletes sample data from the given database.

The database, table, and data must already exist and can be created using

`create_database` and `insert_data`.

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

# Delete individual rows

albums_to_delete = spanner.KeySet(keys=[[2, 1], [2, 3]])

# Delete a range of rows where the column key is >=3 and <5

singers_range = spanner.KeyRange(start_closed=[3], end_open=[5])

singers_to_delete = spanner.KeySet(ranges=[singers_range])

# Delete remaining Singers rows, which will also delete the remaining

# Albums rows because Albums was defined with ON DELETE CASCADE

remaining_singers = spanner.KeySet(all_=True)

with database.batch() as batch:

batch.delete("Albums", albums_to_delete)

batch.delete("Singers", singers_to_delete)

batch.delete("Singers", remaining_singers)

print("Deleted data.")

# [END spanner_delete_data]

# [START spanner_query_data]

def query_data(instance_id, database_id):

"""Queries sample data from the database using SQL."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

results = snapshot.execute_sql(

"SELECT SingerId, AlbumId, AlbumTitle FROM Albums"

)

for row in results:

print("SingerId: {}, AlbumId: {}, AlbumTitle: {}".format(*row))

# [END spanner_query_data]

# [START spanner_read_data]

def read_data(instance_id, database_id):

"""Reads sample data from the database."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

keyset = spanner.KeySet(all_=True)

results = snapshot.read(

table="Albums", columns=("SingerId", "AlbumId", "AlbumTitle"), keyset=keyset

)

for row in results:

print("SingerId: {}, AlbumId: {}, AlbumTitle: {}".format(*row))

# [END spanner_read_data]

# [START spanner_read_stale_data]

def read_stale_data(instance_id, database_id):

"""Reads sample data from the database. The data is exactly 15 seconds

stale."""

import datetime

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

staleness = datetime.timedelta(seconds=15)

with database.snapshot(exact_staleness=staleness) as snapshot:

keyset = spanner.KeySet(all_=True)

results = snapshot.read(

table="Albums",

columns=("SingerId", "AlbumId", "MarketingBudget"),

keyset=keyset,

)

for row in results:

print("SingerId: {}, AlbumId: {}, MarketingBudget: {}".format(*row))

# [END spanner_read_stale_data]

# [START spanner_query_data_with_new_column]

def query_data_with_new_column(instance_id, database_id):

"""Queries sample data from the database using SQL.

This sample uses the `MarketingBudget` column. You can add the column

by running the `add_column` sample or by running this DDL statement against

your database:

ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

results = snapshot.execute_sql(

"SELECT SingerId, AlbumId, MarketingBudget FROM Albums"

)

for row in results:

print("SingerId: {}, AlbumId: {}, MarketingBudget: {}".format(*row))

# [END spanner_query_data_with_new_column]

# [START spanner_create_index]

def add_index(instance_id, database_id):

"""Adds a simple index to the example database."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.UpdateDatabaseDdlRequest(

database=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

),

statements=["CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"],

)

operation = database_admin_api.update_database_ddl(request)

print("Waiting for operation to complete...")

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Added the AlbumsByAlbumTitle index.")

# [END spanner_create_index]

# [START spanner_query_data_with_index]

def query_data_with_index(

instance_id, database_id, start_title="Aardvark", end_title="Goo"

):

"""Queries sample data from the database using SQL and an index.

The index must exist before running this sample. You can add the index

by running the `add_index` sample or by running this DDL statement against

your database:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

This sample also uses the `MarketingBudget` column. You can add the column

by running the `add_column` sample or by running this DDL statement against

your database:

ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

params = {"start_title": start_title, "end_title": end_title}

param_types = {

"start_title": spanner.param_types.STRING,

"end_title": spanner.param_types.STRING,

}

with database.snapshot() as snapshot:

results = snapshot.execute_sql(

"SELECT AlbumId, AlbumTitle, MarketingBudget "

"FROM Albums@{FORCE_INDEX=AlbumsByAlbumTitle} "

"WHERE AlbumTitle >= @start_title AND AlbumTitle < @end_title",

params=params,

param_types=param_types,

)

for row in results:

print("AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

# [END spanner_query_data_with_index]

# [START spanner_read_data_with_index]

def read_data_with_index(instance_id, database_id):

"""Reads sample data from the database using an index.

The index must exist before running this sample. You can add the index

by running the `add_index` sample or by running this DDL statement against

your database:

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

keyset = spanner.KeySet(all_=True)

results = snapshot.read(

table="Albums",

columns=("AlbumId", "AlbumTitle"),

keyset=keyset,

index="AlbumsByAlbumTitle",

)

for row in results:

print("AlbumId: {}, AlbumTitle: {}".format(*row))

# [END spanner_read_data_with_index]

# [START spanner_create_storing_index]

def add_storing_index(instance_id, database_id):

"""Adds an storing index to the example database."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.UpdateDatabaseDdlRequest(

database=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

),

statements=[

"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)"

"STORING (MarketingBudget)"

],

)

operation = database_admin_api.update_database_ddl(request)

print("Waiting for operation to complete...")

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Added the AlbumsByAlbumTitle2 index.")

# [END spanner_create_storing_index]

# [START spanner_read_data_with_storing_index]

def read_data_with_storing_index(instance_id, database_id):

"""Reads sample data from the database using an index with a storing

clause.

The index must exist before running this sample. You can add the index

by running the `add_scoring_index` sample or by running this DDL statement

against your database:

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)

STORING (MarketingBudget)

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot() as snapshot:

keyset = spanner.KeySet(all_=True)

results = snapshot.read(

table="Albums",

columns=("AlbumId", "AlbumTitle", "MarketingBudget"),

keyset=keyset,

index="AlbumsByAlbumTitle2",

)

for row in results:

print("AlbumId: {}, AlbumTitle: {}, " "MarketingBudget: {}".format(*row))

# [END spanner_read_data_with_storing_index]

# [START spanner_add_column]

def add_column(instance_id, database_id):

"""Adds a new column to the Albums table in the example database."""

from google.cloud.spanner_admin_database_v1.types import spanner_database_admin

spanner_client = spanner.Client()

database_admin_api = spanner_client.database_admin_api

request = spanner_database_admin.UpdateDatabaseDdlRequest(

database=database_admin_api.database_path(

spanner_client.project, instance_id, database_id

),

statements=[

"ALTER TABLE Albums ADD COLUMN MarketingBudget INT64",

],

)

operation = database_admin_api.update_database_ddl(request)

print("Waiting for operation to complete...")

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Added the MarketingBudget column.")

# [END spanner_add_column]

# [START spanner_update_data]

def update_data(instance_id, database_id):

"""Updates sample data in the database.

This updates the `MarketingBudget` column which must be created before

running this sample. You can add the column by running the `add_column`

sample or by running this DDL statement against your database:

ALTER TABLE Albums ADD COLUMN MarketingBudget INT64

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.batch() as batch:

batch.update(

table="Albums",

columns=("SingerId", "AlbumId", "MarketingBudget"),

values=[(1, 1, 100000), (2, 2, 500000)],

)

print("Updated data.")

# [END spanner_update_data]

# [START spanner_read_write_transaction]

def read_write_transaction(instance_id, database_id):

"""Performs a read-write transaction to update two sample records in the

database.

This will transfer 200,000 from the `MarketingBudget` field for the second

Album to the first Album. If the `MarketingBudget` is too low, it will

raise an exception.

Before running this sample, you will need to run the `update_data` sample

to populate the fields.

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

def update_albums(transaction):

# Read the second album budget.

second_album_keyset = spanner.KeySet(keys=[(2, 2)])

second_album_result = transaction.read(

table="Albums",

columns=("MarketingBudget",),

keyset=second_album_keyset,

limit=1,

)

second_album_row = list(second_album_result)[0]

second_album_budget = second_album_row[0]

transfer_amount = 200000

if second_album_budget < transfer_amount: