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.

Sign up for GitHub

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

Jump to bottom

Returning data from modified rows #332

Open
caiopizzol opened this issue Jul 7, 2023 · 1 comment · May be fixed by #335
Open

Returning data from modified rows #332

caiopizzol opened this issue Jul 7, 2023 · 1 comment · May be fixed by #335
Assignees
@asthamohta
Labels
api: spanner Issues related to the googleapis/python-spanner-sqlalchemy API. priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@caiopizzol
Copy link

caiopizzol commented Jul 7, 2023 •
edited

Environment details

  • Programming language: python
  • OS: 13.4 (22F66)
  • Language runtime version: 3.11
  • Package version:
    sqlalchemy-spanner==1.6.1
    SQLAlchemy==2.0.15

Steps to reproduce

  1. Create an update/insert statement
update_stmt = (
    update(DBModel)
    .values(
        **db_record.dict(exclude_none=True)
    )
    .where(
        DBModel.number == db_record.number
    )
    .returning(DBModel.unique_id)
)
  1. Generated SQL statement
UPDATE
  db_table
SET
  db_col.value = :db_schema_col_value
WHERE
  db_table.number = :db_schema_col_number
RETURNING
  db_table.unique_id
  1. Expected GoogleSQL statement
    ref: https://cloud.google.com/spanner/docs/reference/standard-sql/dml-syntax#insert-and-then-return
UPDATE
  db_table
SET
  db_col.value = :db_schema_col_value
WHERE
  db_table.number = :db_schema_col_number
THEN RETURN
  db_table.unique_id
@caiopizzol caiopizzol added priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. labels Jul 7, 2023
@product-auto-label product-auto-label bot added the api: spanner Issues related to the googleapis/python-spanner-sqlalchemy API. label Jul 7, 2023
@andrii-harbour
Copy link

Looks like default dialect for SQLalchemy and spanner-wrapper is PostgreSQL. Didn't find any options to switch it to GoogleSQL. Custom functions using text() might work, but definitely missing feature in the library.

+1 to the request

P.S. My usecase has the same issue, but is related to the ANY (in PostgreSQL) vs ARRAY_INCLUDES_ANY (in GoogleSQL)

@harshachinta harshachinta linked a pull request Jul 30, 2023 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@asthamohta asthamohta

Labels
api: spanner Issues related to the googleapis/python-spanner-sqlalchemy API. priority: p2 Moderately-important priority. Fix may not be included in next release. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

feat: support dml returning harshachinta/python-spanner-sqlalchemy
3 participants
@caiopizzol @asthamohta @andrii-harbour