Michael, Thanks for the quick response. In this example, `bar` is *not* the primary key, so I guess using the ORM here would be out of the question.
What's your recommendation for refreshing the `foo` object after executing the `session.execute(do_update_stmt)` statement? Should I do an `INSERT...ON CONFLICT UPDATE...RETURNING id` and fetch the updated `foo` by ID, or is there a more preferred approach? Thanks again, Calvin On Wednesday, February 15, 2017 at 12:24:33 PM UTC-8, Mike Bayer wrote: > > Well first off the ORM on the persistence side only identifies objects by > primary key. Is "bar" the primary key here ? That would be one > requirement. > > The semantics of INSERT on conflict most closely match those of > Session.merge(). If you're dealing with primary key, merge will do this > operation right now but it uses separate SELECT and INSERT/UPDATE. an ORM > integration of merge() and INSERT on conflict would need some very > motivated contributors to come on board and help implement and test. It > can be done as a third party extension to start with. > > On Feb 15, 2017 2:13 PM, "Calvin Young" <calvin...@gmail.com <javascript:>> > wrote: > >> I use the SQLAlchemy ORM in my application, and I know I can use >> something the following to perform an `INSERT...ON CONFLICT` statement: >> >> from sqlalchemy.dialects.postgresql import insert >> >> >> >> class Foo(Base): >> ... >> bar = Column(Integer) >> >> >> foo = Foo(bar=1) >> >> >> insert_stmt = insert(Foo).values(bar=foo.bar) >> do_update_stmt = insert_stmt.on_conflict_do_update( >> set_=dict( >> bar=insert_stmt.excluded.bar, >> ) >> ) >> >> session.execute(do_update_stmt) >> >> Is there a better solution that doesn't require dropping into the >> Expression Language? It'd be great if we had a solution that automatically >> detected the fields that need to be inserted / update, and that >> automatically refreshed the `foo` instance after the committing to the db. >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+...@googlegroups.com <javascript:>. >> To post to this group, send email to sqlal...@googlegroups.com >> <javascript:>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.