Using `INSERT...ON CONFLICT` with ORM

[Calvin Young]

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.

[Mike Bayer]

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.  

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Calvin Young]

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

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On Feb 17, 2017 2:00 PM, "Calvin Young" <calvin...@gmail.com> wrote:

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?

You could take the ID via RETURNING, and then do a simple query.get(), sure.  

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Ketan Patel]

Hi Mike,

Just checking if ORM way of handling the conflict during the insert was ever implemented after this discussion. 

[Mike Bayer]

nope, there's a vague plan in https://github.com/sqlalchemy/sqlalchemy/issues/5441 for how this might work but it might keep getting pushed off as it's a big job and there are many higher priority items.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a765d0fa-329f-482c-a2d0-3e6ce0da5a8an%40googlegroups.com.

[Ketan Patel]

Thanks Mike. 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ef69252e-832f-43a9-aeba-03469f3f8971%40www.fastmail.com.

--

Thank You.

Regards,

Ketan Patel