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")