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)