Path: csiph.com!news.mixmin.net!feeds.phibee-telecom.net!newsfeed.xs4all.nl!newsfeed7.news.xs4all.nl!nzpost1.xs4all.net!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: Is this the way to go with SQLite
Date: Sun, 23 Aug 2015 12:17:20 -0400
Organization: IISS Elusive Unicorn
References: <871teum9c2.fsf@Equus.decebal.nl> <CAPTjJmqo98J7Uqn=syArxebHX6wFSpA2yVe4TqqRfuoBbFgM0Q@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.36.1440346653.17298.python-list@python.org>
Lines: 35
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:95595

On Mon, 24 Aug 2015 00:03:53 +1000, Chris Angelico <rosuav@gmail.com>
declaimed the following:

>Otherwise, looks reasonable. I'm normally expecting to see this kind
>of "query, and if it isn't there, insert" code to have an UPDATE in
>its other branch (which makes it into a classic upsert or merge
>operation - what MySQL calls "INSERT... ON DUPLICATE KEY UPDATE"), or
>else throw an error (in which case the cleanest way is to put a unique
>key on the column in question and let the database throw the error).
>The risk normally is of a race condition; you could execute your
>SELECT query, find no results, and then have someone else insert one
>just a moment before you do. But with SQLite, you're probably assuming
>no other writers anyway - an assumption which (I think) you can
>mandate simply by opening a transaction and holding it through the
>full update procedure - which would make this safe.
>
	SQLite3 supports the non-standard

		INSERT OR REPLACE ...

(or one can do INSERT OR IGNORE; the OR XXX has a number of values that are
allowed to control behavior... BUT the OR clause only applies if a UNIQUE
constraint would be violated by the INSERT action... So if the field is not
a unique index, no foul is detected)

	Looking at the 2nd Edition DGtoSQLite, it sounds like the conflict
behavior can be defined on the table itself, turning all INSERT statements
into INSERT OR xxx for the table -- and one can thereby forgo any UPDATE
statements <G>

{Heh... And Firebird has UPDATE OR INSERT}
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/