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

Groups > comp.lang.python > #95589 > unrolled thread

Is this the way to go with SQLite

Back to article view | Back to comp.lang.python

Contents

  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

#95589 — Is this the way to go with SQLite

I understood that with sqlite3 in Python you can not use prepared
statements. Below the way I solved this.

Also an URL is unique, so I need to check that if it is found, the
values are the same as the ones I wanted to insert.

This is my code.
========================================================================
select_url  = '''SELECT year
                 ,      month
                 ,      description
                 FROM   LINKS
                 WHERE  URL = ?'''
year        = 2015
month       = 8
for link in links:
    description = link[0]
    url         = link[1]
    url_values  = c.execute(select_url, [url]).fetchall()
    if len(url_values) == 0:
        print('Adding {0}'.format(link))
        c.execute('''INSERT INTO links
                     (year, month, description, URL)
                     VALUES
                     (?, ?, ?, ?)
                  ''',
                  [year, month, description, url])
    else:
        to_insert   = (year, month, description)
        found       = url_values[0]
        if found != to_insert:
            print('For {0} found {1} instead of {2}'.format(url, found, to_insert))
========================================================================

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [next] | [standalone]

#95590

On Sun, Aug 23, 2015 at 11:18 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
> Also an URL is unique, so I need to check that if it is found, the
> values are the same as the ones I wanted to insert.

And if they aren't? Currently, all you do is print out a message and
continue on; what happens if you get the same URL coming up more than
once?

> select_url  = '''SELECT year
>                  ,      month
>                  ,      description
>                  FROM   LINKS
>                  WHERE  URL = ?'''
> year        = 2015
> month       = 8

PEP 8 has a word or two to say about this, but carry on. Incidentally,
I'd be inclined to put the SELECT query down below, same as the INSERT
query is; it's not in any way different from just using a string
literal there, and this separates two pieces of code (IMO)
unnecessarily.

> for link in links:
>     description = link[0]
>     url         = link[1]

for description, url in links:

>     url_values  = c.execute(select_url, [url]).fetchall()
>     if len(url_values) == 0:

if not url_values:

>         print('Adding {0}'.format(link))
>         c.execute('''INSERT INTO links
>                      (year, month, description, URL)
>                      VALUES
>                      (?, ?, ?, ?)
>                   ''',
>                   [year, month, description, url])
>     else:
>         to_insert   = (year, month, description)
>         found       = url_values[0]
>         if found != to_insert:
>             print('For {0} found {1} instead of {2}'.format(url, found, to_insert))

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.

ChrisA

[toc] | [prev] | [next] | [standalone]

#95608

On Sunday 23 Aug 2015 16:03 CEST, Chris Angelico wrote:

> On Sun, Aug 23, 2015 at 11:18 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
>> Also an URL is unique, so I need to check that if it is found, the
>> values are the same as the ones I wanted to insert.
>
> And if they aren't? Currently, all you do is print out a message and
> continue on; what happens if you get the same URL coming up more
> than once?

That is all what I want at the moment: to get notified when an URL has
two different descriptions. It is just a script to do an initial fill
of the table. When run again I do not insert the URLs that are already
in the database. But just skipping is not enough, when it has a
different description I did something wrong and should investigate
that.

One thing I could do is when the only difference is case, that I use
the latter definition and notify the change.


>> select_url  = '''SELECT year
>> ,      month
>> ,      description
>> FROM   LINKS
>> WHERE  URL = ?'''
>> year        = 2015
>> month       = 8
>
> PEP 8 has a word or two to say about this, but carry on.

Something to read then.


> Incidentally, I'd be inclined to put the SELECT query down below,
> same as the INSERT query is; it's not in any way different from just
> using a string literal there, and this separates two pieces of code
> (IMO) unnecessarily.

I am inclined to do the opposite: put the INSERT query where the
SELECT is. Both will be used several times in the near future (next
week) and I like DRY. Was an omission when I changed the code. I have
taken care of that.


>> for link in links:
>> description = link[0]
>> url         = link[1]
>
> for description, url in links:
>
>> url_values  = c.execute(select_url, [url]).fetchall()
>> if len(url_values) == 0:
>
> if not url_values:
>
>> print('Adding {0}'.format(link)) c.execute('''INSERT INTO links
>> (year, month, description, URL) VALUES (?, ?, ?, ?) ''', [year,
>> month, description, url]) else: to_insert = (year, month,
>> description) found = url_values[0] if found != to_insert:
>> print('For {0} found {1} instead of {2}'.format(url, found,
>> to_insert))

Implemented.


> 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

In my case I do not want the old value changed. (Maybe with the
exception if only the case is different.) I need to evaluate which
value is the right one.


> 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.

I start with:
    conn = sqlite3.connect('links.sqlite')
    c = conn.cursor()

and end with:
    conn.commit()
    conn.close()

Taken from:
    https://docs.python.org/2/library/sqlite3.html

This takes care of the transaction, or not?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95609

On Mon, Aug 24, 2015 at 9:00 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
> On Sunday 23 Aug 2015 16:03 CEST, Chris Angelico wrote:
>
>> On Sun, Aug 23, 2015 at 11:18 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
>>> Also an URL is unique, so I need to check that if it is found, the
>>> values are the same as the ones I wanted to insert.
>>
>> And if they aren't? Currently, all you do is print out a message and
>> continue on; what happens if you get the same URL coming up more
>> than once?
>
> That is all what I want at the moment: to get notified when an URL has
> two different descriptions. It is just a script to do an initial fill
> of the table. When run again I do not insert the URLs that are already
> in the database. But just skipping is not enough, when it has a
> different description I did something wrong and should investigate
> that.

Sounds to me like you mostly want an error, but in some cases, you'll
suppress the error (ie it's exactly the same description and
datestamp).

>> 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.
>
> I start with:
>     conn = sqlite3.connect('links.sqlite')
>     c = conn.cursor()
>
> and end with:
>     conn.commit()
>     conn.close()
>
> Taken from:
>     https://docs.python.org/2/library/sqlite3.html
>
> This takes care of the transaction, or not?

Yep, I think so. If not, you should be able to ensure transactional
integrity by simply adding an explicit "BEGIN" query.

ChrisA

[toc] | [prev] | [next] | [standalone]

#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/

[toc] | [prev] | [next] | [standalone]

#95596

On Mon, Aug 24, 2015 at 2:17 AM, Dennis Lee Bieber
<wlfraed@ix.netcom.com> wrote:
>         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)

Sure. But that's still the same as MySQL's (equally non-standard) "ON
DUPLICATE KEY UPDATE", and various others. It's a way of shoving down
a level the common idiom of "do this insert, only if that doesn't
work, do this update instead". But that doesn't seem to be what the
original code was doing - it was more like "do this insert, but if
it's a duplicate based on URL, check if the other fields are the same,
and if not... uhh, print something out?", which is far from common.

ChrisA

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web