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.

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