CODEWITHSUNDEEP
pythonsqlalchemyupdates
Justin williams
Justin williams

Reputation: 604

SQLAlchemy Update row where session has gone out of scope

I have an existing get method.

    def get_parameter(self, app, my_business_key):
        my_db = MultiTenantSQLAlchemy(app)
        session = my_db.get_session(app)
        return session.query(MyParameter).filter(MyParameter.my_business_key.in_(my_business_key)).all()

Since I don't have the primary key and I'm updating by a business key, my assumption is that I have to pull the records first.

I get the parameters object, do the updates to it and then want to save them. 

    def save_parameters(self, app, my_business_key, my_parameters):
        my_db = MultiTenantSQLAlchemy(app)
        session = lro_db.get_session(app)

        # session.Update(my_parameters)    ?????

        session.flush()
        session.commit()

Every example I find on here shows creating the session, doing the select, doing the update, then flush/commit all in one method. I'd rather not duplicate the GET if I don't have to.

Ideally I'd love for this to all be one singular query like bulk_update_mappings and only go to the DB once but I don't think I can do that without the primary key.

Upvotes: 0

Views: 520

Answers (1)

9000
9000

Reputation: 40884

Update the table directly, do not fetch / update / save instances if you don't have to.

my_table = MyORMClass.__table__
update_statement = (
    my_table.update()
            .where(my_table.c.business_key == bindparam('key'))
            .values(field_to_set=bindparam('value')))
session.execute(update_statement, [
    {'key': 100, 'value': 'value for 100'},
    {'key': 200, 'value': 'value for 200'},
])

Here the update happens in one round-trip.

the idea is that you use bindparam to bind your data to named placeholders, and then pass a list of dicts where keys match the names of the placeholders. In this case, it's 'key' and 'value', but it can be anything, and of course you can use many arguments in .values(). The example assumes that the business key field is named business_key in your table, and the field you want to update is named field_to_set.

Please note the difference between .where() ans .values(): one takes a condition formed by column object and ==, the other takes plain named arguments.

Of course you can build the update list programmatically, I just show a constant here for simplicity.

See this SQLAlchemy doc for reference.

Upvotes: 1

Related Questions