Re: updating before insert

On Fri, 10 Aug 2007 00:53:51 -0400, "Eric S. Johansson" <[email protected]> wrote:
> I've tried a couple different ways of doing this but I'm screwing
> something up.  I know this is part SQL but it's also part Python and how
> they interfere... I mean interacts with each other.
> 
> What's the right way to update a few fields in a record if the record
> exists otherwise, create the record?

Oracle has the nonstandard MERGE statement that comes in very handy for such cases. SQLite has "INSERT OR REPLACE", which AFAIK is less powerful (I think it does a UNIQUE check on all columns).

What I'd recommend is this:

Create a UNIQUE INDEX on the columns in question.

Then just INSERT from pysqlite. If this fails with an IntegrityError, do the equivalent UPDATE instead. Then COMMIT.

> Related is:
> 
> What's the right way to test for a record (including a predicate against
> some of the fields (specifically timestamp calculations)) and if the
> record doesn't exist, add it.

There's no way to do this reliably without locking the whole table. So do as I described: INSERT. If fails, then UPDATE. No race conditions here.

> In both cases, handle the implicit race conditions between testing and
> creating/setting.
> 
> (I'm coming to feel like that Monty Python character, sitting in a local
> restaurant screeching "but I don't like SQL")