How to Update Sqlalchemy Orm Object by a Python Dict

Update SQLAlchemy orm object with changed python dict

I realised that I was able to work around my problem by simply not using the .filter method. Hence the example below works just fine.

    transaction1 = {'item1': 'banana',
                    'item2': 'apple',
                    'item3': 'sugar',
                    'item4': 'coke',
                    'item5': 'candy'}
    transaction2 = {'item1': 'pizza',
                    'item2': 'water'}

    new_obj = Person(first_name='Bob', last_name='Smith')
    session.add(new_obj)

    new_transaction = Transactions(transactions=transaction1, person_object=new_obj)
    session.add(new_transaction)

    new_transaction = Transactions(transactions=transaction2, person_object=new_obj)
    session.add(new_transaction)
    session.commit()

    test = session.query(Transactions).all()

    for tmp in test:
        print(tmp.transactions)

    new_transaction.transactions['item1'] = 'curry banana'
    session.commit()

    test = session.query(Transactions).all()

    for tmp in test:
        print(tmp.transactions)

In SQLAlchemy, how does the dict update method interact with the ORM?

Yes. By default SQLAlchemy doesn't track changes inside dict attributes. To make it track changes, you can use the mutable extension:

class MyTable(db.Model):
    ...
    my_json_column = db.Column(MutableDict.as_mutable(JSON))

Updating an entity with sqlalchemy ORM

Table objects are not part of SQLAlchemy ORM, they are part of SQLAlchemy Core. In order to use ORM you'll want to do something like this:

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session

engine = create_engine("sqlite://", echo=True)

# create test environment
with engine.begin() as conn:
    conn.exec_driver_sql("CREATE TABLE my_thing (id int primary key, sell_price int)")
    conn.exec_driver_sql("INSERT INTO my_thing (id, sell_price) VALUES (1, 123)")

Base = automap_base()


class MyThing(Base):
    __tablename__ = "my_thing"


Base.prepare(autoload_with=engine)

# test
with Session(engine) as sess:
    thing_1 = sess.scalar(select(MyThing).where(MyThing.id == 1))
    thing_1.sell_price = 456
    sess.commit()
""" SQL emitted:
UPDATE my_thing SET sell_price=? WHERE my_thing.id = ?
[generated in 0.00032s] (456, 1)
"""

easier method to update SQLAlchemy object from form data

You might want to take a look at flask-wtf's populate_obj function. Furthermore, the given example shows you how to fill the form by passing the requested database object to your form using the obj argument.

@app.route('/edit/<int:post_id>', methods=['GET', 'POST'])
def edit_post(post_id):
    post = BlogPost.query.get_or_404(post_id)
    form = CreatePostForm(request.form, obj=post)
    if form.validate_on_submit():
        form.populate_obj(post)
        db.session.commit()
        return redirect(url_for('show_post', index=post_id))
    return render_template('make-post.html', **locals())

How to update specific row in Sqlalchemy ORM for a non unique primary key

Figured it out, you can import the update function from sqlalchemy if you have an update that is more complicated than just using the primary key

    stmt = update(CategoryTable).where(CategoryTable.category_key == cat.category_key,
    CategoryTable.current_flag == '1', CategoryTable.effective_from == cat.effective_from).values(
        effective_to=effective_date, current_flag='0').execution_options(synchronize_session="fetch")
    db.execute(stmt)

---

Related Topics