OK, I'll give you like 30 minutes next time ! Sorry I'm not used to having much help here ! Thanks !
On Jan 11, 2012, at 8:08 PM, Jackson, Cameron wrote: > Bah! Mike beat me to it again! :P > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On > Behalf Of Michael Bayer > Sent: Thursday, 12 January 2012 12:07 PM > To: [email protected] > Subject: Re: [sqlalchemy] insert or update on duplicate key > > > On Jan 11, 2012, at 7:54 PM, Mason wrote: > >> Hi, >> >> I have been looking at the group archive and googling around a bit, >> but it seems like if I want to do insert to table if row doesn't >> exist, otherwise update a field in the row is not really possible. I >> end up doing something like below. >> >> def _add_index(self, src_id, tar_id): >> >> row = self.session.query(Index).filter(Index.src_id==src_id).\ >> filter(Index.tar_id==tar_id).first() >> >> if row: >> row.new += 1 >> row.total += 1 >> else: >> row = Index(src_id, tar_id, 1, 1, 0) >> >> self.session.add(row) >> self.session.commit() >> >> return >> >> This doesn't look very efficient, is there a better way to do this? I >> have been looking at merge(), but that looks like it is for merging >> uncommitted transactions in the session before committing to the db. >> Is this correct? > > merge() is not the same thing as the MERGE statement in ANSI SQL, which is > what people are often looking for here - though it does have a similar use. > > SQLAlchemy doesn't have direct support for SQL MERGE, as the support by > different databases is minimal and also varies considerably in > implementation. Particularly the ORM is tailored towards a straightforward > SELECT/INSERT/UPDATE model and even when we do get support for a MERGE-like > statement, getting the ORM to use it might not be possible without major > changes to underlying assumptions. > > So typically when doing lots of insert/update decisions, the most efficient > way, though not always possible, is to SELECT from the target table ahead of > time all those records that you might be updating, typically into a > dictionary keyed to the criteria that you intend to locate them on. You > then check this dictionary for each target key which allows you to decide if > you're add()-ing a new instance to be INSERTed, or updating an existing > instance. > > The less efficient way is more or less what you have there, though you can > still use a cache to minimize repeat lookups. > > A recipe which illustrates a way to embed this feature in the object's > constructor is at > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject . > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > ------------------------------------------------------------------------- > DISCLAIMER: This e-mail transmission and any documents, files and > previous e-mail messages attached to it are private and confidential. > They may contain proprietary or copyright material or information that > is subject to legal professional privilege. They are for the use of > the intended recipient only. Any unauthorised viewing, use, disclosure, > copying, alteration, storage or distribution of, or reliance on, this > message is strictly prohibited. No part may be reproduced, adapted or > transmitted without the written permission of the owner. If you have > received this transmission in error, or are not an authorised recipient, > please immediately notify the sender by return email, delete this > message and all copies from your e-mail system, and destroy any printed > copies. Receipt by anyone other than the intended recipient should not > be deemed a waiver of any privilege or protection. Thales Australia > does not warrant or represent that this e-mail or any documents, files > and previous e-mail messages attached are error or virus free. > > ------------------------------------------------------------------------- > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
