On 09/08/2022 11:40, Charlie Clark wrote:
On 9 Aug 2022, at 11:09, Gilles wrote:
Nice idea too. I could just ignore the error when trying to insert a duplicate
https://www.sqlitetutorial.net/sqlite-unique-constraint/ Sure, though that's a kind of try/except and if you have a lot of data I suspect the aggregate function will be faster for this kind of one off.
Here's some working code. I recon using SQL's UNIQUE and ignoring the error triggered when adding a duplicate is a bit kludgy, but it works ========= import sqlite3 db = sqlite3.connect('wp.sqlite') cursor = db.cursor() cursor.execute('CREATE TABLE IF NOT EXISTS wp(id INTEGER PRIMARY KEY,name TEXT UNIQUE,latitude TEXT,longitude TEXT)') db.commit() cursor.execute('BEGIN') wps = tree.findall("wpt") for wp in wps : name = wp.find('name').text lat = wp.attrib['lat'] lon = wp.attrib['lon'] print(name,lat,lon) #Ignore error when inserting dup try: cursor.execute('INSERT INTO wp (name,latitude,longitude) VALUES(?,?,?)', (name,lat,lon)) except sqlite3.IntegrityError as err: if err.args != ('UNIQUE constraint failed: wp.name',): raise cursor.execute('END') db.commit() db.close() =========