"How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?"

[Maurice Schleußinger]

Is there no other way?

http://stackoverflow.com/questions/27635933/how-can-i-know-which-fiels-cause-integrityerror-when-inserting-into-a-table-with/27884632#27884632

Parsing an exception with regex just doesn't feel right…

[Michael Bayer]

The two other ways are that you can pre-select the rows, or use a MERGE / upsert approach (you’d have to roll that yourself).

[Maurice Schleußinger]

Thanks for the reply!

Since I create objects concurrently I can not predict and therefore not pre-select all rows which could possibly provoke IntegrityErrors.

On the other hand Session.merge() seams to provide the functionality which could resolve my problem. In my setup many processes create a number of objects which could occur multiple times (in other words throw an IntegrityError on commit). That can happen in one process (which I can handle otherwise), in between multiple processes and between a process and the consistent state in the database (which is my main problem ATM). 

I just read the corresponding part in the SQLAlchemy docs. So if I use Session.merge() with the load=True argument (instead of Session.add()) the session should create the corresponding object if it does not exists, avoid duplicates within one session and also update an existing entry in the database?

Also it seems that merging only works for primary keys. So if I had the problem with an unique key, I still would have to parse the exception, right?

[Michael Bayer]

On Feb 23, 2015, at 8:49 AM, Maurice Schleußinger <m.schle...@gmail.com> wrote:

Thanks for the reply!

Since I create objects concurrently I can not predict and therefore not pre-select all rows which could possibly provoke IntegrityErrors.

On the other hand Session.merge() seams to provide the functionality which could resolve my problem. In my setup many processes create a number of objects which could occur multiple times (in other words throw an IntegrityError on commit). That can happen in one process (which I can handle otherwise), in between multiple processes and between a process and the consistent state in the database (which is my main problem ATM). 

I just read the corresponding part in the SQLAlchemy docs. So if I use Session.merge() with the load=True argument (instead of Session.add()) the session should create the corresponding object if it does not exists, avoid duplicates within one session and also update an existing entry in the database?

Also it seems that merging only works for primary keys. So if I had the problem with an unique key, I still would have to parse the exception, right?

Ah ok, yes session.merge() works this way, but it does emit a SELECT.   If you were looking to emit less SQL I was referring to an operation like MySQL's REPLACE statement.   

But if merge works for you then stick with that.   You can write your own function that does something similar for other fields that are not the PK.    The recipe below is one way to do this.

https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject

On Thursday, February 19, 2015 at 5:29:56 PM UTC+1, Michael Bayer wrote:

Maurice Schleußinger <m.schle...@gmail.com> wrote:

> Is there no other way?
>
> http://stackoverflow.com/questions/27635933/how-can-i-know-which-fiels-cause-integrityerror-when-inserting-into-a-table-with/27884632#27884632
>
> Parsing an exception with regex just doesn't feel right…


The two other ways are that you can pre-select the rows, or use a MERGE / upsert approach (you’d have to roll that yourself).

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe Sent from my iPhone
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Maurice Schleußinger]

Merge wont work in my case, because I commit in bulk and also use multiple processes. By the time the commit is triggered (e.g. as soon as there are 1000 new objects to commit) an other process could have committed an object already which triggers an IntegrityError. The same applies for the Unique Object recipes if I'm not mistaken. So I basically have to check every object as I commit it which defies the idea of an bulk commit. Thats why I want to get the key which triggers the IntegrityError, update that object and commit all other elements again in bulk (and do this recursively if there are still other conflicts). To archive this I have no other way but to parse the exception or am I missing something? 

Alternatively I thought about using raw SQL statements (INSERT IGNORE), but I would have to disable relationships for that to work and possibly create many new problems...

[Gombas, Gabor]

There may be better ways if your backend supports it. E.g. if you’re using a recent version of PostgreSQL, then diagnostics information is attached to the exception being thrown by psycopg2 (which is available under the .orig property of the SQLAlchemy exception). See the documentation of psycopg2.extensions.Diagnostics and the documentation of libpq. I don’t know if there are enough details in the diagnostics to support your use case.

 

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

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

---

NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies; do not disclose, use or act upon the information; and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing.