def Insert_db(conn, table_name, result):
if conn == None:
conn = get_connection()
cr = conn.cursor()
# print('diaoyong')
# sql = "INSERT INTO SYS.TRAVEL_TIME_HK93TOHK92(HPHM, HPZL_y, JGSJ_x, JGSJ_y, TRAVEL_TIME, DATE_TYPES) VALUES (:1, :2, :3, :4, :5, :6)"
sql = (
"INSERT INTO %s(HPHM, TOTAL_NUM, WORK_NUM, HOLIDAY_NUM, START_DATE, END_DATE) VALUES (:1, :2, :3, :4, :5, :6)"
) % (table_name)
# # 如果插入数据库失败，可以取消下面3行注释，看报的错误是什么
# cr.executemany(sql, result)
# conn.commit()
# print('insert successfully!')
# # 批量运算时用下面的代码
try:
cr.executemany(sql, result)
conn.commit()
print('insert successfully!')
except:
conn.rollback()
# 关闭游标、关闭数据库连接
cr.close()
conn.close()
return 0
def plate_match(conn, SSID, CDBH, start_time, end_time):
# SSID=['HK-92','HK-107','HK-93'] [终点，起点], start_time是一个list，形式为：['2019-7-2 17:00:00', '2019-7-2 16:00:00']
date_types = holiday(start_time[0][0:10])
if conn == None:
conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
# 查询路段下游的车牌和经过时间，使用时从下面4个终点里面选择一个取消注释（注释掉其他3个终点）
query_plate_low = (
"SELECT HPHM, HPZL, JGSJ FROM SJCJ_T_CLXX_LS_ERXIAO WHERE SSID='%s' AND CDBH in %s AND JGSJ>=to_date('%s','yyyy-mm-dd hh24:mi:ss') AND JGSJ<=to_date('%s','yyyy-mm-dd hh24:mi:ss')"
) % (SSID[0], CDBH[0], start_time[0], end_time[0])
cr.execute(query_plate_low)
query_res_low = cr.fetchall()
dataframe_res_low = pd.DataFrame(list(query_res_low),
columns=['HPHM', 'HPZL', 'JGSJ'])
# 查询路段上游的车牌和经过时间
# query_plate_upper = ("SELECT HPHM, HPZL, JGSJ FROM SJCJ_T_CLXX_LS WHERE SSID='%s' AND CDBH in ('1','2','3','4','5','6','10','11','12') AND JGSJ>=to_date('%s','yyyy-mm-dd hh24:mi:ss') AND JGSJ<=to_date('%s','yyyy-mm-dd hh24:mi:ss')")%(SSID[1],start_time[0],end_time[0])
query_plate_upper = (
"SELECT HPHM, HPZL, JGSJ FROM SJCJ_T_CLXX_LS_ERXIAO WHERE SSID='%s' AND CDBH in %s AND JGSJ>=to_date('%s','yyyy-mm-dd hh24:mi:ss') AND JGSJ<=to_date('%s','yyyy-mm-dd hh24:mi:ss')"
) % (SSID[1], CDBH[1], start_time[0], end_time[0])
cr.execute(query_plate_upper)
query_res_upper = cr.fetchall()
dataframe_res_upper = pd.DataFrame(list(query_res_upper),
columns=['HPHM', 'HPZL', 'JGSJ'])
# 上下游匹配并计算路段旅行时间,merge之后jgsj自动分成2列:jgsj_x（下游）和jgsj_y（上游）
merge_ls = pd.merge(dataframe_res_low, dataframe_res_upper, on='HPHM')
merge_ls = merge_ls.drop(index=(merge_ls.loc[(
merge_ls['HPHM'] == '车牌')].index)).reset_index() # 删除hphm列中，值为车牌的行
merge_ls = merge_ls.drop(
index=(merge_ls.loc[merge_ls['JGSJ_x'] < merge_ls['JGSJ_y']].index
)).reset_index() # 如果下游检测时间小于上游检测时间，说明匹配错误
merge_ls['JGSJ_x'] = pd.to_datetime(
merge_ls['JGSJ_x'],
format='%Y-%m-%d %H:%M:%S') # 将JGSJ_x列转为datetime标准格式
merge_ls['JGSJ_y'] = pd.to_datetime(
merge_ls['JGSJ_y'],
format='%Y-%m-%d %H:%M:%S') # 将JGSJ_y列转为datetime标准格式
merge_ls['travel_time'] = merge_ls['JGSJ_x'].sub(
merge_ls['JGSJ_y']) # jgsj_x - jgsj_y，即下游检测时间-上游检测时间
# merge_ls['travel_time'] = pd.to_numeric(merge_ls['travel_time'].dt.seconds, downcast='integer') # 计算结果从timedelta转为int，以秒为单位
merge_ls['travel_time'] = (merge_ls['travel_time'] /
np.timedelta64(1, 's')).astype(
int) # 计算结果从timedelta转为int，以秒为单位
merge_ls = merge_ls.sort_values(by=['JGSJ_y'],
ascending=True) # 将dataframe按照JGSJ_y的排序
merge_ls = merge_ls.drop_duplicates(
'JGSJ_x', 'last', inplace=False) # 按JGSJ_x，去除该列下面的重复行,删除重复项并保留最后一次出现的项
merge_ls = merge_ls.drop_duplicates(
'JGSJ_y', 'last', inplace=False) # 按JGSJ_y，去除该列下面的重复行,删除重复项并保留最后一次出现的项
# print(merge_ls['travel_time'].dtypes)
merge_ls = merge_ls[['HPHM', 'HPZL_y', 'JGSJ_x', 'JGSJ_y',
'travel_time']] # 提取列表中的5列组成新的merge_ls
merge_ls['date_types'] = date_types # 新增一列，用于表征日期性质，0：工作日；1：周末；2：节假日
free(conn, cr)
return merge_ls
def Query_Afternoon_Travel_Time(conn, HPHM):
if conn == None: conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
# 同时查6个表中的平均旅行时间和样本数（晚上放学期间）
query_sql = ("SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK107TOHK92 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'"
"UNION SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK107TOHK93 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'"
"UNION SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK92TOHK107 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'"
"UNION SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK92TOHK93 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'"
"UNION SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK93TOHK92 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'"
"UNION SELECT AVG(TRAVEL_TIME),COUNT(TRAVEL_TIME) FROM TRAVEL_TIME_HK93TOHK107 WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '16:30' AND '18:00'")\
% (HPHM, HPHM, HPHM, HPHM, HPHM, HPHM)
cr.execute(query_sql)
query_res = cr.fetchall()
dataframe_res = pd.DataFrame(list(query_res),
columns=['AVG_T',
'SAM_NUM']) # 查询结果转为dataframe格式
dataframe_res = dataframe_res.sort_values(
by=['SAM_NUM'], ascending=False).reset_index() # 将查询结果按样本数量降序排列
dataframe_res = dataframe_res[['AVG_T',
'SAM_NUM']] # 仅提取'AVG_T', 'SAM_NUM'列
# print(dataframe_res)
# # 统计对应车牌在6个子路段上的总样本数时，取消下面代码注释
# travel_time, sample_numbers = dataframe_res['AVG_T'][0], dataframe_res['SAM_NUM'].sum() # 将dataframe中的第一行，赋值给travel_time和sample_numbers（样本数是指该车牌在6个子路段上的总共样本数）
# # 统计用于计算平均旅行时间的样本数(SAM_NUM)，对应车牌在6个子路段上的总样本数(TOTAL_NUM)
travel_time, sample_numbers, total_numbers = dataframe_res[
'AVG_T'][0], dataframe_res['SAM_NUM'][0], dataframe_res['SAM_NUM'].sum(
) # 当需要统计：用于计算平均旅行时间的样本数时取消注释
return travel_time, sample_numbers, total_numbers
def Travel_time_query(conn, table_name, car_type, start_time, end_time):
if conn == None: conn = get_connection()
cr = conn.cursor()
query_sql = (
"SELECT HPZL_Y, JGSJ_X, TRAVEL_TIME FROM %s WHERE HPZL_Y='%s' AND "
"TO_CHAR(JGSJ_X,'YYYY-MM-DD HH24:MI:SS') BETWEEN '%s' AND '%s' ORDER BY JGSJ_X ") % (
table_name, car_type, start_time, end_time)
cr.execute(query_sql)
query_res = cr.fetchall()
query_res = pd.DataFrame(data=query_res, columns=['HPZL_Y', 'JGSJ_X', 'TRAVEL_TIME'])
return query_res
def Query_ls(conn, SSID, start_time, end_time, cdbh):
if conn == None: conn = get_connection() # conn为None时，建立数据库连接
cr = conn.cursor() # 建立查询游标
# 从卡口流水表中查询给定卡口编号、时段和车道组编号的JGSJ
query_sql = (
"SELECT JGSJ FROM SJCJ_T_CLXX_LS WHERE SSID = '%s' AND CDBH IN %s AND TO_CHAR(JGSJ,'YYYY-MM-DD HH24:MI:SS') BETWEEN '%s' AND '%s'"
) % (SSID, cdbh[0], start_time, end_time)
cr.execute(query_sql) # 执行查询
query_res = cr.fetchall(
) # 提取查询结果，赋予变量query_res，查询结果形式：[(结果1),(结果2),(结果3)...]
query_res = [i[0] for i in query_res] # 重新组织查询结果，变成[结果1,结果2,...]
series_res = pd.Series(
data=query_res,
dtype='datetime64[ns]') # 将jgsj列转为Series格式，数据类型指定为datetime64
result = pd.Series(
np.ones(len(query_res)),
index=series_res) # 将查询到的JGSJ时间列设置为索引，Series的值为1（索引—1辆车）
ls_query_result = result.sort_index(
ascending=True) # 将构建好的Series按索引升序排序（输出结果时间升序）
return ls_query_result # 返回流水查询结果
def Insert_db(conn, table_name, result):
if conn == None:
conn = get_connection()
cr = conn.cursor()
# print('diaoyong')
# sql = "INSERT INTO SYS.TRAVEL_TIME_HK93TOHK92(HPHM, HPZL_y, JGSJ_x, JGSJ_y, TRAVEL_TIME, DATE_TYPES) VALUES (:1, :2, :3, :4, :5, :6)"
sql = (
"INSERT INTO %s(HPHM, HPZL_y, JGSJ_x, JGSJ_y, TRAVEL_TIME, DATE_TYPES) VALUES (:1, :2, :3, :4, :5, :6)"
) % (table_name)
try:
cr.executemany(sql, result)
conn.commit()
print('insert successfully!')
except:
conn.rollback()
# 关闭游标、关闭数据库连接
cr.close()
conn.close()
return 0
def Work_day_frequency_vehicles(conn):
if conn == None: conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
# 查询 HIGH_FRE_VEHICLES表的HPHM、TOTAL_NUM、WORK_NUM列
query_sql = "SELECT HPHM, TOTAL_NUM, WORK_NUM FROM HIGH_FRE_VEHICLES"
cr.execute(query_sql) # 执行查询
query_res_total = cr.fetchall() # 查询结果从游标中提取并赋值给变量query_res_total
df_res_total = pd.DataFrame(list(query_res_total),
columns=['HPHM', 'TOTAL_NUM',
'WORK_NUM']) # 查询结果转化成pd的dataframe
df_res = df_res_total.groupby('HPHM').sum() # 按HPHM分组合并，将其他列HPHM相同的值求和
df_res = df_res.drop(index=(
df_res.loc[1.0 *
(df_res['WORK_NUM'] / df_res['TOTAL_NUM']) < 0.9].index
)) # 保留工作日出行频数占总频数的90%以上的车牌
df_res_c1 = df_res.sort_values(
by=['TOTAL_NUM'], ascending=False).reset_index() # 将上面的结果按降序排列
return df_res_c1
def get_travel_month(conn, hphm):
if conn == None: conn = get_connection()
cr = conn.cursor() # 生成连接的游标
# 查询语句,查询的3个表，都可以忽略信号配时的影响（路中卡口）
query_sql_1 = (
"SELECT JGSJ_x from TRAVEL_TIME_HK93TOHK107 WHERE HPHM='%s'") % (hphm)
query_sql_2 = (
"SELECT JGSJ_x from TRAVEL_TIME_HK92TOHK107 WHERE HPHM='%s'") % (hphm)
query_sql_3 = (
"SELECT JGSJ_x from TRAVEL_TIME_HK93TOHK92 WHERE HPHM='%s'") % (hphm)
cr.execute(query_sql_1) # 执行查询语句1
res1 = cr.fetchall() # 查询结果赋值res1
cr.execute(query_sql_2) # 执行查询语句2
res2 = cr.fetchall() # 查询结果赋值res2
cr.execute(query_sql_3) # 执行查询语句3
res3 = cr.fetchall() # 查询结果赋值res3
res = res1 + res2 + res3 # 查询的3个list合并，结果示例 [(res1),(res2),...]
free(conn, cr) # 关闭查询
lis_res = [] # 提取查询到的旅行时间（datetime格式），并存储至该list
for ele in res: # 查询结果合并list中的每个元素（tuple），将它的第一个值添加到上一行的list
lis_res.append(ele[0])
month_list = [x.month for x in lis_res] # 提取lis_res中每个元素的月份信息并存在一个列表中
return month_list # 返回结果
def Query_Morning_Travel_NUM(conn, HPHM):
# if am == 'am':
# JGSJ_x = ('06','07')
# else: JGSJ_x = ('16','17','18')
if conn == None: conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
query_sql_1 = (
"SELECT COUNT(a.JGSJ_x) FROM TRAVEL_TIME_HK93TOHK107 a "
"WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '06:45' AND '07:20'"
) % (HPHM)
query_sql_2 = (
"SELECT COUNT(b.JGSJ_x) FROM TRAVEL_TIME_HK92TOHK107 b "
"WHERE HPHM='%s' AND TO_CHAR(JGSJ_x,'HH24:MI') BETWEEN '06:45' AND '07:20'"
) % (HPHM)
cr.execute(query_sql_1)
res_1 = cr.fetchall()
cr.execute(query_sql_2)
res_2 = cr.fetchall()
res = res_1[0][0] + res_2[0][0]
return res
def High_frequency_vehicles(conn, start_time):
if conn == None: conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
# 按日期查询一周内上下学期间车辆的出行次数，并返回出行次数大于等于5次的车牌号及其总体出行次数
query_time_interval = Week_Period(
start_time) # 根据给定的start_time，生成一个列表，包含该周的起始日期和终止日期
# 查询车牌号码和经过时间记录数（仅上下学期间）
query_sql = (
"SELECT HPHM, COUNT(JGSJ) FROM SJCJ_T_CLXX_LS_OCT WHERE SSID='HK-107' AND CDBH IN ('1','2','3','4') "
"AND TO_CHAR(JGSJ,'HH24') IN ('16','17')"
" AND JGSJ BETWEEN to_date('%s','yyyy-mm-dd hh24:mi:ss') AND to_date('%s','yyyy-mm-dd hh24:mi:ss') GROUP BY HPHM "
) % (query_time_interval[0], query_time_interval[1])
cr.execute(query_sql) # 执行查询
query_res_total = cr.fetchall() # 查询结果从游标中提取并赋值给变量query_res_total
dataframe_res_total = pd.DataFrame(list(query_res_total),
columns=['HPHM', 'TOTAL_numbers'
]) # 查询结果转化成pd的dataframe
holiday_list = Get_Holidays_during_Aweek(
start_time
) # 根据给定的start_time，生成该周的休息日 eg：['2019-05-011 00:00:00', '2019-05-12 00:00:00']
holiday_query_list = [] # 将休息日的查询结果转化为dataframe格式，存入该列表
for i in range(len(holiday_list)):
query_sql_for_holiday = (
"SELECT HPHM, COUNT(JGSJ) FROM SJCJ_T_CLXX_LS_OCT WHERE SSID='HK-107' AND CDBH IN ('1','2','3','4') "
"AND TO_CHAR(JGSJ,'HH24') IN ('16','17')"
" AND JGSJ BETWEEN to_date('%s','yyyy-mm-dd hh24:mi:ss') AND to_date('%s','yyyy-mm-dd hh24:mi:ss') GROUP BY HPHM "
) % (holiday_list[i], Add_serval_days(holiday_list[i], 1)
) # 查休息日当天的出行情况
cr.execute(query_sql_for_holiday) # 执行查询
query_res_holiday = cr.fetchall() # 查询结果提取
To_dataframe = pd.DataFrame(list(query_res_holiday),
columns=['HPHM',
'HOLIDAY_numbers']) # 转为dataframe
holiday_query_list.append(To_dataframe) # 加入列表holiday_query_list
# 将休息日的查询结果合成为一个dataframe
df_holiday = pd.concat(holiday_query_list,
ignore_index=True) # 将所有查询结果合并（上下合并）
df_holiday = df_holiday.groupby(
'HPHM').sum().reset_index() # 按HPHM列的内容进行表内合并，对其他列执行求和的操作
# 将df_holiday和dataframe_res_total整合
# df_holiday.rename(columns={'HOLIDAY_numbers': 'holiday_numbers'}, inplace=True) # 重新命名指定列的列名
df_holiday_total_tem = pd.concat([dataframe_res_total, df_holiday],
ignore_index=True,
sort=True) # 上下合并2个表格，空白地方填NaN
# print(df_holiday_total_tem)
df_holiday_total = df_holiday_total_tem.groupby(
'HPHM').sum().reset_index() # 按HPHM列的内容，进行表内整合，对其他列执行求和的操作
df_holiday_total['WORK_numbers'] = df_holiday_total['TOTAL_numbers'].sub(
df_holiday_total['HOLIDAY_numbers']
) # TOTAL_numbers列减去HOLIDAY_numbers列，赋值给新增的WORK_numbers列
df_holiday_total['START_DATE'] = datetime.datetime.strptime(
query_time_interval[0], '%Y-%m-%d %H:%M:%S') # 新增一列用于表征计算起始的日期
df_holiday_total['END_DATE'] = datetime.datetime.strptime(
query_time_interval[1], '%Y-%m-%d %H:%M:%S') # 新增一列用于表征统计截止的日期（不包括当天）
# 通过上述流程，将同一周内出行的车牌及工作日、休息日出行次数统计完成
# 下面将统计、筛选高频车（一周内出行5次以上，工作日出行3次以上的作为高频车）
df_holiday_total = df_holiday_total.drop(index=(
df_holiday_total.loc[df_holiday_total['TOTAL_numbers'] < 5].index))
df_holiday_total = df_holiday_total.drop(
index=(df_holiday_total.loc[df_holiday_total['WORK_numbers'] < 3].index
)).reset_index() # 删去工作日出行次数小于3次的
df_holiday_total = df_holiday_total.drop(
index=(df_holiday_total.loc[df_holiday_total['HPHM'] == '车牌'].index
)).reset_index() # 删去车牌识别失败的行
df_holiday_total = df_holiday_total[[
'HPHM', 'TOTAL_numbers', 'WORK_numbers', 'HOLIDAY_numbers',
'START_DATE', 'END_DATE'
]] #提取对应列重构dataframe
return df_holiday_total
def plate_match(conn, SSID, CDBH, start_time, end_time):
date_types = holiday(start_time[0][0:10])
start_ssid, end_ssid = SSID[0], SSID[1]
start_cdbh, end_cdbh = CDBH[0], CDBH[1]
if conn == None:
conn = get_connection() # 建立数据库连接
cr = conn.cursor() # 生成连接的游标
# 查询路段上游的车牌和经过时间
query_plate_upper = (
"SELECT HPHM, HPZL, JGSJ, CDBH FROM SJCJ_T_CLXX_LS WHERE SSID='%s' AND CDBH in %s AND JGSJ BETWEEN '%s' AND '%s'"
) % (start_ssid, start_cdbh, start_time[0], end_time[0])
cr.execute(query_plate_upper)
query_res_upper = cr.fetchall()
dataframe_res_upper = pd.DataFrame(
list(query_res_upper), columns=['HPHM', 'HPZL', 'JGSJ', 'CDBH'])
# 查询路段下游的车牌和经过时间
query_plate_low = (
"SELECT HPHM, HPZL, JGSJ, CDBH FROM SJCJ_T_CLXX_LS WHERE SSID='%s' AND CDBH in %s AND JGSJ BETWEEN '%s' AND '%s'"
) % (end_ssid, end_cdbh, start_time[0], end_time[0])
cr.execute(query_plate_low)
query_res_low = cr.fetchall()
dataframe_res_low = pd.DataFrame(list(query_res_low),
columns=['HPHM', 'HPZL', 'JGSJ', 'CDBH'])
# 上下游匹配并计算路段旅行时间,merge之后jgsj自动分成2列:jgsj_x（下游）和jgsj_y（上游）
merge_ls = pd.merge(dataframe_res_low, dataframe_res_upper, on='HPHM')
# print(merge_ls)
merge_ls = merge_ls.drop(index=(merge_ls.loc[(
merge_ls['HPHM'] == '车牌')].index)).reset_index() # 删除hphm列中，值为车牌的行
merge_ls = merge_ls.drop(
index=(merge_ls.loc[merge_ls['JGSJ_x'] < merge_ls['JGSJ_y']].index
)).reset_index() # 如果下游检测时间小于上游检测时间，说明匹配错误
merge_ls['JGSJ_x'] = pd.to_datetime(
merge_ls['JGSJ_x'],
format='%Y-%m-%d %H:%M:%S') # 将JGSJ_x列转为datetime标准格式
merge_ls['JGSJ_y'] = pd.to_datetime(
merge_ls['JGSJ_y'],
format='%Y-%m-%d %H:%M:%S') # 将JGSJ_y列转为datetime标准格式
merge_ls['travel_time'] = merge_ls['JGSJ_x'].sub(
merge_ls['JGSJ_y']) # jgsj_x - jgsj_y，即下游检测时间-上游检测时间
# 计算结果从timedelta转为int，以秒为单位
merge_ls['travel_time'] = (merge_ls['travel_time'] /
np.timedelta64(1, 's')).astype(int)
merge_ls = merge_ls.sort_values(by=['JGSJ_y'],
ascending=True) # 将dataframe按照JGSJ_y的排序
merge_ls = merge_ls.drop_duplicates(
'JGSJ_x', 'last', inplace=False) # 按JGSJ_x，去除该列下面的重复行,删除重复项并保留最后一次出现的项
merge_ls = merge_ls.drop_duplicates(
'JGSJ_y', 'last', inplace=False) # 按JGSJ_y，去除该列下面的重复行,删除重复项并保留最后一次出现的项
# print(merge_ls['travel_time'].dtypes)
merge_ls = merge_ls[[
'HPHM', 'HPZL_y', 'JGSJ_x', 'JGSJ_y', 'travel_time', 'CDBH_x', 'CDBH_y'
]] # 提取列表中的5列组成新的merge_ls
merge_ls.rename(columns={
'JGSJ_x': 'JGSJ_OUT',
'JGSJ_y': 'JGSJ_IN',
'CDBH_x': 'CDBH_OUT',
'CDBH_y': 'CDBH_IN',
'HPZL_y': 'HPZL'
},
inplace=True)
merge_ls['date_types'] = date_types # 新增一列，用于表征日期性质，0：工作日；1：周末；2：节假日
merge_ls['SSID_IN'] = start_ssid
merge_ls['SSID_OUT'] = end_ssid
columns_order = [
'HPHM', 'HPZL', 'SSID_IN', 'CDBH_IN', 'JGSJ_IN', 'SSID_OUT',
'CDBH_OUT', 'JGSJ_OUT', 'travel_time', 'date_types'
]
merge_ls = merge_ls[columns_order]
free(conn, cr)
return merge_ls