Sqlalchemy - Select for Update Example

SQLAlchemy update multiple rows in one transaction

Yes, updating a larger number of rows with a single bulk UPDATE statement will be a lot faster than using individual UPDATEs on each and every object. An IN filter would only help you limit what rows are updated, but you still need to tell the database what value to use for the col2 updates.

You can use a CASE ... WHEN ... THEN construct for that, with the case() function:

from sqlalchemy.sql import case

query(MyTable).filter(
    MyTable.col1.in_(payload)
).update({
    MyTable.col2: case(
        payload,
        value=MyTable.col1,
    )
}, synchronize_session=False)

The above a) selects rows where the col1 value is a key in the payload dictionary, then b) updates the col2 column value using a CASE statement that picks values from that same dictionary to update that column based on matching col1 against the keys.

With payload set to {'x': 'y', 'a': 'b', 'c': 'd'}, the above executes the following query (give or take the exact order of WHEN clauses and values in the IN test):

UPDATE mytable
SET
    col2=CASE mytable.col1
        WHEN 'x' THEN 'y'
        WHEN 'a' THEN 'b'
        WHEN 'c' THEN 'd'
    END
WHERE
    mytable.col1 IN ('x', 'a', 'c')

I set synchronize_session to False there, as updating all possible cached MyTable instances at once is perhaps not the best idea when updating a large number of rows. Your other options are 'evaluate' and 'fetch'.

• We can't use the default 'evaluate' (which would find existing objects in the session that match the where clause, to update in-place), because SQLAlchemy currently doesn't know how to process an IN filter (you get an UnevaluatableError exception).

• If you do use 'fetch' then all instances of MyTable cached in the session that were affected are updated with new values for col2 (as mapped by their primary key).

Note that a commit would expire the session anyway, so you'd only want to use 'fetch' if you need to do some more work with the updated rows before you can commit the current transaction.

See the Query.update() documentation for more information on what synchronize_session options you have.

How to update SQLAlchemy row entry?

user.no_of_logins += 1
session.commit()

Run UPDATE SET parameterized sql statement in Connection.execute sqlalchemy

The bind params aren't MySQL style, and as you're passing in plain text to engine.execute(), SQLAlchemy isn't applying a dialect to the query before executing it.

Try this:

engine.execute("SELECT :val", {"val": 1})  # will fail, same as your query

...and then this:

engine.execute("SELECT %(val)s", {"val": 1})  # will execute

Wrapping the query with text() will let SQLAlchemy handle the proper bind style:

from sqlalchemy import text  # or you can use db.text w/ flask-sqlalchemy
engine.execute(text("SELECT :val"), {"val": 1})

One other thing to note is that SQLAlchemy will automatically handle construction of the UPDATE query for you, respecting the values in the parameter dict, e.g.:

id_ = 1
params = {'username': "testing", "email": "testing@testing.ts"}
User.__table__.update().values(params).where(id=id_)
# UPDATE user SET username=%(username)s, email=%(email)s WHERE user.id = %(id_1)s

params = {'username': "testing"}
User.__table__.update().values(params).where(id=id_)
# UPDATE user SET username=%(username)s WHERE user.id = %(id_1)s

params = {'username': "testing", "unexpected": "value"}
User.__table__.update().values(params).where(id=id_)
# sqlalchemy.exc.CompileError: Unconsumed column names: unexpected

SQLAlchemy: update from_select

Assuming that t_node is node Table instance, while t_node_node - node_node Table instance, see the statement below.

upd = (t_node.update()
        .values(
            parent_id = t_node_node.c.parent_id,
            label = t_node_node.c.label,
            )
        .where(t_node_node.c.child_id == t_node.c.node_id)
        )

Read more on Inserts, Updates and Deletes documentation for more information.

Flask-SQLAlchemy with_for_update() row lock

After struggling for one whole day, i found the problem.

user = User.query.with_for_update().filter_by(id=userid).first()

should be

result = db.session.query(User.money).with_for_update().filter_by(id=userid).first()
money = result[0]
user.money = money - 0.1

Yes, so simple but annoying

Updating a row using SQLAlchemy ORM

I believe you are looking for something like this for your update query:

session.query(FoobarModel).filter(FoobarModel.id == foobar_id).update({'name': 'New Foobar Name!'})

Since update() belongs to Query, and filter() does return a Query object, this will work, contrary to trying to call update() on your FoobarModel object (which does not have such a function) returned by Query.get(), see also here.

As for looping over your properties and assigning them by name, you could do this with setattr and a dict, like this:

foobar = session.query(FoobarModel).get(foobar_id)

props = {'name': 'my new name'}

for key, value in props.items():
    setattr(foobar, key, value)

session.commit()
session.flush()

This is obviously a little pointless with just one property, but maybe it will come in handy at some point.

---

Related Topics