On Fri, Nov 17, 2017 at 9:17 AM, Petar <petargyuro...@gmail.com> wrote: > I want to override my `Session` such that it performs and `INSERT ON > CONFLICT` whenever I `add` an object. > > `merge` is too slow for my use-case: it has to perform a `SELECT` first, and > I want to reduce my latency as much as I can. > > > Currently this is the standard behaviour: > > user = User(id=1, name='John') # suppose we already have a record with > id=1 > session.add(user) # this tries to INSERT, which will raise an Integrity > error > session.commit() > > > > I want to be able to do something like this: > > user = User(id=1, name='John', update=True) > session.add(user) # this now does INSERT ON CONFLICT DO UPDATE SET ... > session.commit() > > > and if `update=False` then perform `DO NOTHING` > > > I assume I will need to monkey-patch around the Insert class but I am not > 100% sure. Any idea how to achieve this in an elegant way?
it would not happen within the Session as it exists today. the .add() method doesn't emit an INSERT, it just adds the object to a collection. the INSERT occurs within the flush process. The good news is that INSERT/UPDATEs are actually bundled very close together within the flush process, but actually since this object is given as pending, it would only be the INSERT process that needs to change. so it starts first here: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/persistence.py#L167 then objects like this would go into here: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/persistence.py#L799 . it is possible that an _emit_insert_w_merge() type of function could be produced here. However, a lot of the behavioral contracts of the ORM would either be impossible, or be very difficult to maintain, including the "versioning" feature, the ability to correctly invoke Python-side column defaults (because we don't know if this is an INSERT or UPDATE) as well as the ability to receive the values of server-side defaults (same reason). whether insert_w_merge() is called upon could depend upon various factors. We could have a new session.add_for_merge() method that specifies it at that level (probably best), a flag can be added to the instance_state indicating the pending object is of this style. Also, if this were to become a real feature, it would have to work across the various implementations of "merge" for different databases, e.g. postgresql vs. mysql vs. ?. this would not be particularly easy to implement, especially for someone unfamiliar with the codebase (I have 12 years w/ it :) ). So if you just need this to work you might want to stick with your own external function that just calls upon the Core INSERT ON CONFLICT. > > > -- > 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. -- 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.
