Bah! Mike beat me to it again! :P
-----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Thursday, 12 January 2012 12:07 PM To: sqlalchemy@googlegroups.com 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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. 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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
