Is this the way to go with SQLite

[Chris Angelico]

On Sun, Aug 23, 2015 at 11:18 PM, Cecil Westerhof <Cecil at 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