SQLAlchemy: a better way for update with declarative?

Question

Let's say I have a user table in declarative mode:

class User(Base):
    __tablename__ = 'user'
    id = Column(u'id', Integer(), primary_key=True)
    name = Column(u'name', String(50))

When I know user's id without object loaded into session, I update such user like this:

ex = update(User.__table__).where(User.id==123).values(name=u"Bob Marley")
Session.execute(ex)

I dislike using User.__table__, should I stop worrying with that?

Is there a better way to do this?

update http://docs.sqlalchemy.org/en/rel_1_0/core/dml.html#sqlalchemy.sql.expression.update — The Demz, May 13 '15 at 14:33

score 65 · Accepted Answer · answered Apr 13 '10 at 20:19

65

There's also some update capability at the ORM level. It doesn't handle any tricky cases yet but for the trivial case of single row update (or bulk update) it works fine. It even goes over any already loaded objects and applies the update on them also. You can use it like this:

session.query(User).filter_by(id=123).update({"name": u"Bob Marley"})

answered Apr 13 '10 at 20:19

Ants Aasma

53,288
15
90
97

2

This doesn't take into account the user assigned variable names for the columns - If name were declared user_name = Column("name", String(50)), this would fail b/c the actual name of the column is required. Can anyone explain why this is? – Kira Jul 02 '12 at 20:40
1

Shouldn't that be either `.get(123)` or `.filter_by(id == 123)` ? – Brendan Aug 04 '14 at 22:25
3

@Brendan `filter` and `filter_by` are different methods. The former takes an expression as an argument while the latter takes keyword arguments. – Mark E. Haase Aug 18 '15 at 13:36
3

@Kira: Late response, but I'll add it in case anyone else needs it. session.query(User).filter_by(id=123).update({User.name: "Bob Marley"}) – sirdodger Sep 14 '15 at 22:42
1

Would `session.query(User).with_for_update().filter_by(id=123).update({"name": u"Bob Marley"})` be better to ensure atomicity? – alphabetasoup Jan 31 '17 at 01:14
3

@RichardLaw No, `FOR UPDATE` is not a part of an `UPDATE` statement and SQLA simply ignores the `with_for_update()`. – Ilja Everilä Nov 13 '18 at 22:31
So you can't update a single row but you can update a list of rows? Is that why `.get().update()` doesn't work while the filter method does? It's very confusing. – cglacet Jan 30 '21 at 10:39

score 20 · Answer 2 · answered Apr 13 '10 at 18:24

You're working on clause level here, not on model/entity/object level. Clause level is lower than mapped objects. And yes, something have to be done to convert one terms into others.

You could also stay on object level and do:

session = Session()
u = session.query(User).get(123)
u.name = u"Bob Marley"
session.commit()

but it will be significantly slower since it leads to the mapped object construction. And I'm not sure that it is more readable.

In the example your provided I see the most natural and “right” solution. I would not worry about little __table__ magic.

score 4 · Answer 3 · answered May 13 '15 at 14:52

Similar functionality is available via the update() method on Table object.

class User(Base):
    __tablename__   = 'user'
    id = Column('id', Integer(), primary_key=True)
    name = Column('name', String(50))

stmt = User.__table__.update().where(User.id==5).values(name='user #5')

To use User.__table__ is how its done in SQLAlchemy.

SQLAlchemy: a better way for update with declarative?

3 Answers3

Linked

Related