From 042f003133249fb38d87d92faa9c730833d14904 Mon Sep 17 00:00:00 2001 From: Eugene Crosser Date: Thu, 18 Aug 2022 11:47:40 +0200 Subject: [PATCH] opencellid: use temp table rather than memory db --- loctrkd/opencellid.py | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) diff --git a/loctrkd/opencellid.py b/loctrkd/opencellid.py index e05648e..e77ba0c 100644 --- a/loctrkd/opencellid.py +++ b/loctrkd/opencellid.py @@ -14,6 +14,7 @@ ldb = None def init(conf: ConfigParser) -> None: global ldb ldb = connect(conf["opencellid"]["dbfn"]) + ldb.execute("create temp table seen (locac int, cellid int, signal int)") def shut() -> None: @@ -26,25 +27,24 @@ def lookup( ) -> Tuple[float, float, float]: assert ldb is not None lc = ldb.cursor() - lc.execute("""attach database ":memory:" as mem""") - lc.execute("create table mem.seen (locac int, cellid int, signal int)") lc.executemany( - """insert into mem.seen (locac, cellid, signal) - values (?, ?, ?)""", + "insert into seen (locac, cellid, signal) values (?, ?, ?)", gsm_cells, ) ldb.commit() lc.execute( """select c.lat, c.lon, s.signal - from main.cells c, mem.seen s + from cells c, seen s where c.mcc = ? + and c.net = ? and c.area = s.locac and c.cell = s.cellid""", - (mcc,), + (mcc, mnc), ) data = list(lc.fetchall()) - # lc.execute("drop table mem.seen") - lc.execute("""detach database mem""") + # This should be faster than dropping and recreating the temp table + # https://www.sqlite.org/lang_delete.html#the_truncate_optimization + lc.execute("delete from seen") lc.close() if not data: raise ValueError("No location data found in opencellid") -- 2.39.2