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 > 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 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.
>
>
--
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.
