Using SQLAlchemy, how do i update the db using json only?

Question

some_json

{ 
    "id": 1,
    "foo": "foo",
    "bar": "bar",
    "baz": "baz"
}

assume all the keys in some_json are columns

then normally when i update the database, i'll do something like

item = db.query.filter_by(id=some_json['id']).first()
if item:
    item.foo = some_json['foo']
    item.bar = some_json['bar']
    item.baz = some_json['baz']
    db.session.add(item)
    db.commit()

however, this can obviously be tedious if have more and more keys is there an easier way to update the table rows just by simply passing the json?

If isinstance(some_json, dict) is True then you could try passing some_json as parameter values to an insert() statement, e.g., db.session.execute(item.__table__.insert(), some_json) — Gord Thompson
– Gord Thompson, Commented Jan 5, 2021 at 0:11
You should be able to do instance = MyModel(**json_dict); db.add(instance) if json_dict is a flat dict and its keys match column names in MyModel. — snakecharmerb
– snakecharmerb, Commented Jan 5, 2021 at 7:44

Nour-Allah Hussein · Accepted Answer · 2021-01-06 16:41:04Z

1

By converting json to dectionery, which maps the table model table_model in your case, you can write your code as follows:

a= {
    "id": 1,
    "foo": "foo",
    "bar": "bar",
    "baz": "baz"
    }
item = table_model.query.filter_by(id=some_json['id']).first()
if item:
    item=table_model(**a)
    db.session.add(item)
    db.commit()

answered Jan 6, 2021 at 16:41

Nour-Allah Hussein

1,4591 gold badge9 silver badges17 bronze badges

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

1 Comment

steve Over a year ago

If you desired more complex handling of the data you could use something like the marshmallow-sqlalchemy library. Marshmallow would allow you quickly load orm objects from json or a dictionary. You could also define pre/post load functions on certain ORM attributes (columns) so massage the data that comes in. E.g you could define a pre load function to encrypt a password before it's saved. Super useful in building standard rest apis where the json passed between front and backend is usually just json'ed mappings of database orm objects.

Collectives™ on Stack Overflow

Using SQLAlchemy, how do i update the db using json only?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related