Esempio n. 1
0
def updateICAOmap(db, cursor, icao, tailPin):
    
    sql="SELECT icao,  tailpin "\
            "FROM  AirTraffic.ICAOmap "\
            "WHERE icao='" + icao + "';"
            
    try:
        tday =  datetime.now(pytz.timezone(timeZone))
        dateStr = "%s-%02d-%02d"%(tday.year, tday.month,tday.day)
        
        cursor.execute(sql)
        rows = cursor.fetchall()
 
        print('updateICAOmap() Total Row(s):', cursor.rowcount)
        if cursor.rowcount == 0: #insert it as a new icao map
            sql = "INSERT INTO  AirTraffic.ICAOmap VALUES ('" + icao + "', '" + tailPin +"', '" + dateStr + "');"
        else:
            sql = "UPDATE AirTraffic.ICAOmap SET tailpin = '" + tailPin +"', date = '"  + dateStr + "' WHERE icao='" + icao + "';"

        print('updateICAOmap() sql=[', sql)

        cursor.execute(sql)
        db.commit()
            
    except :
        print "updateICAOmap() Something is wrong sql=[", sql
        ex = traceback.print_stack()
        print ex
        sendMail("parseICAO Failed updateICAOmap()", ex)
        db.rollback()
    return None
Esempio n. 2
0
def findNewICAOs4Today():
    flightSql = ""
    sql = ""
    icaoDict = {}
    #MySQL lookup & update
    '''
    tday =  datetime.now(pytz.timezone(timeZone))
    dateStr = "%s-%02d-%02d"%(tday.year, tday.month,tday.day)
    print "dateStr=[",dateStr, "(%f,%f)"%( calculateDistance.origLat,calculateDistance.origLong)
    sql="SELECT icao,  max(date) datetm, min(altitude) alt, avg(latitude) lat, avg(longititude) lon, avg(speed) speed, flight "\
            "FROM AirTraffic.AirtrafficUI_positionjsonhistory "\
            "WHERE date LIKE '" + dateStr + "%' and altitude > " + str(minAlt) + " and altitude < " + str(maxAlt) + " "\
                " AND icao NOT IN (SELECT distinct icao "\
                                    "FROM AirTraffic.AirtrafficUI_flights "\
                                    "WHERE date LIKE '" + dateStr + "%') "\
            "GROUP BY icao "\
            "ORDER BY  datetm  DESC; "
    '''
        # Or don't use GROUP BY, scan all coordinates for a flight to see if it's over our head. radius of 3 miles
        # library: https://pypi.python.org/pypi/geopy/1.11.0
    
    # http://www.mysqltutorial.org/python-mysql-query/
    db = None
    cursor = None
    
    try:        
        while True:
            tday =  datetime.now(pytz.timezone(timeZone))
            dateStr = "%s-%02d-%02d"%(tday.year, tday.month,tday.day)
            #print "dateStr=[",dateStr, "(%f,%f)"%( calculateDistance.origLat,calculateDistance.origLong)
            sql = "SELECT distinct icao, flight "\
                    "FROM AirTraffic.AirtrafficUI_positionjsonhistory "\
                    "WHERE date LIKE '" + dateStr + "%'  "\
                    "and altitude > " + str(minAlt) + " and altitude < " + str(maxAlt) + " "\
                    "AND icao NOT IN  "\
                    "(SELECT distinct icao "\
                    "FROM AirTraffic.AirtrafficUI_flights WHERE date LIKE  '" + dateStr + "%') ORDER BY icao; " 
            #print sql

            db = MySQLdb.connect("lab2.svcvllc.com","httpuser","Save3ySky","AirTraffic" )
            #db = MySQLdb.connect("localhost","httpuser","Save3ySky","AirTraffic" )
            
            # prepare a cursor object using cursor() method
            cursor = db.cursor()
            
            cursor.execute(sql)
            print "EXE main sql=[", sql
            rows = cursor.fetchall()
     
            print('findNewICAOs4Today() Total New ICAO Row(s):', cursor.rowcount)
            noneCounter = 0
            for row in rows:
                newIcao=row[0]
                #datetm=row[1]
                flightNo = row[1]
                print "\n new ICAO=[", newIcao, "] flight=[",flightNo, "] counter=", noneCounter
                if (flightNo is not None and str(flightNo)!= 'null'):
                    flightDict = lookupFlightNoVer2(flightNo.rstrip())
                    icaoDict[newIcao] = flightNo
                    
                    updateICAOmap(db, cursor, newIcao, flightNo) #update the mapping table for this icao
                else:
                    flightNo = icaoDict.get(newIcao, "null")
                    print 'icaoDict[newIcao]=',flightNo
                    if (flightNo is not None and flightNo != "null"):
                        continue    # skip this because I just processed it
                    
                    flightDict = lookupICAO(newIcao)
                    if (flightDict is not None and flightDict['flightNo'] is not None):
                        updateICAOmap(db, cursor, newIcao, flightNo) #update the mapping table for this icao
                    else: # Finally we must look up the flight # from the mapping table
                        sqlJoinMap = "SELECT TailPin FROM AirTraffic.ICAOmap "\
                            "WHERE icao = '"+ newIcao+"';"
                        
                        cursor.execute(sqlJoinMap)
                        mapRows = cursor.fetchall()
    
                        if (len(mapRows)==1):
                            TailPin = mapRows[0][0]
                            print "In ICAOmap, icao=[", newIcao, "] TailPin=[", TailPin
                            flightDict = lookupFlightNoVer2(TailPin.rstrip())
                            #print "In ICAOmap, flightDict=[",flightDict
                            
                    #time.sleep(interval*10)
                if (flightDict is None or bool(flightDict) == False):
                    noneCounter +=1
                    #print noneCounter, flightDict
                else:
                    noneCounter =0 #reset the counter
                    print noneCounter, flightDict
                    coordinate = isInsideMaxTolerantDistance(cursor, newIcao, dateStr)
                    
                    if coordinate is not None: #insert the flight info to flights table
                        try:
                            flightSql = "INSERT INTO AirtrafficUI_flights VALUES(null,'" + newIcao + "', '" + flightDict['flightNo'] + "', " \
                                        " '" + str(coordinate['date']) + "', " + str(coordinate['alt']) + ", " + str(coordinate['lat']) + ", " + \
                                        str(coordinate['long']) + ", '" +  flightDict['aircraft'] + "', '" + flightDict['origAirport'] + "', '" + \
                                        flightDict['destAirport'] + "', " + str(coordinate['speed']) + ");"
                            
                            print "INSERTing flightSql=[" , flightSql
                            # Commit your changes in the database
                            cursor.execute(flightSql)
                            db.commit()
                        except :
                            print "findNewICAOs4Today() Something is wrong Insert=[",  flightSql
                            ex = traceback.print_stack()
                            print ex
                            sendMail("findNewICAOs4Today() Failed Insert=[", ex)
                            db.rollback()


                if (noneCounter > 50):
                    #stop processing obsolete ICAO further
                    break;
            
            if cursor is not None:
                cursor.close()
            if db is not None:
                db.close()
                
            print "Sleep ", interval*6, "\n\n\n"
            time.sleep(interval*6)
    
    except :
        print "findNewICAOs4Today() Something is wrong sql=[", sql
        ex = traceback.print_stack()
        print ex
        sendMail("parseICAO Failed findNewICAOs4Today()", ex)
        db.rollback()
        traceback.print_exc(file=sys.stdout)
    
    if cursor is not None:
        cursor.close()
    if db is not None:
        db.close()
    
    return None