How to update sqlalchemy orm object by a python dict

Question

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

Answer 1

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)

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

In sqlalchemy 2.0 API, you can use:

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

Answer 6

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.

Answer 7

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()