Is this the way to go with SQLite

[Cecil Westerhof]

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

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

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