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))