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 62e55b5

Browse filesBrowse files
c2nesc2nes
authored
feat: Add snippets for Spanner DML with returning clause (#811)
Samples are provided for INSERT, DELETE, and UPDATE in both GoogleSQL and PostgreSQL dialects. To provide a more compelling example for the INSERT case, a generated column has been added in the "create_database" example so that the generated value can be returned in the INSERT examples.
1 parent adc511f commit 62e55b5
Copy full SHA for 62e55b5

File tree

Expand file treeCollapse file tree

4 files changed

+262
-5
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+262
-5
lines changed

‎samples/samples/pg_snippets.py

Copy file name to clipboardExpand all lines: samples/samples/pg_snippets.py
+105Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,8 @@ def create_table_using_ddl(database_name):
9595
FirstName character varying(1024),
9696
LastName character varying(1024),
9797
SingerInfo bytea,
98+
FullName character varying(2048)
99+
GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED,
98100
PRIMARY KEY (SingerId)
99101
)""",
100102
"""CREATE TABLE Albums (
@@ -539,6 +541,38 @@ def insert_singers(transaction):
539541
# [END spanner_postgresql_dml_getting_started_insert]
540542

541543

544+
def insert_with_dml_returning(instance_id, database_id):
545+
"""Inserts sample data into the given database using a DML statement having a RETURNING clause. """
546+
# [START spanner_postgresql_dml_insert_returning]
547+
# instance_id = "your-spanner-instance"
548+
# database_id = "your-spanner-db-id"
549+
550+
spanner_client = spanner.Client()
551+
instance = spanner_client.instance(instance_id)
552+
database = instance.database(database_id)
553+
554+
# Insert records into the SINGERS table and returns the
555+
# generated column FullName of the inserted records using
556+
# 'RETURNING FullName'.
557+
# It is also possible to return all columns of all the
558+
# inserted records by using 'RETURNING *'.
559+
def insert_singers(transaction):
560+
results = transaction.execute_sql(
561+
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
562+
"(21, 'Luann', 'Chizoba'), "
563+
"(22, 'Denis', 'Patricio'), "
564+
"(23, 'Felxi', 'Ronan'), "
565+
"(24, 'Dominik', 'Martyna') "
566+
"RETURNING FullName"
567+
)
568+
for result in results:
569+
print("FullName: {}".format(*result))
570+
print("{} record(s) inserted.".format(results.stats.row_count_exact))
571+
572+
database.run_in_transaction(insert_singers)
573+
# [END spanner_postgresql_dml_insert_returning]
574+
575+
542576
def query_data_with_parameter(instance_id, database_id):
543577
"""Queries sample data from the database using SQL with a parameter."""
544578
# [START spanner_postgresql_query_with_parameter]
@@ -852,6 +886,37 @@ def update_albums(transaction):
852886
# [END spanner_postgresql_dml_standard_update]
853887

854888

889+
def update_data_with_dml_returning(instance_id, database_id):
890+
"""Updates sample data from the database using a DML statement having a RETURNING clause."""
891+
# [START spanner_postgresql_dml_update_returning]
892+
# instance_id = "your-spanner-instance"
893+
# database_id = "your-spanner-db-id"
894+
895+
spanner_client = spanner.Client()
896+
instance = spanner_client.instance(instance_id)
897+
database = instance.database(database_id)
898+
899+
# Update MarketingBudget column for records satisfying
900+
# a particular condition and returns the modified
901+
# MarketingBudget column of the updated records using
902+
# 'RETURNING MarketingBudget'.
903+
# It is also possible to return all columns of all the
904+
# updated records by using 'RETURNING *'.
905+
def update_albums(transaction):
906+
results = transaction.execute_sql(
907+
"UPDATE Albums "
908+
"SET MarketingBudget = MarketingBudget * 2 "
909+
"WHERE SingerId = 1 and AlbumId = 1 "
910+
"RETURNING MarketingBudget"
911+
)
912+
for result in results:
913+
print("MarketingBudget: {}".format(*result))
914+
print("{} record(s) updated.".format(results.stats.row_count_exact))
915+
916+
database.run_in_transaction(update_albums)
917+
# [END spanner_postgresql_dml_update_returning]
918+
919+
855920
def delete_data_with_dml(instance_id, database_id):
856921
"""Deletes sample data from the database using a DML statement."""
857922
# [START spanner_postgresql_dml_standard_delete]
@@ -873,6 +938,35 @@ def delete_singers(transaction):
873938
# [END spanner_postgresql_dml_standard_delete]
874939

875940

941+
def delete_data_with_dml_returning(instance_id, database_id):
942+
"""Deletes sample data from the database using a DML statement having a RETURNING clause. """
943+
# [START spanner_postgresql_dml_delete_returning]
944+
# instance_id = "your-spanner-instance"
945+
# database_id = "your-spanner-db-id"
946+
947+
spanner_client = spanner.Client()
948+
instance = spanner_client.instance(instance_id)
949+
database = instance.database(database_id)
950+
951+
# Delete records from SINGERS table satisfying a
952+
# particular condition and returns the SingerId
953+
# and FullName column of the deleted records using
954+
# 'RETURNING SingerId, FullName'.
955+
# It is also possible to return all columns of all the
956+
# deleted records by using 'RETURNING *'.
957+
def delete_singers(transaction):
958+
results = transaction.execute_sql(
959+
"DELETE FROM Singers WHERE FirstName = 'David' "
960+
"RETURNING SingerId, FullName"
961+
)
962+
for result in results:
963+
print("SingerId: {}, FullName: {}".format(*result))
964+
print("{} record(s) deleted.".format(results.stats.row_count_exact))
965+
966+
database.run_in_transaction(delete_singers)
967+
# [END spanner_postgresql_dml_delete_returning]
968+
969+
876970
def dml_write_read_transaction(instance_id, database_id):
877971
"""First inserts data then reads it from within a transaction using DML."""
878972
# [START spanner_postgresql_dml_write_then_read]
@@ -1522,12 +1616,17 @@ def query_data_with_jsonb_parameter(instance_id, database_id):
15221616
help=insert_data_with_dml.__doc__)
15231617
subparsers.add_parser("update_data_with_dml",
15241618
help=update_data_with_dml.__doc__)
1619+
subparsers.add_parser("update_data_with_dml",
1620+
help=update_data_with_dml_returning.__doc__)
15251621
subparsers.add_parser("delete_data_with_dml",
15261622
help=delete_data_with_dml.__doc__)
1623+
subparsers.add_parser("delete_data_with_dml_returning",
1624+
help=delete_data_with_dml_returning.__doc__)
15271625
subparsers.add_parser(
15281626
"dml_write_read_transaction", help=dml_write_read_transaction.__doc__
15291627
)
15301628
subparsers.add_parser("insert_with_dml", help=insert_with_dml.__doc__)
1629+
subparsers.add_parser("insert_with_dml_returning", help=insert_with_dml_returning.__doc__)
15311630
subparsers.add_parser(
15321631
"query_data_with_parameter", help=query_data_with_parameter.__doc__
15331632
)
@@ -1628,12 +1727,18 @@ def query_data_with_jsonb_parameter(instance_id, database_id):
16281727
insert_data_with_dml(args.instance_id, args.database_id)
16291728
elif args.command == "update_data_with_dml":
16301729
update_data_with_dml(args.instance_id, args.database_id)
1730+
elif args.command == "update_data_with_dml_returning":
1731+
update_data_with_dml_returning(args.instance_id, args.database_id)
16311732
elif args.command == "delete_data_with_dml":
16321733
delete_data_with_dml(args.instance_id, args.database_id)
1734+
elif args.command == "delete_data_with_dml_returning":
1735+
delete_data_with_dml_returning(args.instance_id, args.database_id)
16331736
elif args.command == "dml_write_read_transaction":
16341737
dml_write_read_transaction(args.instance_id, args.database_id)
16351738
elif args.command == "insert_with_dml":
16361739
insert_with_dml(args.instance_id, args.database_id)
1740+
elif args.command == "insert_with_dml_returning":
1741+
insert_with_dml_returning(args.instance_id, args.database_id)
16371742
elif args.command == "query_data_with_parameter":
16381743
query_data_with_parameter(args.instance_id, args.database_id)
16391744
elif args.command == "write_with_dml_transaction":

