#!/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_v1 import param_types

OPERATION_TIMEOUT_SECONDS = 240

# [START spanner_create_instance]

def create_instance(instance_id):

"""Creates an instance."""

spanner_client = spanner.Client()

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

spanner_client.project_name

)

instance = spanner_client.instance(

instance_id,

configuration_name=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())),

},

)

operation = instance.create()

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

operation.result(OPERATION_TIMEOUT_SECONDS)

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

# [END spanner_create_instance]

# [START spanner_create_instance_with_processing_units]

def create_instance_with_processing_units(instance_id, processing_units):

"""Creates an instance."""

spanner_client = spanner.Client()

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

spanner_client.project_name

)

instance = spanner_client.instance(

instance_id,

configuration_name=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())),

},

)

operation = instance.create()

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

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."""

spanner_client = spanner.Client()

configs = spanner_client.list_instance_configs()

for config in configs:

print(

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

config.name, config.leader_options

)

)

# [END spanner_list_instance_configs]

# [START spanner_list_databases]

def list_databases(instance_id):

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

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

databases = list(instance.list_databases())

for database in databases:

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."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(

database_id,

ddl_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""",

],

)

operation = database.create()

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

operation.result(OPERATION_TIMEOUT_SECONDS)

print("Created database {} on instance {}".format(database_id, instance_id))

# [END spanner_create_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)."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(

database_id,

ddl_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={"kms_key_name": kms_key_name},

)

operation = database.create()

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

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_default_leader]

def create_database_with_default_leader(instance_id, database_id, default_leader):

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

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(

database_id,

ddl_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.create()

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

operation.result(OPERATION_TIMEOUT_SECONDS)

database.reload()

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."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

[

"ALTER DATABASE {}"

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

]

)

operation.result(OPERATION_TIMEOUT_SECONDS)

database.reload()

print(

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

database.name, database.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()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

ddl = spanner_client.database_admin_api.get_database_ddl(database=database.name)

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, u"Marc", u"Richards"),

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

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

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

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

],

)

batch.insert(

table="Albums",

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

values=[

(1, 1, u"Total Junk"),

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

(2, 1, u"Green"),

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

(2, 3, u"Terrified"),

],

)

print("Inserted data.")

# [END spanner_insert_data]

# [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(u"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(u"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(u"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(u"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."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

["CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"]

)

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(u"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."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

[

"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle)"

"STORING (MarketingBudget)"

]

)

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_soring_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(u"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."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

["ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"]

)

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:

# Raising an exception will automatically roll back the

# transaction.

raise ValueError("The second album doesn't have enough funds to transfer")

# Read the first album's budget.

first_album_keyset = spanner.KeySet(keys=[(1, 1)])

first_album_result = transaction.read(

table="Albums",

columns=("MarketingBudget",),

keyset=first_album_keyset,

limit=1,

)

first_album_row = list(first_album_result)[0]

first_album_budget = first_album_row[0]

# Update the budgets.

second_album_budget -= transfer_amount

first_album_budget += transfer_amount

print(

"Setting first album's budget to {} and the second album's "

"budget to {}.".format(first_album_budget, second_album_budget)

)

# Update the rows.

transaction.update(

table="Albums",

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

values=[(1, 1, first_album_budget), (2, 2, second_album_budget)],

)

database.run_in_transaction(update_albums)

print("Transaction complete.")

# [END spanner_read_write_transaction]

# [START spanner_read_only_transaction]

def read_only_transaction(instance_id, database_id):

