Is this the way to go with SQLite
Cecil Westerhof
Cecil at decebal.nl
Mon Aug 24 07:00:05 EDT 2015
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
More information about the Python-list
mailing list