def get_data_mid():
mid_num = {}
mid_num1 = {}
max_num = {}
max_num1 = {}
for i in range(1, 75):
mid_num[i] = []
mid_num1[i] = 0
for i in range(1, 25):
max_num[i] = []
max_num1[i] = 0
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
sql = 'select * from tb_area_min'
cur.execute(sql)
for i in cur:
mid_num[int(i[2])].append(int(i[8]))
for i in mid_num:
list = mid_num[i]
a = numpy.sum(list)
mid_num1[i] = a
insert_area_num_mid(mid_num1, cur, conn)
sql1 = 'select * from tb_area_mid'
cur.execute(sql1)
for i in cur:
max_num[int(i[2])].append(int(i[8]))
for i in max_num:
list = max_num[i]
a = numpy.sum(list)
max_num1[i] = a
insert_area_num_max(max_num1, cur, conn)
cur.close()
conn.close()
def write_db():
conn = mysql_conn.get_bike_connection()
cursor = conn.cursor()
sql = "select CompanyID, Longitude, Latitude from tb_bike_status_realtime"
cursor.execute(sql)
global dic
global num
not_cnt = 0
for item in cursor.fetchall():
point = [item[1], item[2]]
cid = item[0]
inside = False
for a in dic.keys():
inside = dic[a].contains_point(point)
if inside:
num[a][cid] += 1
break
if not inside:
# print point
not_cnt += 1
print "not in", not_cnt
for area in num.keys():
test = num[area]
print area
for name in test:
print name, test[name]
def get_result():
out_num = {}
for i in range(1, 3288):
out_num[i] = 0
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
sql = 'select * from tb_bike_status_realtime'
cur.execute(sql)
global bpund, lon, lat
for i in cur:
if i[3] == None or i[4] == None:
continue
logi = i[3]
lati = i[4]
ind_x = int((logi - bound['xmi']) / lon) + 1
ind_y = int((lati - bound['ymi']) / lat) + 1
is_out = i[9]
if is_out == None:
continue
if 1 <= ind_x <= 1000 and 1 <= ind_y <= 1000:
global map_index
reg = map_index[(ind_x, ind_y)]
if reg != 0:
out_num[reg] += is_out
return out_num
def insert_OD(d_ten):
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
insert_sql = 'insert into tb_bike_odgraph (OrientID, DestID, Count, DBtime) values(%s,%s,%s,%s) '
tup_list = []
for i in d_ten:
OD = i
t = d_ten[i]
cnt = 0
for k in range(0, len(t)):
DI = t[k][0]
if DI == OD:
continue
cnt += 1
C = t[k][1]
DT = time.strftime("%Y-%m-%d", time.localtime())
tup = (OD, DI, C, DT)
tup_list.append(tup)
if cnt == 10:
break
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
def check_bike(t1, t2, t):
conn = mysql_conn.get_bike_connection()
cursor = conn.cursor()
bike_status = {}
bike_fact = {}
for name in company:
bike_status[name] = set()
bike = set()
sql = "select bicycleno from tb_bike"
cursor.execute(sql)
for item in cursor.fetchall():
bike.add(item[0])
sql = "select companyid, bicycleno, orderid from tb_bike_gps_{2} " \
"where state = 0 and positiontime >= '{0}' and positiontime < '{1}'".format(t1, t2, t)
bt = time.clock()
cursor.execute(sql)
for item in cursor.fetchall():
bicycleno = item[1]
name = item[0]
bike_status[name].add(bicycleno)
et = time.clock()
print et - bt
for name in company:
bike_fact[name] = bike_status[name] & bike
print name, len(bike_fact[name])
cursor.close()
conn.close()
return bike_fact
def tick1():
now = datetime.now()
print 'tick1 is: %s' % now
yst = now + timedelta(days=-1)
st = yst.strftime("%Y-%m-%d 00:00:00")
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
sql = 'delete from tb_fq_company_history where DBTime <"{0}"'.format(st)
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
def insert_bike_area(sta_num):
conn = mysql_conn.get_bike_connection()
cur = conn.cursor()
insert_sql = 'update tb_bike_area set area_num = %s where area_id = %s '
tup_list = []
cnt = 0
for i in sta_num:
cnt += 1
tup = (sta_num[i], i)
tup_list.append(tup)
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
print 'insert !'
def insert_area_num(area_num):
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
insert_sql = 'update tb_area_min set area_num = %s where area_id = %s'
tup_list = []
for i in area_num:
AN = area_num[i]
AI = i
tup = (AN, AI)
tup_list.append(tup)
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
def insert_bicycle_num(by_dict):
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
insert_sql = 'insert into tb_bike_odgraph_statis (OrientID, Number, DBtime) values(%s,%s,%s)'
tup_list = []
for i in by_dict:
OD = i
nm = by_dict[i]
DT = time.strftime("%Y-%m-%d", time.localtime())
tup = (OD, nm, DT)
tup_list.append(tup)
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
def get_data_all():
record = [] # 公司名+坐标
bt = time.time()
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
sql = 'SELECT * from tb_bike_status_realtime where PositionTime > DATE_SUB(Now(),INTERVAL 1 month)'
cur.execute(sql)
for i in cur:
if i[3] is None or i[4] is None:
continue
record.append([i[0], (i[3], i[4])])
et = time.time()
cur.close()
conn.close()
print 'select cost', et - bt
return record
def insert_data(bike_fact, orders, DT):
st = DT.strftime("%Y-%m-%d")
conn = mysql_conn.get_bike_connection()
cur = conn.cursor()
sql = 'insert into tb_static(CompanyId,Order_num,Vehicle_num,DBtime) values(%s,%s,%s,%s)'
tup_list = []
for i in bike_fact:
if i == 'xiaoming' or i == 'qibei':
continue
CI = i
On = len(orders[i])
Vn = len(bike_fact[i])
# DT = time.strftime("%Y-%m-%d", time.localtime())
tup = (CI, On, Vn, st)
tup_list.append(tup)
cur.executemany(sql, tup_list)
conn.commit()
cur.close()
conn.close()
def insert_OD(d_num):
global xq_district
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='60.191.16.73', user='******', passwd='bike', db='bike', port=6052, charset='utf8')
cur = conn.cursor()
insert_sql = 'insert into tb_area_dest_num (Area_ID, area_district, DestCount, DBtime) values(%s,%s,%s,%s) '
tup_list = []
for i in d_num:
if i[1] == 0:
break
AI = i[0]
AD = xq_district[i[0]]
DC = i[1]
DT = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
tup = (AI, AD, DC, DT)
tup_list.append(tup)
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
def get_data_all(t1, t2):
or_dict = {}
record = []
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='60.191.16.73', user='******', passwd='bike', db='bike', port=6052)
cur = conn.cursor()
bt = time.time()
sql = 'SELECT * from tb_bike_gps_1801 WHERE CompanyId = "mb" and PositionTime>="{0}" and PositionTime< "{1}"'.format(
t1, t2)
cur.execute(sql)
for i in cur:
record.append([i[1], i[2], i[3], i[4], i[6], i[7]])
et = time.time()
print 'get orderid cost {0}'.format(et - bt)
for i in record:
or_dict[i[5]] = []
for i in record:
or_dict[i[5]].append(i[:5])
record1 = process(or_dict)
return record1
def get_data_all(t, t1, t2):
or_dict = {}
record = []
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
bt = time.time()
sql = 'SELECT * from tb_bike_gps_{2} WHERE CompanyId = "mb" and PositionTime>="{0}" and PositionTime< "{1}"'.format(
t1, t2, t[2:])
cur.execute(sql)
for i in cur:
record.append([i[2], i[3], i[4], i[5], i[7], i[8]])
# bicycle_no ,position_time,longitude,latitude,state,order_id
et = time.time()
print 'get orderid cost {0}'.format(et - bt)
for i in record:
or_dict[i[5]] = []
for i in record:
or_dict[i[5]].append(i[:5])
record1 = process(or_dict)
return record1
def check_orderid(t1, t2, t):
conn = mysql_conn.get_bike_connection()
cursor = conn.cursor()
orders = {}
for name in company:
orders[name] = set()
sql = "select companyid, bicycleno, orderid from tb_bike_gps_{2} " \
"where state = 0 and positiontime >= '{0}' and positiontime < '{1}'".format(t1, t2, t)
bt = time.clock()
cursor.execute(sql)
for item in cursor.fetchall():
orderid = item[2]
name = item[0]
if orderid != '':
orders[name].add(orderid)
et = time.clock()
print et - bt
for name in company:
print name, len(orders[name])
cursor.close()
conn.close()
return orders
def insert_od(company_num):
conn = mysql_conn.get_bike_connection()
# conn = MySQLdb.connect(host='172.18.106.159', user='******', passwd='tw_85450077', db='bike', port=3306)
cur = conn.cursor()
insert_sql = 'insert into tb_fq_company_history (AreaID, CompanyID, AreaCount, DBTime) values(%s,%s,%s,%s)'
tup_list = []
for i in company_num:
CI = i
d = company_num[i][0]
for t in range(1, 3288):
AI = t
if t in d:
AN = d[t]
else:
AN = 0
DT = time.strftime("%Y-%m-%d %H:00:00", time.localtime())
tup = (AI, CI, AN, DT)
tup_list.append(tup)
cur.executemany(insert_sql, tup_list)
conn.commit()
cur.close()
conn.close()
print 'insert done'
def get_data():
conn = mysql_conn.get_bike_connection()
cur = conn.cursor()
path_l = get_xq_index(cur)
sql = 'SELECT * from tb_bike_status_realtime where PositionTime > DATE_SUB(Now(),INTERVAL 1 day) '
cur.execute(sql)
pl = [] # point list
for i in cur:
pl.append((i[3], i[4]))
sta_num = {}
for i in path_l:
sta_num[i[1]] = 0
for i in pl:
if i[0] == None or i[1] == None:
continue
res = judge_region(i, path_l)
if res != 0:
if res not in sta_num:
sta_num[res] = 1
else:
sta_num[res] += 1
cur.close()
conn.close()
return sta_num