"""Reads data inside of a read-only transaction.

Within the read-only transaction, or "snapshot", the application sees

consistent view of the database at a particular timestamp.

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.snapshot(multi_use=True) as snapshot:

# Read using SQL.

results = snapshot.execute_sql(

"SELECT SingerId, AlbumId, AlbumTitle FROM Albums"

)

print("Results from first read:")

for row in results:

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

# Perform another read using the `read` method. Even if the data

# is updated in-between the reads, the snapshot ensures that both

# return the same data.

keyset = spanner.KeySet(all_=True)

results = snapshot.read(

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

)

print("Results from second read:")

for row in results:

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

# [END spanner_read_only_transaction]

# [START spanner_create_table_with_timestamp_column]

def create_table_with_timestamp(instance_id, database_id):

"""Creates a table with a COMMIT_TIMESTAMP column."""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

[

"""CREATE TABLE Performances (

SingerId INT64 NOT NULL,

VenueId INT64 NOT NULL,

EventDate Date,

Revenue INT64,

LastUpdateTime TIMESTAMP NOT NULL

OPTIONS(allow_commit_timestamp=true)

) PRIMARY KEY (SingerId, VenueId, EventDate),

INTERLEAVE IN PARENT Singers ON DELETE CASCADE"""

]

)

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

operation.result(OPERATION_TIMEOUT_SECONDS)

print(

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

database_id, instance_id

)

)

# [END spanner_create_table_with_timestamp_column]

# [START spanner_insert_data_with_timestamp_column]

def insert_data_with_timestamp(instance_id, database_id):

"""Inserts data with a COMMIT_TIMESTAMP field into a table. """

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

with database.batch() as batch:

batch.insert(

table="Performances",

columns=("SingerId", "VenueId", "EventDate", "Revenue", "LastUpdateTime"),

values=[

(1, 4, "2017-10-05", 11000, spanner.COMMIT_TIMESTAMP),

(1, 19, "2017-11-02", 15000, spanner.COMMIT_TIMESTAMP),

(2, 42, "2017-12-23", 7000, spanner.COMMIT_TIMESTAMP),

],

)

print("Inserted data.")

# [END spanner_insert_data_with_timestamp_column]

# [START spanner_add_timestamp_column]

def add_timestamp_column(instance_id, database_id):

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

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(

[

"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP "

"OPTIONS(allow_commit_timestamp=true)"

]

)

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

operation.result(OPERATION_TIMEOUT_SECONDS)

print(

'Altered table "Albums" on database {} on instance {}.'.format(

database_id, instance_id

)

)

# [END spanner_add_timestamp_column]

# [START spanner_update_data_with_timestamp_column]

def update_data_with_timestamp(instance_id, database_id):

"""Updates Performances tables in the database with the COMMIT_TIMESTAMP

column.

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

In addition this update expects the LastUpdateTime column added by

applying this DDL statement against your database:

ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP

OPTIONS(allow_commit_timestamp=true)

"""

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", "LastUpdateTime"),

values=[

(1, 1, 1000000, spanner.COMMIT_TIMESTAMP),

(2, 2, 750000, spanner.COMMIT_TIMESTAMP),

],

)

print("Updated data.")

# [END spanner_update_data_with_timestamp_column]

# [START spanner_query_data_with_timestamp_column]

def query_data_with_timestamp(instance_id, database_id):

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

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

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

`add_timestamp_column` sample or by running this DDL statement

against your database:

ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP

OPTIONS (allow_commit_timestamp=true)

"""

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 "

"ORDER BY LastUpdateTime DESC"

)

for row in results:

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

# [END spanner_query_data_with_timestamp_column]

# [START spanner_add_numeric_column]

def add_numeric_column(instance_id, database_id):

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

"""

spanner_client = spanner.Client()

instance = spanner_client.instance(instance_id)

database = instance.database(database_id)

operation = database.update_ddl(["ALTER TABLE Venues ADD COLUMN Revenue NUMERIC"])

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

operation.result(OPERATION_TIMEOUT_SECONDS)

print(

'Altered table "Venues" on database {} on instance {}.'.format(

database_id, instance_id

)

)

# [END spanner_add_numeric_column]

# [START spanner_update_data_with_numeric_column]

def update_data_with_numeric(instance_id, database_id):

"""Updates Venues tables in the database with the NUMERIC

column.

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

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

`add_numeric_column` sample or by running this DDL statement

against your database: