How to update sqlalchemy orm object by a python dict

Each Answer to this Q is separated by one/two green lines.

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"}

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

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

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

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

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)

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

In sqlalchemy 2.0 API, you can use:

stmt = update(User).where(User.name == "john").values(**your_data)
session.execute(stmt)

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.

I found this solution while working with flask-sqlalchemy then test it on sqlalchemy & it works as well:

dict = {name: "marchel", fullname: "richie marchel"}
session.execute(update(User).filter_by(id=3).values(**dict))
session.commit()


The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .