Returning data from modified rows

[caiopizzol]

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)
   )

2. 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

3. 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

[andrii-harbour]

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)