On 9 Aug 2022, at 15:16, Gilles wrote:
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
For the task I don't see the need for any kind of keys, they'll just slow things down. Also, it will be faster using cursor.executemany() with a list of rows. Not sure if you can combine this with a generator expression but it would be great if you could, otherwise just materialise it when you pass it in. ```python import sqlite3 db = sqlite3.connect('wp.sqlite') cursor = db.cursor() cursor.execute('CREATE TABLE IF NOT EXISTS wp(name TEXT, latitude TEXT, longitude TEXT)') db.commit() def get_rows(tree): for row in tree.iter("wpt"): yield [row.find("name").text] + row.attrib.values() rows = get_rows() # initialise generator cursor.execute('BEGIN') cursor.executemany('INSERT INTO wp (name,latitude,longitude) VALUES(?,?,?)', rows) cursor.execute("COMMIT") cursor.execute("SELECT name, latitude, longitude from wp group by latitude, longitude") cursor.fetchall() Charlie -- Charlie Clark Managing Director Clark Consulting & Research German Office Sengelsweg 34 Düsseldorf D- 40489 Tel: +49-203-3925-0390 Mobile: +49-178-782-6226