def query_nested_struct_field(instance_id, database_id):
"""Query a table using nested field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
song_info_type = param_types.Struct([
param_types.StructField('SongName', param_types.STRING),
param_types.StructField(
'ArtistNames',
param_types.Array(
param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])))
])
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId, @song_info.SongName "
"FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) "
"IN UNNEST(@song_info.ArtistNames)",
params={'song_info': song_info},
param_types={'song_info': song_info_type})
for row in results:
print(u'SingerId: {} SongName: {}'.format(*row))
def update_data_with_dml_struct(instance_id, database_id):
"""Updates data with a DML statement and STRUCT parameters. """
# [START spanner_dml_structs]
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Timothy', 'Campbell')
def write_with_struct(transaction):
row_ct = transaction.execute_update(
"UPDATE Singers SET LastName = 'Grant' "
"WHERE STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type})
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(write_with_struct)
def query_with_array_of_struct(instance_id, database_id):
"""Query a table using an array of STRUCT parameters. """
# [START spanner_create_user_defined_struct]
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
# [END spanner_create_user_defined_struct]
# [START spanner_create_array_of_struct_with_data]
band_members = [("Elena", "Campbell"), ("Gabriel", "Wright"),
("Benjamin", "Martinez")]
# [END spanner_create_array_of_struct_with_data]
# [START spanner_query_data_with_array_of_struct]
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 FROM Singers WHERE "
"STRUCT<FirstName STRING, LastName STRING>"
"(FirstName, LastName) IN UNNEST(@names)",
params={'names': band_members},
param_types={'names': param_types.Array(name_type)})
for row in results:
print(u'SingerId: {}'.format(*row))
def query_with_struct(instance_id, database_id):
"""Query a table using STRUCT parameters. """
# [START spanner_create_struct_with_data]
record_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
record_value = ('Elena', 'Campbell')
# [END spanner_create_struct_with_data]
# [START spanner_query_data_with_struct]
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 FROM Singers WHERE "
"(FirstName, LastName) = @name",
params={'name': record_value},
param_types={'name': record_type})
for row in results:
print(u'SingerId: {}'.format(*row))
def test_it(self):
from google.cloud.spanner_v1.proto import type_pb2
from google.cloud.spanner_v1 import param_types
struct_type = type_pb2.StructType(fields=[
type_pb2.StructType.Field(
name='name',
type=type_pb2.Type(code=type_pb2.STRING),
),
type_pb2.StructType.Field(
name='count',
type=type_pb2.Type(code=type_pb2.INT64),
),
])
expected = type_pb2.Type(
code=type_pb2.STRUCT,
struct_type=struct_type,
)
found = param_types.Struct([
param_types.StructField('name', param_types.STRING),
param_types.StructField('count', param_types.INT64),
])
self.assertEqual(found, expected)
def test_it(self):
from google.cloud.spanner_v1 import Type
from google.cloud.spanner_v1 import TypeCode
from google.cloud.spanner_v1 import StructType
from google.cloud.spanner_v1 import param_types
struct_type = StructType(fields=[
StructType.Field(name="name", type_=Type(code=TypeCode.STRING)),
StructType.Field(name="count", type_=Type(code=TypeCode.INT64)),
])
expected = Type(code=TypeCode.STRUCT, struct_type=struct_type)
found = param_types.Struct([
param_types.StructField("name", param_types.STRING),
param_types.StructField("count", param_types.INT64),
])
self.assertEqual(found, expected)
def query_struct_field(instance_id, database_id):
"""Query a table using field access on a STRUCT parameter. """
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
name_type = param_types.Struct([
param_types.StructField('FirstName', param_types.STRING),
param_types.StructField('LastName', param_types.STRING)
])
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers "
"WHERE FirstName = @name.FirstName",
params={'name': ("Elena", "Campbell")},
param_types={'name': name_type})
for row in results:
print(u'SingerId: {}'.format(*row))
