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.
