On Sunday 23 Aug 2015 16:03 CEST, Chris Angelico wrote: > On Sun, Aug 23, 2015 at 11:18 PM, Cecil Westerhof <ce...@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 -- https://mail.python.org/mailman/listinfo/python-list