Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

fix: support THEN RETURN for insert, update, delete #503

Merged
merged 3 commits into from
Nov 28, 2024

Conversation

olavloite
Copy link
Contributor

@olavloite olavloite commented Nov 8, 2024

Support THEN RETURN clauses for INSERT, UPDATE, and DELETE statements.

Without this change, inserting a value into a table that uses a bit-reversed sequence for generating a primary key looks like this:

2024-11-26 18:35:31,309 INFO sqlalchemy.engine.Engine SELECT GET_NEXT_SEQUENCE_VALUE(SEQUENCE ticket_sale_id)
2024-11-26 18:35:31,309 INFO sqlalchemy.engine.Engine [raw sql] []
2024-11-26 18:35:31,312 INFO sqlalchemy.engine.Engine INSERT INTO ticket_sales (id, customer_name, seats, venue_code, start_time, singer_id) VALUES (%s, %s, %s, %s, %s, %s)
2024-11-26 18:35:31,312 INFO sqlalchemy.engine.Engine [generated in 0.00307s] [4611686018427387904, 'Alice Doe', ['A010', 'A011', 'A012'], 'CH', datetime.datetime(2024, 11, 7, 19, 30), '13cd9756-103a-48ec-97c1-03dd32540120']

With this change, the same example looks like this:

2024-11-26 18:37:48,981 INFO sqlalchemy.engine.Engine INSERT INTO ticket_sales (id, customer_name, seats, venue_code, start_time, singer_id) VALUES ( GET_NEXT_SEQUENCE_VALUE(SEQUENCE ticket_sale_id), %s, %s, %s, %s, %s) THEN RETURN ticket_sales.id
2024-11-26 18:37:48,981 INFO sqlalchemy.engine.Engine [generated in 0.00008s] ['Alice Doe', ['A010', 'A011', 'A012'], 'CH', datetime.datetime(2024, 11, 7, 19, 30), '40879c57-a640-4652-8d35-ea0f6970ff85']

In other words: 1 round-trip less to Spanner.

Fixes #498

Support THEN RETURN clauses for INSERT, UPDATE, and DELETE statements.

Fixes #498
@product-auto-label product-auto-label bot added the api: spanner Issues related to the googleapis/python-spanner-sqlalchemy API. label Nov 8, 2024
@olavloite olavloite marked this pull request as ready for review November 26, 2024 17:33
@olavloite olavloite merged commit ac64472 into main Nov 28, 2024
17 checks passed
@olavloite olavloite deleted the then-return-for-auto-generated branch November 28, 2024 13:22
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: spanner Issues related to the googleapis/python-spanner-sqlalchemy API.
Projects
None yet
Development

Successfully merging this pull request may close these issues.

SpannerDialect does not register RETURNING as supported for insert, update, delete
2 participants