def run(self): connection = DbSource(self.dbConnectionInfo).getConnection() while self.doRun or len(self.toDoStatements) > 0: if len(self.toDoStatements) > 0: with self.toDoStatementsLock: try: cur = connection.cursor() while len(self.toDoStatements) > 0: sql = self.toDoStatements.pop() # print(f"[INFO] dbThread sql: {sql}") try: cur.execute(sql) except Exception as ex: sys.stderr.write("error in statement: %s\n" % sql) sys.stderr.write(str(type(ex)) + "\n") sys.stderr.write(str(ex) + "\n") connection.commit() cur.close() except Exception as e: print('[ERROR] in DbThread:', e) connection = DbSource( self.dbConnectionInfo).getConnection() else: time.sleep(1) if connection: connection.commit() connection.close() print("DbThread terminated")
if not landingIcao and landingLat and landingLon: landingIcao = afm.getNearest(landingLat, landingLon) if landingIcao: print('landingIcao:', landingIcao) strSql = f"UPDATE logbook_entries set landing_icao = '{landingIcao}' where id = {id}" dbt.addStatement(strSql) numUpdatedRecords += 1 print('numUpdatedRecords:', numUpdatedRecords) if __name__ == '__main__': afm = AirfieldManager() dbt = DbThread(dbConnectionInfo=dbConnectionInfo) dbt.start() dbs = DbSource(dbConnectionInfo=dbConnectionInfo) _processLogbookEvents() # take-offs and landings _processLogbookEntries() # flights while len(dbt.toDoStatements) > 0: print('len DB toDoStatements:', len(dbt.toDoStatements)) sleep(1) dbt.stop() print('KOHEU.')
def listFlights(address=None, icaoCode=None, registration=None, forDay=None, limit=None, icaoFilter: list = [], sortTsDesc=False, orderByCol='takeoff_ts'): c1 = c2 = '' if icaoCode: c1 = f" AND (l.takeoff_icao = '{icaoCode}' OR l.landing_icao = '{icaoCode}')" if registration: c2 = f" AND d.aircraft_registration = '{registration}'" cond = c1 + c2 condTs = '' condLimit = '' startTs, endTs = getDayTimestamps(forDay) if startTs and endTs: condTs = f" AND l.takeoff_ts >= {startTs} AND l.landing_ts <= {endTs}" if limit: condLimit = f" limit {limit}" condIcao = '' if len(icaoFilter) > 0: c = "" for i, prefix in enumerate(icaoFilter): c += f"l.takeoff_icao LIKE '{prefix}%' OR l.landing_icao like '{prefix}%'" if i < (len(icaoFilter) - 1): c += ' OR ' condIcao += f" AND ({c})" sortTs = 'DESC' if sortTsDesc else 'ASC' records = list() with DbSource(dbConnectionInfo).getConnection() as c: strSql = f"""SELECT l.address, l.takeoff_ts, l.takeoff_lat, l.takeoff_lon, l.takeoff_icao, l.landing_ts, l.landing_lat, l.landing_lon, l.landing_icao, l.flight_time, d.device_type, d.aircraft_type, d.aircraft_registration, d.aircraft_cn FROM logbook_entries as l LEFT JOIN ddb AS d ON l.address = d.device_id WHERE (d.tracked != false OR d.tracked IS NULL) AND (d.identified != false OR d.identified IS NULL) {cond} {condTs} {condIcao} ORDER by {orderByCol} {sortTs} {condLimit};""" c.execute(strSql) rows = c.fetchall() for row in rows: (address, ts1, lat1, lon1, locationIcao1, ts2, lat2, lon2, locationIcao2, flightTime, devType, aircraftType, registration, cn) = row item = LogbookItem( address=address, takeoff_ts=ts1 if ts1 else None, takeoff_lat=float(lat1) if lat1 else None, takeoff_lon=float(lon1) if lon1 else None, takeoff_icao=locationIcao1 if locationIcao1 else None, landing_ts=ts2, landing_lat=float(lat2), landing_lon=float(lon2), landing_icao=locationIcao2, flight_time=flightTime, device_type=devType, registration=registration, cn=cn, aircraft_type=aircraftType) records.append(item) return records
def listArrivals(address=None, icaoCode=None, registration=None, forDay=None, limit=None, icaoFilter: list = [], sortTsDesc=False): cond = _prepareCondition(address=address, icaoCode=icaoCode, registration=registration) condTs = '' condLimit = '' startTs, endTs = getDayTimestamps(forDay) if startTs and endTs: condTs = f" AND l.ts >= {startTs} AND l.ts <= {endTs}" if limit: condLimit = f" limit {limit}" condIcao = '' if len(icaoFilter) > 0: c = "" for i, prefix in enumerate(icaoFilter): c += f"l.location_icao LIKE '{prefix}%'" if i < (len(icaoFilter) - 1): c += ' OR ' condIcao += f" AND ({c})" sortTs = 'DESC' if sortTsDesc else 'ASC' records = list() with DbSource(dbConnectionInfo).getConnection() as c: strSql = f"""SELECT l.ts, l.address, l.address_type, l.aircraft_type, l.lat, l.lon, l.location_icao, l.flight_time, d.device_type, d.aircraft_type, d.aircraft_registration, d.aircraft_cn FROM logbook_events AS l LEFT JOIN ddb AS d ON l.address = d.device_id WHERE l.event = 'L' AND (d.tracked != false OR d.tracked IS NULL) AND (d.identified != false OR d.identified IS NULL) {cond} {condTs} {condIcao} ORDER by ts {sortTs} {condLimit};""" c.execute(strSql) rows = c.fetchall() for row in rows: (ts, address, addrType, aircraftTypeCode, lat, lon, locationIcao, flightTime, devType, aircraftType, registration, cn) = row item = LogbookItem(address=address, takeoff_ts=0, takeoff_lat=0, takeoff_lon=0, takeoff_icao=None, landing_ts=ts, landing_lat=float(lat), landing_lon=float(lon), landing_icao=locationIcao, flight_time=flightTime, device_type=devType, registration=registration, cn=cn, aircraft_type=aircraftType) records.append(item) return records
def run(self): numEmptyLoops = 0 connection = DbSource(self.dbConnectionInfo).getConnection() while self.doRun or len(self.toDoStatements) > 0: if len(self.toDoStatements) > 0: numEmptyLoops = 0 with self.toDoStatementsLock: sql = None try: cur = connection.cursor() while len(self.toDoStatements) > 0: sql = self.toDoStatements.pop() # print(f"[INFO] dbThread sql: {sql}") try: cur.execute(sql) connection.commit() except (sqlite3.OperationalError, pymysql.err.OperationalError) as ex: sys.stderr.write("error in statement: %s\n" % sql) sys.stderr.write(str(type(ex)) + "\n") sys.stderr.write(str(ex) + "\n") cur.close() except Exception as ex: print('[ERROR] in DbThread (1):', ex) connection = DbSource( self.dbConnectionInfo).getConnection() self.toDoStatements.append(sql) # requeue for retry else: time.sleep(1) numEmptyLoops += 1 if numEmptyLoops > 60: numEmptyLoops = 0 try: cur = connection.cursor() cur.execute( 'SELECT 1;') # to prevent connection timeouts cur.close() except ex: print(f"[ERROR] in DbThread (2):", ex) if connection: connection.commit() connection.close() print("DbThread terminated")