How to update sqlalchemy orm object by a python dict

35

votes

the dict's key names are mapping to the sqlalchemy object attrs

ex:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

can update from id = 3, {name: "diana"} or id = 15, {name: "marchel", fullname: "richie marchel"}

pythonormsqlalchemy

53

votes

You can use setattr() to update attributes on an existing SQLAlchemy object dynamically:

user = session.query(User).get(someid)

for key, value in yourdict.iteritems():
    setattr(user, key, value)

19

votes

I have another solution here. It would be handy to define model method as following.

class ModelName(db.Model):
    """
    docstring here
    """
    ...

    def update(self, **kwargs):
        for key, value in kwargs.items():
            if hasattr(self, key):
                setattr(self, key, value)

I hope it would solve your problem.

Thank you

13

votes

Depending on your usecase (if you don't need to validate or infer anything from the model), you can save one DB call by using filter_by with id to get a specific row, and update it using a dictionary like you initially wanted.

user_query = session.query(User).filter_by(id=someid)
data_to_update = dict(name="marchel", fullname="richie marchel")

user_query.update(data_to_update)

You might also need to add synchronize_session=False keyword argument to your update call, depending on the type of your session (if you use scoped_session):

user_query.update(data_to_update, synchronize_session=False)

6

votes

base on answer of @martijn-pieters, you can not only dynamic update column with setattr, but also can use dynamic table and column combine with getattr and setattr

example:

# models.py
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

# update.py
import models

def dynamic_update(dynamic_table, col_id, dynamic_cols):
    """
    dynamic_table: name of the table, "User" for example
    col_id: id of which column you want to update
    dynamic_cols: key value pairs {name: "diana"}
    """
    if hasattr(models, dynamic_table):
        table = getattr(models, dynamic_table)
        col_info = table.query.filter_by(id=col_id).first()
        for (key, value) in dynamic_cols.items():
            if hasattr(table, key):
                setattr(col_info, key, value)
                session.commit()

BTW, you can get more info about setattr, getattr, hasattr from python offical doc https://docs.python.org/2/library/functions.html#setattr

https://docs.python.org/2/library/functions.html#getattr

https://docs.python.org/2/library/functions.html#hasattr

0

votes

I think the simplest way is to use sqlalchemy update with a filter

def update_item(db: Session, item_id: int, item: ItemUpdate):
    db.query(Item).filter(id=item_id).update(item.dict())
    db.commit()

Ensure you are always filtering on a primary key to avoid updating more than a single row. This could be done as a check in the code before committing the session.

How to update sqlalchemy orm object by a python dict

5 Answers