Updating a row using SQLAlchemy ORM

Question

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?

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

bgse · Accepted Answer · 2017-12-10 03:27:22Z

40

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.

edited Dec 10, 2017 at 3:27

answered Dec 10, 2017 at 2:59

bgse

8,6672 gold badges43 silver badges43 bronze badges

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

2 Comments

Kyle Morgan Over a year ago

+1 for all the great edits to your answer. This was the problem. I was using .get() when I should have been using .filter(). Thanks!

Deepa MG Over a year ago

session.flush() should come first, then session.commit(). Since a commit() always flushes.

gbiz123 · Accepted Answer · 2024-01-04 21:56:36Z

Here is a generic function for SQLAlchemy2.0 based on bgse's solution.

from typing import Type
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

from . import models

_ENGINE = create_engine(os.environ["DATABASE_URL"])

def update(id: int, t: Type[models.Base], props: dict) -> None:
    """Generic function to update an entry in database by ID

    Args:
        id (int): ID of object to update
        t (Type[models.Base]): Type of object to update
        props (dict): Dictionary containing properties to update
    """
    with Session(_ENGINE) as session:
        with session.begin():
            obj = session.get(t, id)
            for field, value in props.items():
                setattr(obj, field, value)

Vladimir Stazhilov · Accepted Answer · 2020-05-11 13:50:33Z

-1

Worked

device = models.Device.query.filter(models.Device.device_identifier == device_identifier).first()

setattr(device, 'updated_at', datetime.now())
setattr(device, 'ip_address', ip_address)
db.session.commit()

Didn't work

device.is_active = True
device.id_address = ip_address
device.updated_at = datetime.now()
print(device.ip_address)
db.session.commit()

answered May 11, 2020 at 13:50

Vladimir Stazhilov

1,9544 gold badges31 silver badges64 bronze badges

1 Comment

Jens Over a year ago

What does “didn’t work” mean? An error? An expectation not fulfilled?

Collectives™ on Stack Overflow

Updating a row using SQLAlchemy ORM

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related