How to use RETURNING for query.update() with sqlalchemy

Question

I want to specify the return values for a specific update in sqlalchemy.

The documentation of the underlying update statement (sqlalchemy.sql.expression.update) says it accepts a "returning" argument and the docs for the query object state that query.update() accepts a dictionary "update_args" which will be passed as the arguments to the query statement.

Therefore my code looks like this:

session.query(
  ItemClass
).update(
  {ItemClass.value: value_a},
  synchronize_session='fetch',
  update_args={
    'returning': (ItemClass.id,)
  }
)

However, this does not seem to work. It just returns the regular integer.

My question is now: Am I doing something wrong or is this simply not possible with a query object and I need to manually construct statements or write raw sql?

Answer 1

The full solution that worked for me was to use the SQLAlchemy table object directly.

You can get that table object and the columns from your model easily by doing

table = Model.__table__
columns = table.columns

Then with this table object, I can replicate what you did in the question:

from your_settings import db

update_statement = table.update().returning(table.id)\
    .where(columns.column_name=value_one)\
    .values(column_name='New column name')
result = db.session.execute(update_statement)

tuple_of_results = result.fetchall()

db.session.commit()

The tuple_of_results variable would contain a tuple of the results. Note that you would have to run db.session.commit() in order to persist the changes to the database as you it is currently running within a transaction.

You could perform an update based on the current value of a column by doing something like:

update_statement = table.update().returning(table.id)\
    .where(columns.column_name=value_one)\
    .values(like_count=table_columns.like_count+1)

This would increment our numeric like_count column by one.

Hope this was helpful.

Answer 2

Here's a snippet from the SQLAlchemy documentation:

# UPDATE..RETURNING
result = table.update().returning(table.c.col1, table.c.col2).\
    where(table.c.name=='foo').values(name='bar')
print result.fetchall()