def insertlog(source, timestart, timeend, tp, pt1, pt2, mmsi, output): conn = MySQLConn(MYSQL_DB_INFO['tasklogdb']) cursor = conn.cursor() mmsistring = '' if mmsi[0] == '0': mmsistring = 'ALL' else: mmsistring = 'FILEPATH' start = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(timestart)) end = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime(timeend)) timenow = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime()) taskid = TaskLog.getnewtaskid() #print taskid sql = 'insert into {9} ({10},{11},{12},{13},{14},{15},{16},{17},{18}) values \ (\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\',\'{6}\',\'{7}\',\'{8}\');' \ .format(taskid,source,start,end,tp,pt1,pt2,mmsistring,output,MYSQL_TASK_INFO['detailtable'], \ MYSQL_TASK_INFO['taskid'],MYSQL_TASK_INFO['source'],MYSQL_TASK_INFO['timestart'],\ MYSQL_TASK_INFO['timeend'],MYSQL_TASK_INFO['tp'],MYSQL_TASK_INFO['pt1'],MYSQL_TASK_INFO['pt2'],\ MYSQL_TASK_INFO['mmsi'],MYSQL_TASK_INFO['opformat']) #print sql cursor.execute(sql) sql = 'insert into {3} ({4}, {5}, {6}) values \ (\'{0}\',\'{1}\',\'{2}\');'.format(taskid, 0, timenow,MYSQL_TASK_INFO['listtable'],\ MYSQL_TASK_INFO['listtaskid'],MYSQL_TASK_INFO['liststatus'], MYSQL_TASK_INFO['listtimestart']) #print sql cursor.execute(sql) cursor.close() conn.commit() conn.disconnect() return taskid
def getlist(startindex,endindex): conn = MySQLConn(MYSQL_DB_INFO['tasklogdb']) cursor = conn.cursor() #for i in range(count / MYSQL_INFO['page_size'] + 1): cursor.execute('SELECT {2}, {3}, {4}, {5} FROM {6} ORDER BY {2} DESC\ limit {1} offset {0};'.format(startindex, endindex-startindex,MYSQL_TASK_INFO['listid']\ ,MYSQL_TASK_INFO['listtaskid'], MYSQL_TASK_INFO['listtimestart'],MYSQL_TASK_INFO['liststatus']\ ,MYSQL_TASK_INFO['listtable'])) result = cursor.fetchall() cursor.close(); conn.disconnect() return result
def getdetail(taskid): conn = MySQLConn(MYSQL_DB_INFO['tasklogdb']) cursor = conn.cursor() print 'SELECT * from taskdetail where taskid = "'+taskid+'";' #listnum = cursor.execute('SELECT * from taskdetail where taskid = "'+taskid+'";') listnum = cursor.execute('SELECT * from {1} where {2} = \'{0}\';'.format(taskid,\ MYSQL_TASK_INFO['detailtable'],MYSQL_TASK_INFO['taskid'])) if listnum == 0: cursor.close() conn.disconnect() return "<b>暂无详情</b>" item = cursor.fetchall()[0] cursor.close() conn.disconnect() return item
class mysqlQuery(Thread): latcolrad = '(RADIANS('+MYSQL_COL_TABLE['latitude'] + ' / 600000))' loncolrad = '(RADIANS('+MYSQL_COL_TABLE['longitude'] + ' / 600000))' """docstring for mysqlQuery""" # def __init__(self,timestart='', timeend='', tp=0, pt1=(0,0), pt2=(0,0), mmsi=['0'],out="csv"): ''' timelist: list[(start,end)] timestamp string positionlist: dict['tp':int{0,1,2},'pt1':tuple(float,float),'pt2':tuple(float,float)] mmsilist: list[string] output:{'csv','text'} ''' def __init__(self,timelist,positionlist,mmsilist,countrylist,output,taskid): super(mysqlQuery, self).__init__() print 'start connect init' self.queryconn = MySQLConn(MYSQL_DB_INFO['querydb']) self.timelist = timelist self.positionlist = positionlist self.mmsilist = mmsilist self.countrylist = countrylist self.output = output self.taskid = taskid print 'connect success' def query(self): print 'function query(self,sql)' # self.mysqlconn.close() self.queryconn.keepconn() sql = mysqlQuery.makeupsql(timelist=self.timelist,positionlist=self.positionlist,mmsilist=self.mmsilist,countrylist=self.countrylist) sql += mysqlQuery.makeupoutput(filetype=self.output,filename=self.taskid) print 'execute ['+sql+']' # cur = self.queryconn.cursor() # result = cur.execute(sql) # cur.close() print "end query" return ''' cur = self.queryconn.conn.cursor() #cur.close() print 'execute ['+sql+']' start = time.time() result = cur.execute(sql) end = time.time() duration = end-start print 'execute time: '+str(duration)+'ms' start = time.time() re = cur.fetchall() end = time.time() duration = end-start print 'fetchall time: '+str(duration)+'ms' cur.close() index = 0 for r in re: print '======='+str(index)+'========' si = 0 for i in r: print str(si)+': ['+str(i)+']' si = si+1 index = index+1 print 'end query' return re ''' @staticmethod def makeupsql(timelist=[],positionlist=[],mmsilist=[],countrylist=[]): ''' args: timestart : time string timeend : time string type: number, 0,1,2 pt1: tuple (,) pt2: tuple (,) mmsi: string 0 or other ''' sql = 'SELECT * FROM '+MYSQL_COL_TABLE['tablename']+' WHERE ' sql += "(" for t in timelist: print t sql += mysqlQuery.makeuptime(t[0],t[1]) + ' OR ' if sql[-4:] == ' OR ': sql = sql[:-4] sql += ")" sql +=' AND ' if sql[-1] == "(": sql = sql[:-1] print 'time :' +sql # sql += mysqlQuery.makeuptime(timestart,timeend) sql += "(" for p in positionlist: sql += mysqlQuery.makeupposition(p['tp'],p['pt1'],p['pt2']) + ' OR ' if sql[-4:] == ' OR ': sql = sql[:-4] sql += ")" sql +=' AND ' if sql[-1] == "(": sql = sql[:-1] #sql += mysqlQuery.makeupposition(tp,pt1,pt2) print 'position :' +sql sql += "(" tmp = mysqlQuery.makeupmmsi(mmsilist) if tmp != '': sql += tmp + ') AND ' if sql[-1] == "(": sql = sql[:-1] print 'mmsi :' +sql sql += '(' for country in countrylist: sql += mysqlQuery.makeupcountry(country) + ' OR ' if sql[-4:] == ' OR ': sql = sql[:-4] sql += ')' if sql[-1] == "(": sql = sql[:-1] # for c in countrylist: # sql += mysqlQuery.makeupcountry(c) + ' OR ' if sql[-4:] == ' OR ': sql = sql[:-4] if sql[-5:] == ' AND ': sql = sql[:-5] if sql[-6:] == 'WHERE ': sql = sql[:-6] # tmp = mysqlQuery.makeupmmsi(mmsi) # if tmp != '': # sql +=' AND '+tmp print 'sql :' + sql return sql @staticmethod def makeuptime(timestart,timeend): return '('+MYSQL_COL_TABLE['time'] +' >= '+timestart +' AND '+MYSQL_COL_TABLE['time'] +' <= '+timeend+')' @staticmethod def makeupposition(tp,pair1,pair2): sql = '(' if tp == 2: print 'position type is 2' #0 maxup = callnglat.getdespoint(pair1[0],pair1[1],0,pair2[0])[1] #90 maxright = callnglat.getdespoint(pair1[0],pair1[1],90,pair2[0])[0] #180 maxdown = callnglat.getdespoint(pair1[0],pair1[1],180,pair2[0])[1] #-90 maxleft = callnglat.getdespoint(pair1[0],pair1[1],-90,pair2[0])[0] sql = MYSQL_COL_TABLE['latitude'] +' <= ' +mysqlQuery.revertdegree(maxup)+ ' AND '\ + MYSQL_COL_TABLE['latitude'] +' >= ' +mysqlQuery.revertdegree(maxdown)+ ' AND ' if maxleft <= maxright: sql+= MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(maxleft)+ ' AND '\ + MYSQL_COL_TABLE['longitude'] + ' <= ' +mysqlQuery.revertdegree(maxright) else: sql += '(( ' + MYSQL_COL_TABLE['longitude'] + ' >= ' +mysqlQuery.revertdegree(maxleft) + ' AND '\ +MYSQL_COL_TABLE['longitude'] +' <= '+mysqlQuery.revertdegree(180)+' ) OR ( '\ +MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(-180) + ' AND '\ +MYSQL_COL_TABLE['longitude'] + ' <= ' + mysqlQuery.revertdegree(maxright)+'))' sql += ' AND ('+ mysqlQuery.makeupdistance(str(math.radians(pair1[0])),str(math.radians(pair1[1]))) + ' <= '+ str(pair2[0])+")" return sql if tp == 1: print 'position type is 1' if tp == 0: print 'position type is 0' pair2 = callnglat.getlowerrightpoint(pair1[0],pair1[1],pair2[0],pair2[1]) sql = MYSQL_COL_TABLE['latitude'] +' <= ' +mysqlQuery.revertdegree(pair1[1])+ ' AND '\ + MYSQL_COL_TABLE['latitude'] +' >= ' +mysqlQuery.revertdegree(pair2[1])+ ' AND ' if pair1[0] <= pair2[0]: sql+= MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(pair1[0])+ ' AND '\ + MYSQL_COL_TABLE['longitude'] + ' <= ' +mysqlQuery.revertdegree(pair2[0]) else: sql += '( ( ' + MYSQL_COL_TABLE['longitude'] + ' >= ' +mysqlQuery.revertdegree(pair1[0]) + ' AND '\ +MYSQL_COL_TABLE['longitude'] +' <= '+mysqlQuery.revertdegree(180)+' ) OR ( '\ +MYSQL_COL_TABLE['longitude'] + ' >= ' + mysqlQuery.revertdegree(-180) + ' AND '\ +MYSQL_COL_TABLE['longitude'] + ' <= ' + mysqlQuery.revertdegree(pair2[0])+') )' sql += ')' return sql @staticmethod def makeupmmsi(mmsilist): sql = "(" for m in mmsilist: if m != "0": sql += "( "+MYSQL_COL_TABLE['mmsi'] +' = '+m+" ) OR " if sql != "(" and sql[-4:]==" OR ": sql = sql[:-4] if sql == "(": return "" sql += ")" return sql @staticmethod def makeupcountry(country): countrycodes = Country.dmmsi[country] sql = "" for code in countrycodes: sql += " ("+MYSQL_COL_TABLE['mmsi'] + " LIKE '"+code+"%') OR " if sql[-4:] == " OR ": sql = sql[:-4] return sql @staticmethod def revertdegree(degree): return str(degree* 600000) @staticmethod #lon is string def lngdiff(lon1,lon2): return '( '+lon1+' - '+lon2+')' @staticmethod #lon is string def latdiff(lat1,lat2): return '( '+lat1+' - '+lat2+')' @staticmethod #centerlon centerlat is string, in rad def makeupdistance(centerlon,centerlat): # return '(2 * ASIN(SQRT(SIN( '+mysqlQuery.latdiff(centerlat,mysqlQuery.latcolrad)+' /2)*SIN( '+mysqlQuery.latdiff(centerlat,mysqlQuery.latcolrad)\ # +' /2)+ COS('+centerlat+' )*COS(' +mysqlQuery.latcolrad+')*SIN('\ # +mysqlQuery.lngdiff(centerlon,mysqlQuery.loncolrad)+'/2)'\ # +'*SIN('+mysqlQuery.lngdiff(centerlon,mysqlQuery.loncolrad)+'/2)))* 6371.0)' a = '(SIN( '+mysqlQuery.latdiff(centerlat,mysqlQuery.latcolrad)+' /2)*SIN( '+mysqlQuery.latdiff(centerlat,mysqlQuery.latcolrad)+' /2)+ COS('\ +centerlat+' )*COS(' +mysqlQuery.latcolrad+')*SIN('+mysqlQuery.lngdiff(centerlon,mysqlQuery.loncolrad)+'/2)*SIN('+mysqlQuery.lngdiff(centerlon,mysqlQuery.loncolrad)+'/2))' return '(2 * ATAN2(SQRT('+a+'),SQRT(1-'+a+'))*6371.0)' @staticmethod def makeupoutput(filetype,filename): print 'filetype:'+filetype print 'filename:' +filename if filetype == 'csv': return " into outfile '"+MYSQL_INFO['output_path']+filename+".csv' fields terminated by ',' lines terminated by '\r\n'" def run(self): print 'start' result = self.query() print result self.callback() def callback(self): self.queryconn.disconnect()