Sqlalchemy update json column using binaryexpression in python

Question

I am trying too update the JSON datatype column of postgresql table using Sqlalchemy binaryexpression in python.

Example

unit_price = 10.10
final_data = {
    "price_details": {
        "unit_price":unit_price,
        "total_amt":testmodel.qty * unit_price
    }
}

test_db = session.query(testmodel).filter(testmodel.id >= 10)
test_db.update(final_data,synchronize_session=False)

In above example i am trying to calculate total_amt using binaryexpression. But I am getting error.

StatementError: (exceptions.TypeError) <sqlalchemy.sql.elements.BinaryExpression object at 0x6024810> is not JSON serializable

Ilja Everilä · Accepted Answer · 2017-09-11 12:01:44Z

1

In order to use DB values in your update, you have to use the DB JSON features instead of passing serialized JSON from Python – not to mention that the Python JSON encoder has no idea what to do with an SQL expression. Consulting the Postgresql JSON functions and operators it seems that json_build_object() or its JSONB variant are what you're looking for:

unit_price = 10.10
final_data = {
    "price_details": func.json_build_object(
        "unit_price", unit_price,
        "total_amt", testmodel.qty * unit_price
    )
}

test_db = session.query(testmodel).filter(testmodel.id >= 10)
test_db.update(final_data, synchronize_session=False)

edited Sep 11, 2017 at 12:01

answered Sep 11, 2017 at 11:18

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

Collectives™ on Stack Overflow

Sqlalchemy update json column using binaryexpression in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related