#!/usr/bin/env python3 import os, sys, sqlite3, pymysql SQLITE_PATH = "/home/Pi5/.local/share/VirtualRadar/BaseStation.sqb" MYSQL = dict(host="localhost", user="mawdz09", password="asaxPI1324", database="virtualradar", charset="utf8mb4", autocommit=False) BATCH = 5000 def get_mysql(): return pymysql.connect(**MYSQL, cursorclass=pymysql.cursors.DictCursor) def get_sqlite(): uri = f"file:{SQLITE_PATH}?immutable=1&mode=ro" return sqlite3.connect(uri, uri=True) def get_state(cur, key): cur.execute("SELECT key_value FROM import_state WHERE key_name=%s", (key,)) row = cur.fetchone() return int(row['key_value']) if row else 0 def set_state(cur, key, val): cur.execute("""INSERT INTO import_state(key_name,key_value) VALUES(%s,%s) ON DUPLICATE KEY UPDATE key_value=VALUES(key_value)""", (key, int(val))) def copy_locations(sqlite, mysql): ms = mysql.cursor() last = get_state(ms, "max_location_id") ss = sqlite.cursor() ss.execute("SELECT LocationID, LocationName, Latitude, Longitude, Altitude " "FROM Locations WHERE LocationID > ? ORDER BY LocationID ASC", (last,)) rows = ss.fetchmany(BATCH) total = 0 while rows: ms.executemany("""INSERT INTO Locations(LocationID, LocationName, Latitude, Longitude, Altitude) VALUES(%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE LocationName=VALUES(LocationName), Latitude=VALUES(Latitude), Longitude=VALUES(Longitude), Altitude=VALUES(Altitude)""", rows) last = rows[-1][0] total += len(rows) rows = ss.fetchmany(BATCH) set_state(ms, "max_location_id", last) return total def copy_sessions(sqlite, mysql): ms = mysql.cursor() last = get_state(ms, "max_session_id") ss = sqlite.cursor() ss.execute("""SELECT SessionID, LocationID, StartTime, EndTime FROM Sessions WHERE SessionID > ? ORDER BY SessionID ASC""", (last,)) rows = ss.fetchmany(BATCH) total = 0 while rows: rows2 = [] for r in rows: sid, lid, st, et = r rows2.append((sid, lid, st, et)) ms.executemany("""INSERT INTO Sessions(SessionID, LocationID, StartTime, EndTime) VALUES(%s,%s,%s,%s) ON DUPLICATE KEY UPDATE LocationID=VALUES(LocationID), StartTime=VALUES(StartTime), EndTime=VALUES(EndTime)""", rows2) last = rows[-1][0] total += len(rows) rows = ss.fetchmany(BATCH) set_state(ms, "max_session_id", last) return total def copy_aircraft(sqlite, mysql): ms = mysql.cursor() last = get_state(ms, "max_aircraft_id") ss = sqlite.cursor() ss.execute("""SELECT AircraftID, FirstCreated, LastModified, ModeS, ModeSCountry, Country, Registration, ICAOTypeCode, Type, OperatorFlagCode, YearBuilt, Manufacturer FROM Aircraft WHERE AircraftID > ? ORDER BY AircraftID ASC""", (last,)) rows = ss.fetchmany(BATCH) total = 0 while rows: rows2 = [] for r in rows: (AircraftID, FirstCreated, LastModified, ModeS, ModeSCountry, Country, Registration, ICAOTypeCode, TypeName, OperatorFlagCode, YearBuilt, Manufacturer) = r rows2.append((AircraftID, FirstCreated, LastModified, ModeS, ModeSCountry, Country, Registration, ICAOTypeCode, TypeName, OperatorFlagCode, YearBuilt, Manufacturer)) ms.executemany("""INSERT INTO Aircraft (AircraftID, FirstCreated, LastModified, ModeS, ModeSCountry, Country, Registration, ICAOTypeCode, TypeName, OperatorFlagCode, YearBuilt, Manufacturer) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE FirstCreated=VALUES(FirstCreated), LastModified=VALUES(LastModified), ModeS=VALUES(ModeS), ModeSCountry=VALUES(ModeSCountry), Country=VALUES(Country), Registration=VALUES(Registration), ICAOTypeCode=VALUES(ICAOTypeCode), TypeName=VALUES(TypeName), OperatorFlagCode=VALUES(OperatorFlagCode), YearBuilt=VALUES(YearBuilt), Manufacturer=VALUES(Manufacturer)""", rows2) last = rows[-1][0] total += len(rows) rows = ss.fetchmany(BATCH) set_state(ms, "max_aircraft_id", last) return total def copy_flights(sqlite, mysql): ms = mysql.cursor() last = get_state(ms, "max_flight_id") ss = sqlite.cursor() ss.execute("""SELECT FlightID, SessionID, AircraftID, StartTime, EndTime, Callsign, LastIsOnGround, LastLat, LastLon, LastAltitude, NumPosMsgRec, FirstSquawk, LastSquawk, HadAlert, HadEmergency, HadSPI FROM Flights WHERE FlightID > ? ORDER BY FlightID ASC""", (last,)) # Load all known AircraftIDs once — avoids per-row SELECT causing deadlocks ms.execute("SELECT AircraftID FROM Aircraft") known_aircraft = {r['AircraftID'] for r in ms.fetchall()} print(f"[INFO] Loaded {len(known_aircraft)} known aircraft IDs") rows = ss.fetchmany(BATCH) total = 0 skipped = 0 while rows: rows2 = [] for r in rows: (FlightID, SessionID, AircraftID, StartTime, EndTime, Callsign, LastIsOnGround, LastLat, LastLon, LastAltitude, NumPosMsgRec, FirstSquawk, LastSquawk, HadAlert, HadEmergency, HadSPI) = r if AircraftID not in known_aircraft: skipped += 1 continue rows2.append(( FlightID, SessionID, AircraftID, StartTime, EndTime, Callsign, int(LastIsOnGround or 0), LastLat, LastLon, LastAltitude, NumPosMsgRec, FirstSquawk, LastSquawk, int(HadAlert or 0), int(HadEmergency or 0), int(HadSPI or 0) )) if rows2: for attempt in range(3): try: ms.executemany("""INSERT INTO Flights (FlightID, SessionID, AircraftID, StartTime, EndTime, Callsign, LastIsOnGround, LastLat, LastLon, LastAltitude, NumPosMsgRec, FirstSquawk, LastSquawk, HadAlert, HadEmergency, HadSPI) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE SessionID=VALUES(SessionID), AircraftID=VALUES(AircraftID), StartTime=VALUES(StartTime), EndTime=VALUES(EndTime), Callsign=VALUES(Callsign), LastIsOnGround=VALUES(LastIsOnGround), LastLat=VALUES(LastLat), LastLon=VALUES(LastLon), LastAltitude=VALUES(LastAltitude), NumPosMsgRec=VALUES(NumPosMsgRec), FirstSquawk=VALUES(FirstSquawk), LastSquawk=VALUES(LastSquawk), HadAlert=VALUES(HadAlert), HadEmergency=VALUES(HadEmergency), HadSPI=VALUES(HadSPI)""", rows2) mysql.commit() break except Exception as e: print(f"[ERROR] Batch insert failed (attempt {attempt+1}): {e}") mysql.rollback() if attempt == 2: raise last = rows[-1][0] total += len(rows2) if total % 50000 == 0: print(f"[INFO] {total} flights imported so far...") rows = ss.fetchmany(BATCH) set_state(ms, "max_flight_id", last) print(f"[OK] Imported F:{total} Skipped:{skipped}") return total def main(): if not os.path.exists(SQLITE_PATH): print(f"SQLite file not found: {SQLITE_PATH}", file=sys.stderr) sys.exit(1) sconn = get_sqlite() mconn = get_mysql() try: total_loc = copy_locations(sconn, mconn) total_ses = copy_sessions(sconn, mconn) total_ac = copy_aircraft(sconn, mconn) total_fl = copy_flights(sconn, mconn) mconn.commit() print(f"[OK] Imported L:{total_loc} S:{total_ses} A:{total_ac} F:{total_fl}") except Exception as e: mconn.rollback() print(f"[ERROR] {e}", file=sys.stderr) sys.exit(2) finally: sconn.close() mconn.close() if __name__ == "__main__": main()