Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #95595

Re: Is this the way to go with SQLite

Show all headers | View raw

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/

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Is this the way to go with SQLite Cecil Westerhof <Cecil@decebal.nl> - 2015-08-23 15:18 +0200
  Re: Is this the way to go with SQLite Chris Angelico <rosuav@gmail.com> - 2015-08-24 00:03 +1000
    Re: Is this the way to go with SQLite Cecil Westerhof <Cecil@decebal.nl> - 2015-08-24 13:00 +0200
      Re: Is this the way to go with SQLite Chris Angelico <rosuav@gmail.com> - 2015-08-24 21:26 +1000
  Re: Is this the way to go with SQLite Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-08-23 12:17 -0400
  Re: Is this the way to go with SQLite Chris Angelico <rosuav@gmail.com> - 2015-08-24 02:22 +1000

csiph-web