def update_old_passenger_table(conn,date): if conn == None: conn = get_connection() mac_list_map = duration_one_day(conn, date) for sitecode in mac_list_map.keys(): if conn == None: conn = get_connection() macs = mac_list_map.get(sitecode) macs = macs + '#' cr = conn.cursor() sql = ("select F_PASSENGER_MACS, F_OLD_PERIOD from T_PASSENGER_OLD where F_SITE_CODE = '%s'")%(sitecode) cr.execute(sql) rs_macs = cr.fetchone() if rs_macs == None : insert_into_passenger_old_table(conn, sitecode, macs) else: old_macs = str(rs_macs[0].read()) mac_list = old_macs.split('#') period = int(rs_macs[1]) if not (len(mac_list) < period+1): start = old_macs.find('#') old_macs = old_macs[start+1:len(old_macs)] update_passenger_old_table(conn, sitecode, old_macs+macs) cr.close()
def update_passenger_old_table(conn, sitecode, macs): if conn == None: conn = get_connection() cr = conn.cursor() cr.setinputsizes(F_PASSENGER_MACS=cx_Oracle.CLOB) sql_update = ( "update T_PASSENGER_OLD set F_PASSENGER_MACS = :F_PASSENGER_MACS, F_UPDATE_TIME = sysdate where F_SITE_CODE = '%s'" ) % (sitecode) cr.execute(sql_update, F_PASSENGER_MACS=macs) conn.commit() cr.close() print "update"
def insert_into_passenger_old_table(conn, sitecode, macs): if conn == None: conn = get_connection() cr = conn.cursor() cr.setinputsizes(F_PASSENGER_MACS=cx_Oracle.CLOB) sql_insert = ( "insert into T_PASSENGER_OLD values ('%s','', '', :F_PASSENGER_MACS , '7', sysdate)" ) % (sitecode) cr.execute(sql_insert, F_PASSENGER_MACS=macs) conn.commit() cr.close() print "insert"
def get_sites(conn): if conn == None: conn = get_connection() cr = conn.cursor() sql = "SELECT F_CODE FROM T_SITE" cr.execute(sql) rows = cr.fetchall() cr.close() return rows
def get_terminal_feature(conn, start_time, end_time, site_code): if conn == None: conn = get_connection() sql = ( "SELECT F_MAC, F_CAP_TIME FROM T_TERMINAL_FEATURE WHERE F_CAP_TIME BETWEEN" " TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24-MI-SS') AND TO_TIMESTAMP('%s','YYYY-MM-DD HH24-MI-SS')" " AND F_SITE_CODE = '%s'") % (start_time, end_time, site_code) cr = conn.cursor() cr.execute(sql) rs = cr.fetchall() cr.close() return rs
def get_old_passenger_str(conn, sitecode): if conn == None: conn = get_connection() cr = conn.cursor() sql = ( "select F_PASSENGER_MACS from T_PASSENGER_OLD where F_SITE_CODE = '%s'" ) % (sitecode) cr.execute(sql) rs_macs = cr.fetchone() return rs_macs[0]
def get_mac_filter_data(conn, start_time, end_time, site_code): if conn == None: conn = get_connection() sql = ( "SELECT F_HOTSPOT_MAC FROM T_COLLECTION_HOTSPOT WHERE F_CAP_TIME BETWEEN" " TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24-MI-SS') AND TO_TIMESTAMP('%s','YYYY-MM-DD HH24-MI-SS')" " AND F_SITE_CODE = '%s'") % (start_time, end_time, site_code) cr = conn.cursor() cr.execute(sql) rs = cr.fetchall() mac_list = "" for row in rs: mac_list = mac_list + str(row[0]) + "," cr.close() return mac_list
# -*- coding:utf-8 -*- ''' Created on 2017-8-31 @author: onewalnut ''' from jdbc.connection import get_connection, free from statistics.passenger_duration import duration from statistics.passenger_info_statistics import passenger_statistics from statistics.update_old_passenger import update_old_passenger_table if __name__ == '__main__': conn = get_connection() date = '20170921' time = '18' mac_duration = duration(conn, date, time) statistics_res = passenger_statistics(conn, mac_duration) #print statistics_res #打印过滤后的客流人数 for item in statistics_res: print item + ":" + str(statistics_res[item][0].__len__()) + "@" + str( statistics_res[item]) #update_old_passenger_table(conn, date) conn.close() pass