‎samples/samples/pg_snippets_test.py

Copy file name to clipboardExpand all lines: samples/samples/pg_snippets_test.py
+25-2Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,8 @@
2828
FirstName CHARACTER VARYING(1024),
2929
LastName CHARACTER VARYING(1024),
3030
SingerInfo BYTEA,
31+
FullName CHARACTER VARYING(2048)
32+
GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED,
3133
PRIMARY KEY (SingerId)
3234
)
3335
"""
@@ -287,13 +289,27 @@ def test_update_data_with_dml(capsys, instance_id, sample_database):
287289
assert "1 record(s) updated." in out
288290

289291

292+
@pytest.mark.dependency(depends=["add_column"])
293+
def test_update_data_with_dml_returning(capsys, instance_id, sample_database):
294+
snippets.update_data_with_dml_returning(instance_id, sample_database.database_id)
295+
out, _ = capsys.readouterr()
296+
assert "1 record(s) updated." in out
297+
298+
290299
@pytest.mark.dependency(depends=["insert_data"])
291300
def test_delete_data_with_dml(capsys, instance_id, sample_database):
292301
snippets.delete_data_with_dml(instance_id, sample_database.database_id)
293302
out, _ = capsys.readouterr()
294303
assert "1 record(s) deleted." in out
295304

296305

306+
@pytest.mark.dependency(depends=["insert_data"])
307+
def test_delete_data_with_dml_returning(capsys, instance_id, sample_database):
308+
snippets.delete_data_with_dml_returning(instance_id, sample_database.database_id)
309+
out, _ = capsys.readouterr()
310+
assert "1 record(s) deleted." in out
311+
312+
297313
@pytest.mark.dependency(name="dml_write_read_transaction")
298314
def test_dml_write_read_transaction(capsys, instance_id, sample_database):
299315
snippets.dml_write_read_transaction(instance_id,
@@ -310,6 +326,13 @@ def test_insert_with_dml(capsys, instance_id, sample_database):
310326
assert "4 record(s) inserted" in out
311327

312328

329+
@pytest.mark.dependency(name="insert_with_dml_returning")
330+
def test_insert_with_dml_returning(capsys, instance_id, sample_database):
331+
snippets.insert_with_dml_returning(instance_id, sample_database.database_id)
332+
out, _ = capsys.readouterr()
333+
assert "4 record(s) inserted" in out
334+
335+
313336
@pytest.mark.dependency(depends=["insert_with_dml"])
314337
def test_query_data_with_parameter(capsys, instance_id, sample_database):
315338
snippets.query_data_with_parameter(instance_id, sample_database.database_id)
@@ -333,12 +356,12 @@ def update_data_with_partitioned_dml(capsys, instance_id, sample_database):
333356
assert "3 record(s) updated" in out
334357

335358

336-
@pytest.mark.dependency(depends=["insert_with_dml"])
359+
@pytest.mark.dependency(depends=["insert_with_dml", "insert_with_dml_returning"])
337360
def test_delete_data_with_partitioned_dml(capsys, instance_id, sample_database):
338361
snippets.delete_data_with_partitioned_dml(instance_id,
339362
sample_database.database_id)
340363
out, _ = capsys.readouterr()
341-
assert "5 record(s) deleted" in out
364+
assert "9 record(s) deleted" in out
342365

343366

344367
@pytest.mark.dependency(depends=["add_column"])

‎samples/samples/snippets.py

Copy file name to clipboardExpand all lines: samples/samples/snippets.py
+105-1Lines changed: 105 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -169,7 +169,10 @@ def create_database(instance_id, database_id):
169169
SingerId INT64 NOT NULL,
170170
FirstName STRING(1024),
171171
LastName STRING(1024),
172-
SingerInfo BYTES(MAX)
172+
SingerInfo BYTES(MAX),
173+
FullName STRING(2048) AS (
174+
ARRAY_TO_STRING([FirstName, LastName], " ")
175+
) STORED
173176
) PRIMARY KEY (SingerId)""",
174177
"""CREATE TABLE Albums (
175178
SingerId INT64 NOT NULL,
@@ -1344,6 +1347,37 @@ def update_albums(transaction):
13441347
# [END spanner_dml_standard_update]
13451348

13461349

1350+
def update_data_with_dml_returning(instance_id, database_id):
1351+
"""Updates sample data from the database using a DML statement having a THEN RETURN clause."""
1352+
# [START spanner_dml_update_returning]
1353+
# instance_id = "your-spanner-instance"
1354+
# database_id = "your-spanner-db-id"
1355+
1356+
spanner_client = spanner.Client()
1357+
instance = spanner_client.instance(instance_id)
1358+
database = instance.database(database_id)
1359+
1360+
# Update MarketingBudget column for records satisfying
1361+
# a particular condition and returns the modified
1362+
# MarketingBudget column of the updated records using
1363+
# 'THEN RETURN MarketingBudget'.
1364+
# It is also possible to return all columns of all the
1365+
# updated records by using 'THEN RETURN *'.
1366+
def update_albums(transaction):
1367+
results = transaction.execute_sql(
1368+
"UPDATE Albums "
1369+
"SET MarketingBudget = MarketingBudget * 2 "
1370+
"WHERE SingerId = 1 and AlbumId = 1 "
1371+
"THEN RETURN MarketingBudget"
1372+
)
1373+
for result in results:
1374+
print("MarketingBudget: {}".format(*result))
1375+
print("{} record(s) updated.".format(results.stats.row_count_exact))
1376+
1377+
database.run_in_transaction(update_albums)
1378+
# [END spanner_dml_update_returning]
1379+
1380+
13471381
def delete_data_with_dml(instance_id, database_id):
13481382
"""Deletes sample data from the database using a DML statement."""
13491383
# [START spanner_dml_standard_delete]
@@ -1365,6 +1399,35 @@ def delete_singers(transaction):
13651399
# [END spanner_dml_standard_delete]
13661400

13671401

1402+
def delete_data_with_dml_returning(instance_id, database_id):
1403+
"""Deletes sample data from the database using a DML statement having a THEN RETURN clause. """
1404+
# [START spanner_dml_delete_returning]
1405+
# instance_id = "your-spanner-instance"
1406+
# database_id = "your-spanner-db-id"
1407+
1408+
spanner_client = spanner.Client()
1409+
instance = spanner_client.instance(instance_id)
1410+
database = instance.database(database_id)
1411+
1412+
# Delete records from SINGERS table satisfying a
1413+
# particular condition and returns the SingerId
1414+
# and FullName column of the deleted records using
1415+
# 'THEN RETURN SingerId, FullName'.
1416+
# It is also possible to return all columns of all the
1417+
# deleted records by using 'THEN RETURN *'.
1418+
def delete_singers(transaction):
1419+
results = transaction.execute_sql(
1420+
"DELETE FROM Singers WHERE FirstName = 'David' "
1421+
"THEN RETURN SingerId, FullName"
1422+
)
1423+
for result in results:
1424+
print("SingerId: {}, FullName: {}".format(*result))
1425+
print("{} record(s) deleted.".format(results.stats.row_count_exact))
1426+
1427+
database.run_in_transaction(delete_singers)
1428+
# [END spanner_dml_delete_returning]
1429+
1430+
13681431
def update_data_with_dml_timestamp(instance_id, database_id):
13691432
"""Updates data with Timestamp from the database using a DML statement."""
13701433
# [START spanner_dml_standard_update_with_timestamp]
@@ -1472,6 +1535,38 @@ def insert_singers(transaction):
14721535
# [END spanner_dml_getting_started_insert]
14731536

14741537

1538+
def insert_with_dml_returning(instance_id, database_id):
1539+
"""Inserts sample data into the given database using a DML statement having a THEN RETURN clause. """
1540+
# [START spanner_dml_insert_returning]
1541+
# instance_id = "your-spanner-instance"
1542+
# database_id = "your-spanner-db-id"
1543+
1544+
spanner_client = spanner.Client()
1545+
instance = spanner_client.instance(instance_id)
1546+
database = instance.database(database_id)
1547+
1548+
# Insert records into the SINGERS table and returns the
1549+
# generated column FullName of the inserted records using
1550+
# 'THEN RETURN FullName'.
1551+
# It is also possible to return all columns of all the
1552+
# inserted records by using 'THEN RETURN *'.
1553+
def insert_singers(transaction):
1554+
results = transaction.execute_sql(
1555+
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
1556+
"(21, 'Luann', 'Chizoba'), "
1557+
"(22, 'Denis', 'Patricio'), "
1558+
"(23, 'Felxi', 'Ronan'), "
1559+
"(24, 'Dominik', 'Martyna') "
1560+
"THEN RETURN FullName"
1561+
)
1562+
for result in results:
1563+
print("FullName: {}".format(*result))
1564+
print("{} record(s) inserted.".format(results.stats.row_count_exact))
1565+
1566+
database.run_in_transaction(insert_singers)
1567+
# [END spanner_dml_insert_returning]
1568+
1569+
14751570
def query_data_with_parameter(instance_id, database_id):
14761571
"""Queries sample data from the database using SQL with a parameter."""
14771572
# [START spanner_query_with_parameter]
@@ -2273,7 +2368,9 @@ def list_instance_config_operations():
22732368
subparsers.add_parser("insert_data_with_dml", help=insert_data_with_dml.__doc__)
22742369
subparsers.add_parser("log_commit_stats", help=log_commit_stats.__doc__)
22752370
subparsers.add_parser("update_data_with_dml", help=update_data_with_dml.__doc__)
2371+
subparsers.add_parser("update_data_with_dml_returning", help=update_data_with_dml_returning.__doc__)
22762372
subparsers.add_parser("delete_data_with_dml", help=delete_data_with_dml.__doc__)
2373+
subparsers.add_parser("delete_data_with_dml_returning", help=delete_data_with_dml_returning.__doc__)
22772374
subparsers.add_parser(
22782375
"update_data_with_dml_timestamp", help=update_data_with_dml_timestamp.__doc__
22792376
)
@@ -2284,6 +2381,7 @@ def list_instance_config_operations():
22842381
"update_data_with_dml_struct", help=update_data_with_dml_struct.__doc__
22852382
)
22862383
subparsers.add_parser("insert_with_dml", help=insert_with_dml.__doc__)
2384+
subparsers.add_parser("insert_with_dml_returning", help=insert_with_dml_returning.__doc__)
22872385
subparsers.add_parser(
22882386
"query_data_with_parameter", help=query_data_with_parameter.__doc__
22892387
)
@@ -2386,8 +2484,12 @@ def list_instance_config_operations():
23862484
log_commit_stats(args.instance_id, args.database_id)
23872485
elif args.command == "update_data_with_dml":
23882486
update_data_with_dml(args.instance_id, args.database_id)
2487+
elif args.command == "update_data_with_dml_returning":
2488+
update_data_with_dml_returning(args.instance_id, args.database_id)
23892489
elif args.command == "delete_data_with_dml":
23902490
delete_data_with_dml(args.instance_id, args.database_id)
2491+
elif args.command == "delete_data_with_dml_returning":
2492+
delete_data_with_dml_returning(args.instance_id, args.database_id)
23912493
elif args.command == "update_data_with_dml_timestamp":
23922494
update_data_with_dml_timestamp(args.instance_id, args.database_id)
23932495
elif args.command == "dml_write_read_transaction":
@@ -2396,6 +2498,8 @@ def list_instance_config_operations():
23962498
update_data_with_dml_struct(args.instance_id, args.database_id)
23972499
elif args.command == "insert_with_dml":
23982500
insert_with_dml(args.instance_id, args.database_id)
2501+
elif args.command == "insert_with_dml_returning":
2502+
insert_with_dml_returning(args.instance_id, args.database_id)
23992503
elif args.command == "query_data_with_parameter":
24002504
query_data_with_parameter(args.instance_id, args.database_id)
24012505
elif args.command == "write_with_dml_transaction":

0 commit comments

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