Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit e82d1b1

Browse filesBrowse files
jabubakejabubake
authored
Adding Spanner STRUCT param samples (GoogleCloudPlatform#1519)
* Adding Spanner STRUCT param samples
1 parent 3d5b4c2 commit e82d1b1
Copy full SHA for e82d1b1

File tree

Expand file treeCollapse file tree

2 files changed

+231
-2
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+231
-2
lines changed

‎spanner/cloud-client/snippets.py

Copy file name to clipboardExpand all lines: spanner/cloud-client/snippets.py
+170-2Lines changed: 170 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@
2323
import argparse
2424

2525
from google.cloud import spanner
26+
from google.cloud.spanner_v1 import param_types
2627

2728

2829
# [START spanner_create_database]
@@ -514,8 +515,7 @@ def insert_data_with_timestamp(instance_id, database_id):
514515

515516
# [START spanner_add_timestamp_column]
516517
def add_timestamp_column(instance_id, database_id):
517-
"""
518-
Adds a new TIMESTAMP column to the Albums table in the example database.
518+
""" Adds a new TIMESTAMP column to the Albums table in the example database.
519519
"""
520520
spanner_client = spanner.Client()
521521
instance = spanner_client.instance(instance_id)
@@ -598,6 +598,156 @@ def query_data_with_timestamp(instance_id, database_id):
598598
# [END spanner_query_data_with_timestamp_column]
599599

600600

601+
# [START spanner_write_data_for_struct_queries]
602+
def write_struct_data(instance_id, database_id):
603+
"""Inserts sample data that can be used to test STRUCT parameters
604+
in queries.
605+
"""
606+
spanner_client = spanner.Client()
607+
instance = spanner_client.instance(instance_id)
608+
database = instance.database(database_id)
609+
610+
with database.batch() as batch:
611+
batch.insert(
612+
table='Singers',
613+
columns=('SingerId', 'FirstName', 'LastName',),
614+
values=[
615+
(6, u'Elena', u'Campbell'),
616+
(7, u'Gabriel', u'Wright'),
617+
(8, u'Benjamin', u'Martinez'),
618+
(9, u'Hannah', u'Harris')])
619+
620+
print('Inserted sample data for STRUCT queries')
621+
# [END spanner_write_data_for_struct_queries]
622+
623+
624+
def query_with_struct(instance_id, database_id):
625+
"""Query a table using STRUCT parameters. """
626+
# [START spanner_create_struct_with_data]
627+
record_type = param_types.Struct([
628+
param_types.StructField('FirstName', param_types.STRING),
629+
param_types.StructField('LastName', param_types.STRING)
630+
])
631+
record_value = ('Elena', 'Campbell')
632+
# [END spanner_create_struct_with_data]
633+
634+
# [START spanner_query_data_with_struct]
635+
spanner_client = spanner.Client()
636+
instance = spanner_client.instance(instance_id)
637+
638+
database = instance.database(database_id)
639+
640+
with database.snapshot() as snapshot:
641+
results = snapshot.execute_sql(
642+
"SELECT SingerId FROM Singers WHERE "
643+
"(FirstName, LastName) = @name",
644+
params={'name': record_value},
645+
param_types={'name': record_type})
646+
647+
for row in results:
648+
print(u'SingerId: {}'.format(*row))
649+
# [END spanner_query_data_with_struct]
650+
651+
652+
def query_with_array_of_struct(instance_id, database_id):
653+
"""Query a table using an array of STRUCT parameters. """
654+
# [START spanner_create_user_defined_struct]
655+
name_type = param_types.Struct([
656+
param_types.StructField('FirstName', param_types.STRING),
657+
param_types.StructField('LastName', param_types.STRING)])
658+
# [END spanner_create_user_defined_struct]
659+
660+
# [START spanner_create_array_of_struct_with_data]
661+
band_members = [("Elena", "Campbell"),
662+
("Gabriel", "Wright"),
663+
("Benjamin", "Martinez")]
664+
# [END spanner_create_array_of_struct_with_data]
665+
666+
# [START spanner_query_data_with_array_of_struct]
667+
spanner_client = spanner.Client()
668+
instance = spanner_client.instance(instance_id)
669+
database = instance.database(database_id)
670+
671+
with database.snapshot() as snapshot:
672+
results = snapshot.execute_sql(
673+
"SELECT SingerId FROM Singers WHERE "
674+
"STRUCT<FirstName STRING, LastName STRING>"
675+
"(FirstName, LastName) IN UNNEST(@names)",
676+
params={'names': band_members},
677+
param_types={'names': param_types.Array(name_type)})
678+
679+
for row in results:
680+
print(u'SingerId: {}'.format(*row))
681+
# [END spanner_query_data_with_array_of_struct]
682+
683+
684+
# [START spanner_field_access_on_struct_parameters]
685+
def query_struct_field(instance_id, database_id):
686+
"""Query a table using field access on a STRUCT parameter. """
687+
spanner_client = spanner.Client()
688+
instance = spanner_client.instance(instance_id)
689+
database = instance.database(database_id)
690+
691+
name_type = param_types.Struct([
692+
param_types.StructField('FirstName', param_types.STRING),
693+
param_types.StructField('LastName', param_types.STRING)
694+
])
695+
696+
with database.snapshot() as snapshot:
697+
results = snapshot.execute_sql(
698+
"SELECT SingerId FROM Singers "
699+
"WHERE FirstName = @name.FirstName",
700+
params={'name': ("Elena", "Campbell")},
701+
param_types={'name': name_type})
702+
703+
for row in results:
704+
print(u'SingerId: {}'.format(*row))
705+
# [START spanner_field_access_on_struct_parameters]
706+
707+
708+
# [START spanner_field_access_on_nested_struct_parameters]
709+
def query_nested_struct_field(instance_id, database_id):
710+
"""Query a table using nested field access on a STRUCT parameter. """
711+
spanner_client = spanner.Client()
712+
instance = spanner_client.instance(instance_id)
713+
database = instance.database(database_id)
714+
715+
song_info_type = param_types.Struct([
716+
param_types.StructField('SongName', param_types.STRING),
717+
param_types.StructField(
718+
'ArtistNames', param_types.Array(
719+
param_types.Struct([
720+
param_types.StructField(
721+
'FirstName', param_types.STRING),
722+
param_types.StructField(
723+
'LastName', param_types.STRING)
724+
])
725+
)
726+
)
727+
])
728+
729+
song_info = ('Imagination', [('Elena', 'Campbell'), ('Hannah', 'Harris')])
730+
731+
with database.snapshot() as snapshot:
732+
results = snapshot.execute_sql(
733+
"SELECT SingerId, @song_info.SongName "
734+
"FROM Singers WHERE "
735+
"STRUCT<FirstName STRING, LastName STRING>"
736+
"(FirstName, LastName) "
737+
"IN UNNEST(@song_info.ArtistNames)",
738+
params={
739+
'song_info': song_info
740+
},
741+
param_types={
742+
'song_info': song_info_type
743+
}
744+
)
745+
746+
for row in results:
747+
print(u'SingerId: {} SongName: {}'.format(*row))
748+
# [END spanner_field_access_on_nested_struct_parameters]
749+
750+
601751
if __name__ == '__main__': # noqa: C901
602752
parser = argparse.ArgumentParser(
603753
description=__doc__,
@@ -644,6 +794,14 @@ def query_data_with_timestamp(instance_id, database_id):
644794
'update_data_with_timestamp', help=update_data_with_timestamp.__doc__)
645795
subparsers.add_parser(
646796
'query_data_with_timestamp', help=query_data_with_timestamp.__doc__)
797+
subparsers.add_parser('write_struct_data', help=write_struct_data.__doc__)
798+
subparsers.add_parser('query_with_struct', help=query_with_struct.__doc__)
799+
subparsers.add_parser(
800+
'query_with_array_of_struct', help=query_with_array_of_struct.__doc__)
801+
subparsers.add_parser(
802+
'query_struct_field', help=query_struct_field.__doc__)
803+
subparsers.add_parser(
804+
'query_nested_struct_field', help=query_nested_struct_field.__doc__)
647805

648806
args = parser.parse_args()
649807

@@ -689,3 +847,13 @@ def query_data_with_timestamp(instance_id, database_id):
689847
update_data_with_timestamp(args.instance_id, args.database_id)
690848
elif args.command == 'query_data_with_timestamp':
691849
query_data_with_timestamp(args.instance_id, args.database_id)
850+
elif args.command == 'write_struct_data':
851+
write_struct_data(args.instance_id, args.database_id)
852+
elif args.command == 'query_with_struct':
853+
query_with_struct(args.instance_id, args.database_id)
854+
elif args.command == 'query_with_array_of_struct':
855+
query_with_array_of_struct(args.instance_id, args.database_id)
856+
elif args.command == 'query_struct_field':
857+
query_struct_field(args.instance_id, args.database_id)
858+
elif args.command == 'query_nested_struct_field':
859+
query_nested_struct_field(args.instance_id, args.database_id)

‎spanner/cloud-client/snippets_test.py

Copy file name to clipboardExpand all lines: spanner/cloud-client/snippets_test.py
+61Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -234,3 +234,64 @@ def _():
234234
out, _ = capsys.readouterr()
235235

236236
assert 'Go, Go, Go' in out
237+
238+
239+
@pytest.mark.slow
240+
def test_query_data_with_struct(temporary_database, capsys):
241+
@eventually_consistent.call
242+
def _():
243+
snippets.write_struct_data(
244+
SPANNER_INSTANCE,
245+
temporary_database.database_id)
246+
snippets.query_with_struct(
247+
SPANNER_INSTANCE,
248+
temporary_database.database_id)
249+
out, _ = capsys.readouterr()
250+
251+
assert 'SingerId: 6' in out
252+
253+
254+
@pytest.mark.slow
255+
def test_query_data_with_array_struct(temporary_database, capsys):
256+
@eventually_consistent.call
257+
def _():
258+
snippets.write_struct_data(
259+
SPANNER_INSTANCE,
260+
temporary_database.database_id)
261+
snippets.query_with_array_of_struct(
262+
SPANNER_INSTANCE,
263+
temporary_database.database_id)
264+
out, _ = capsys.readouterr()
265+
266+
assert 'SingerId: 6\nSingerId: 7' in out
267+
268+
269+
@pytest.mark.slow
270+
def test_query_data_with_field_struct(temporary_database, capsys):
271+
@eventually_consistent.call
272+
def _():
273+
snippets.write_struct_data(
274+
SPANNER_INSTANCE,
275+
temporary_database.database_id)
276+
snippets.query_struct_field(
277+
SPANNER_INSTANCE,
278+
temporary_database.database_id)
279+
out, _ = capsys.readouterr()
280+
281+
assert 'SingerId: 6' in out
282+
283+
284+
@pytest.mark.slow
285+
def test_query_data_with_nested_field_struct(temporary_database, capsys):
286+
@eventually_consistent.call
287+
def _():
288+
snippets.write_struct_data(
289+
SPANNER_INSTANCE,
290+
temporary_database.database_id)
291+
snippets.query_nested_struct_field(
292+
SPANNER_INSTANCE,
293+
temporary_database.database_id)
294+
out, _ = capsys.readouterr()
295+
296+
assert 'SingerId: 6 SongName: Imagination' in out
297+
assert 'SingerId: 9 SongName: Imagination' in out

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.