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 07408b1

Browse filesBrowse files
kurtisvgkurtisvg
authored
Added Spanner DML/PDML samples. (GoogleCloudPlatform#1742)
* Added Spanner DML/PDML samples. * Fixed lint issues and bumped version.
1 parent 779a525 commit 07408b1
Copy full SHA for 07408b1

File tree

Expand file treeCollapse file tree

3 files changed

+373
-1
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+373
-1
lines changed

‎spanner/cloud-client/requirements.txt

Copy file name to clipboard
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,2 +1,2 @@
1-
google-cloud-spanner==1.4.0
1+
google-cloud-spanner==1.6.0
22
futures==3.2.0; python_version < "3"

‎spanner/cloud-client/snippets.py

Copy file name to clipboardExpand all lines: spanner/cloud-client/snippets.py
+311Lines changed: 311 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -748,6 +748,273 @@ def query_nested_struct_field(instance_id, database_id):
748748
# [END spanner_field_access_on_nested_struct_parameters]
749749

750750

751+
def insert_data_with_dml(instance_id, database_id):
752+
"""Inserts sample data into the given database using a DML statement. """
753+
# [START spanner_dml_standard_insert]
754+
# instance_id = "your-spanner-instance"
755+
# database_id = "your-spanner-db-id"
756+
757+
spanner_client = spanner.Client()
758+
instance = spanner_client.instance(instance_id)
759+
database = instance.database(database_id)
760+
761+
def insert_singers(transaction):
762+
row_ct = transaction.execute_update(
763+
"INSERT Singers (SingerId, FirstName, LastName) "
764+
" VALUES (10, 'Virginia', 'Watson')"
765+
)
766+
767+
print("{} record(s) inserted.".format(row_ct))
768+
769+
database.run_in_transaction(insert_singers)
770+
# [END spanner_dml_standard_insert]
771+
772+
773+
def update_data_with_dml(instance_id, database_id):
774+
"""Updates sample data from the database using a DML statement. """
775+
# [START spanner_dml_standard_update]
776+
# instance_id = "your-spanner-instance"
777+
# database_id = "your-spanner-db-id"
778+
779+
spanner_client = spanner.Client()
780+
instance = spanner_client.instance(instance_id)
781+
database = instance.database(database_id)
782+
783+
def update_albums(transaction):
784+
row_ct = transaction.execute_update(
785+
"UPDATE Albums "
786+
"SET MarketingBudget = MarketingBudget * 2 "
787+
"WHERE SingerId = 1 and AlbumId = 1"
788+
)
789+
790+
print("{} record(s) updated.".format(row_ct))
791+
792+
database.run_in_transaction(update_albums)
793+
# [END spanner_dml_standard_update]
794+
795+
796+
def delete_data_with_dml(instance_id, database_id):
797+
"""Deletes sample data from the database using a DML statement. """
798+
# [START spanner_dml_standard_delete]
799+
# instance_id = "your-spanner-instance"
800+
# database_id = "your-spanner-db-id"
801+
802+
spanner_client = spanner.Client()
803+
instance = spanner_client.instance(instance_id)
804+
database = instance.database(database_id)
805+
806+
def delete_singers(transaction):
807+
row_ct = transaction.execute_update(
808+
"DELETE Singers WHERE FirstName = 'Alice'"
809+
)
810+
811+
print("{} record(s) deleted.".format(row_ct))
812+
813+
database.run_in_transaction(delete_singers)
814+
# [END spanner_dml_standard_delete]
815+
816+
817+
def update_data_with_dml_timestamp(instance_id, database_id):
818+
"""Updates data with Timestamp from the database using a DML statement. """
819+
# [START spanner_dml_standard_update_with_timestamp]
820+
# instance_id = "your-spanner-instance"
821+
# database_id = "your-spanner-db-id"
822+
823+
spanner_client = spanner.Client()
824+
instance = spanner_client.instance(instance_id)
825+
database = instance.database(database_id)
826+
827+
def update_albums(transaction):
828+
row_ct = transaction.execute_update(
829+
"UPDATE Albums "
830+
"SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() "
831+
"WHERE SingerId = 1"
832+
)
833+
834+
print("{} record(s) updated.".format(row_ct))
835+
836+
database.run_in_transaction(update_albums)
837+
# [END spanner_dml_standard_update_with_timestamp]
838+
839+
840+
def dml_write_read_transaction(instance_id, database_id):
841+
"""First inserts data then reads it from within a transaction using DML."""
842+
# [START spanner_dml_write_then_read]
843+
# instance_id = "your-spanner-instance"
844+
# database_id = "your-spanner-db-id"
845+
846+
spanner_client = spanner.Client()
847+
instance = spanner_client.instance(instance_id)
848+
database = instance.database(database_id)
849+
850+
def read_then_write(transaction):
851+
# Insert record.
852+
row_ct = transaction.execute_update(
853+
"INSERT Singers (SingerId, FirstName, LastName) "
854+
" VALUES (11, 'Timothy', 'Campbell')"
855+
)
856+
print("{} record(s) inserted.".format(row_ct))
857+
858+
# Read newly inserted record.
859+
results = transaction.execute_sql(
860+
"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"
861+
)
862+
for result in results:
863+
print("FirstName: {}, LastName: {}".format(*result))
864+
865+
database.run_in_transaction(read_then_write)
866+
# [END spanner_dml_write_then_read]
867+
868+
869+
def update_data_with_dml_struct(instance_id, database_id):
870+
"""Updates data with a DML statement and STRUCT parameters. """
871+
# [START spanner_dml_structs]
872+
# instance_id = "your-spanner-instance"
873+
# database_id = "your-spanner-db-id"
874+
875+
spanner_client = spanner.Client()
876+
instance = spanner_client.instance(instance_id)
877+
database = instance.database(database_id)
878+
879+
record_type = param_types.Struct([
880+
param_types.StructField('FirstName', param_types.STRING),
881+
param_types.StructField('LastName', param_types.STRING)
882+
])
883+
record_value = ('Timothy', 'Campbell')
884+
885+
def write_with_struct(transaction):
886+
row_ct = transaction.execute_update(
887+
"UPDATE Singers SET LastName = 'Grant' "
888+
"WHERE STRUCT<FirstName STRING, LastName STRING>"
889+
"(FirstName, LastName) = @name",
890+
params={'name': record_value},
891+
param_types={'name': record_type}
892+
)
893+
print("{} record(s) updated.".format(row_ct))
894+
895+
database.run_in_transaction(write_with_struct)
896+
# [END spanner_dml_structs]
897+
898+
899+
def insert_with_dml(instance_id, database_id):
900+
"""Inserts data with a DML statement into the database. """
901+
# [START spanner_dml_getting_started_insert]
902+
# instance_id = "your-spanner-instance"
903+
# database_id = "your-spanner-db-id"
904+
spanner_client = spanner.Client()
905+
instance = spanner_client.instance(instance_id)
906+
database = instance.database(database_id)
907+
908+
def insert_singers(transaction):
909+
row_ct = transaction.execute_update(
910+
"INSERT Singers (SingerId, FirstName, LastName) VALUES "
911+
"(12, 'Melissa', 'Garcia'), "
912+
"(13, 'Russell', 'Morales'), "
913+
"(14, 'Jacqueline', 'Long'), "
914+
"(15, 'Dylan', 'Shaw')"
915+
)
916+
print("{} record(s) inserted.".format(row_ct))
917+
918+
database.run_in_transaction(insert_singers)
919+
# [END spanner_dml_getting_started_insert]
920+
921+
922+
def write_with_dml_transaction(instance_id, database_id):
923+
""" Transfers a marketing budget from one album to another. """
924+
# [START spanner_dml_getting_started_update]
925+
# instance_id = "your-spanner-instance"
926+
# database_id = "your-spanner-db-id"
927+
928+
spanner_client = spanner.Client()
929+
instance = spanner_client.instance(instance_id)
930+
database = instance.database(database_id)
931+
932+
def transfer_budget(transaction):
933+
# Transfer marketing budget from one album to another. Performed in a
934+
# single transaction to ensure that the transfer is atomic.
935+
first_album_result = transaction.execute_sql(
936+
"SELECT MarketingBudget from Albums "
937+
"WHERE SingerId = 1 and AlbumId = 1"
938+
)
939+
first_album_row = list(first_album_result)[0]
940+
first_album_budget = first_album_row[0]
941+
942+
transfer_amount = 300000
943+
944+
# Transaction will only be committed if this condition still holds at
945+
# the time of commit. Otherwise it will be aborted and the callable
946+
# will be rerun by the client library
947+
if first_album_budget >= transfer_amount:
948+
second_album_result = transaction.execute_sql(
949+
"SELECT MarketingBudget from Albums "
950+
"WHERE SingerId = 1 and AlbumId = 1"
951+
)
952+
second_album_row = list(second_album_result)[0]
953+
second_album_budget = second_album_row[0]
954+
955+
first_album_budget -= transfer_amount
956+
second_album_budget += transfer_amount
957+
958+
# Update first album
959+
transaction.execute_update(
960+
"UPDATE Albums "
961+
"SET MarketingBudget = @AlbumBudget "
962+
"WHERE SingerId = 1 and AlbumId = 1",
963+
params={"AlbumBudget": first_album_budget},
964+
param_types={"AlbumBudget": spanner.param_types.INT64}
965+
)
966+
967+
# Update second album
968+
transaction.execute_update(
969+
"UPDATE Albums "
970+
"SET MarketingBudget = @AlbumBudget "
971+
"WHERE SingerId = 2 and AlbumId = 2",
972+
params={"AlbumBudget": second_album_budget},
973+
param_types={"AlbumBudget": spanner.param_types.INT64}
974+
)
975+
976+
print("Transferred {} from Album1's budget to Album2's".format(
977+
transfer_amount))
978+
979+
database.run_in_transaction(transfer_budget)
980+
# [END spanner_dml_getting_started_update]
981+
982+
983+
def update_data_with_partitioned_dml(instance_id, database_id):
984+
""" Update sample data with a partitioned DML statement. """
985+
# [START spanner_dml_partitioned_update]
986+
# instance_id = "your-spanner-instance"
987+
# database_id = "your-spanner-db-id"
988+
989+
spanner_client = spanner.Client()
990+
instance = spanner_client.instance(instance_id)
991+
database = instance.database(database_id)
992+
993+
row_ct = database.execute_partitioned_dml(
994+
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
995+
)
996+
997+
print("{} records updated.".format(row_ct))
998+
# [END spanner_dml_partitioned_update]
999+
1000+
1001+
def delete_data_with_partitioned_dml(instance_id, database_id):
1002+
""" Delete sample data with a partitioned DML statement. """
1003+
# [START spanner_dml_partitioned_delete]
1004+
# instance_id = "your-spanner-instance"
1005+
# database_id = "your-spanner-db-id"
1006+
spanner_client = spanner.Client()
1007+
instance = spanner_client.instance(instance_id)
1008+
database = instance.database(database_id)
1009+
1010+
row_ct = database.execute_partitioned_dml(
1011+
"DELETE Singers WHERE SingerId > 10"
1012+
)
1013+
1014+
print("{} record(s) deleted.".format(row_ct))
1015+
# [END spanner_dml_partitioned_delete]
1016+
1017+
7511018
if __name__ == '__main__': # noqa: C901
7521019
parser = argparse.ArgumentParser(
7531020
description=__doc__,
@@ -802,6 +1069,30 @@ def query_nested_struct_field(instance_id, database_id):
8021069
'query_struct_field', help=query_struct_field.__doc__)
8031070
subparsers.add_parser(
8041071
'query_nested_struct_field', help=query_nested_struct_field.__doc__)
1072+
subparsers.add_parser(
1073+
'insert_data_with_dml', help=insert_data_with_dml.__doc__)
1074+
subparsers.add_parser(
1075+
'update_data_with_dml', help=update_data_with_dml.__doc__)
1076+
subparsers.add_parser(
1077+
'delete_data_with_dml', help=delete_data_with_dml.__doc__)
1078+
subparsers.add_parser(
1079+
'update_data_with_dml_timestamp',
1080+
help=update_data_with_dml_timestamp.__doc__)
1081+
subparsers.add_parser(
1082+
'dml_write_read_transaction',
1083+
help=dml_write_read_transaction.__doc__)
1084+
subparsers.add_parser(
1085+
'update_data_with_dml_struct',
1086+
help=update_data_with_dml_struct.__doc__)
1087+
subparsers.add_parser('insert_with_dml', help=insert_with_dml.__doc__)
1088+
subparsers.add_parser(
1089+
'write_with_dml_transaction', help=write_with_dml_transaction.__doc__)
1090+
subparsers.add_parser(
1091+
'update_data_with_partitioned_dml',
1092+
help=update_data_with_partitioned_dml.__doc__)
1093+
subparsers.add_parser(
1094+
'delete_data_with_partitioned_dml',
1095+
help=delete_data_with_partitioned_dml.__doc__)
8051096

8061097
args = parser.parse_args()
8071098

@@ -857,3 +1148,23 @@ def query_nested_struct_field(instance_id, database_id):
8571148
query_struct_field(args.instance_id, args.database_id)
8581149
elif args.command == 'query_nested_struct_field':
8591150
query_nested_struct_field(args.instance_id, args.database_id)
1151+
elif args.command == 'insert_data_with_dml':
1152+
insert_data_with_dml(args.instance_id, args.database_id)
1153+
elif args.command == 'update_data_with_dml':
1154+
update_data_with_dml(args.instance_id, args.database_id)
1155+
elif args.command == 'delete_data_with_dml':
1156+
delete_data_with_dml(args.instance_id, args.database_id)
1157+
elif args.command == 'update_data_with_dml_timestamp':
1158+
update_data_with_dml_timestamp(args.instance_id, args.database_id)
1159+
elif args.command == 'dml_write_read_transaction':
1160+
dml_write_read_transaction(args.instance_id, args.database_id)
1161+
elif args.command == 'update_data_with_dml_struct':
1162+
update_data_with_dml_struct(args.instance_id, args.database_id)
1163+
elif args.command == 'insert_with_dml':
1164+
insert_with_dml(args.instance_id, args.database_id)
1165+
elif args.command == 'write_with_dml_transaction':
1166+
write_with_dml_transaction(args.instance_id, args.database_id)
1167+
elif args.command == 'update_data_with_partitioned_dml':
1168+
update_data_with_partitioned_dml(args.instance_id, args.database_id)
1169+
elif args.command == 'delete_data_with_partitioned_dml':
1170+
delete_data_with_partitioned_dml(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
@@ -209,3 +209,64 @@ def test_query_nested_struct_field(capsys):
209209
out, _ = capsys.readouterr()
210210
assert 'SingerId: 6 SongName: Imagination' in out
211211
assert 'SingerId: 9 SongName: Imagination' in out
212+
213+
214+
def test_insert_data_with_dml(capsys):
215+
snippets.insert_data_with_dml(INSTANCE_ID, DATABASE_ID)
216+
out, _ = capsys.readouterr()
217+
assert '1 record(s) inserted.' in out
218+
219+
220+
def test_update_data_with_dml(capsys):
221+
snippets.update_data_with_dml(INSTANCE_ID, DATABASE_ID)
222+
out, _ = capsys.readouterr()
223+
assert '1 record(s) updated.' in out
224+
225+
226+
def test_delete_data_with_dml(capsys):
227+
snippets.delete_data_with_dml(INSTANCE_ID, DATABASE_ID)
228+
out, _ = capsys.readouterr()
229+
assert '1 record(s) deleted.' in out
230+
231+
232+
def test_update_data_with_dml_timestamp(capsys):
233+
snippets.update_data_with_dml_timestamp(INSTANCE_ID, DATABASE_ID)
234+
out, _ = capsys.readouterr()
235+
assert '2 record(s) updated.' in out
236+
237+
238+
def test_dml_write_read_transaction(capsys):
239+
snippets.dml_write_read_transaction(INSTANCE_ID, DATABASE_ID)
240+
out, _ = capsys.readouterr()
241+
assert '1 record(s) inserted.' in out
242+
assert 'FirstName: Timothy, LastName: Campbell' in out
243+
244+
245+
def test_update_data_with_dml_struct(capsys):
246+
snippets.update_data_with_dml_struct(INSTANCE_ID, DATABASE_ID)
247+
out, _ = capsys.readouterr()
248+
assert '1 record(s) updated' in out
249+
250+
251+
def test_insert_with_dml(capsys):
252+
snippets.insert_with_dml(INSTANCE_ID, DATABASE_ID)
253+
out, _ = capsys.readouterr()
254+
assert '4 record(s) inserted' in out
255+
256+
257+
def test_write_with_dml_transaction(capsys):
258+
snippets.write_with_dml_transaction(INSTANCE_ID, DATABASE_ID)
259+
out, _ = capsys.readouterr()
260+
assert "Transferred 300000 from Album1's budget to Album2's" in out
261+
262+
263+
def update_data_with_partitioned_dml(capsys):
264+
snippets.update_data_with_partitioned_dml(INSTANCE_ID, DATABASE_ID)
265+
out, _ = capsys.readouterr()
266+
assert "3 record(s) updated" in out
267+
268+
269+
def delete_data_with_partitioned_dml(capsys):
270+
snippets.delete_data_with_partitioned_dml(INSTANCE_ID, DATABASE_ID)
271+
out, _ = capsys.readouterr()
272+
assert "5 record(s) deleted" in out

0 commit comments

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