Re: [sqlalchemy] How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?
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... On Monday, February 23, 2015 at 9:11:28 PM UTC+1, Michael Bayer wrote: On Feb 23, 2015, at 8:49 AM, Maurice Schleußinger m.schle...@gmail.com javascript: 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 http://docs.sqlalchemy.org/en/rel_0_9/orm/session_state_management.html#merging. 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 javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?
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 http://docs.sqlalchemy.org/en/rel_0_9/orm/session_state_management.html#merging. 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? On Thursday, February 19, 2015 at 5:29:56 PM UTC+1, Michael Bayer wrote: Maurice Schleußinger m.schle...@gmail.com javascript: 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 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?
On Feb 23, 2015, at 8:49 AM, Maurice Schleußinger m.schleusin...@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 sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How can I know which fields cause IntegrityError when inserting into a table with multiple unique fields?
Maurice Schleußinger m.schleusin...@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 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
