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?

It's not possible. Query.update is clearly documented to return "the count of rows matched as returned by the database’s “row count” feature", not the result of the update query. — univerio
– univerio, Commented Aug 23, 2017 at 18:00

Chidiebere · Accepted Answer · 2020-09-04 10:14:56Z

9

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.

answered Sep 4, 2020 at 10:14

Chidiebere

1,1221 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Weavers Over a year ago

Hi! I'd like to ensure that other threads can't go between the update and the commit. Does this solution accomplish that?

Chidiebere Over a year ago

You would have to make the transaction(or db session) SERIALIZABLE. See this link for more details

Chidiebere Over a year ago

You can get more information about DB isolation levels from this video

djkern · Accepted Answer · 2018-04-30 22:41:20Z

6

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

answered Apr 30, 2018 at 22:41

djkern

4823 silver badges11 bronze badges

4 Comments

courtsimas Over a year ago

Don't believe that works for the way they're using query.update() ...

mymedia Over a year ago

what does table mean here? what is table.c?

majTheHero Over a year ago

@mymedia table is the sqlalchemy Table object. table.c is the collection of columns in that table. Check out docs.sqlalchemy.org/en/13/core/selectable.html

majTheHero Over a year ago

@courtsimas the APIs are a bit different if you work with selectables or with session and query objects, but all support returning if the backend supports returning I think.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related