Updating a row using SQLAlchemy ORM

Question

Note: I am not using Flask-SQLAlchemy here, I'm using SQLAlchemy ORM

I've seen several answers on StackOverflow saying that this is a way to update a record:

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

However I get an error saying:

AttributeError: 'FoobarModel' object has no attribute 'update'

I was able to update like this, however:

foobar = session.query(FoobarModel).get(foobar_id)
foobar.name = 'New Foobar Name!'
session.commit()
session.flush()

So then I tried something like this (so that I don't have to write out every property):

new_foobar = {'name': 'New Foobar Name!'}
old_foobar = session.query(FoobarModel).get(foobar_id)

for property in new_foobar:
  old_foobar[property] = new_foobar[property]

session.commit()
session.flush()

However I then get this error:

TypeError: 'FoobarModel' object does not support item assignment

After doing some digging I found out that it's because even this wouldn't work:

print(old_foobar['name'])

Which throws the error:

TypeError: 'FoobarModel' object is not subscriptable

Honestly the first syntax would be the best in my opinion, but I can't get it to work. Any ideas?

Answer 1

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.