UpdtFct.py 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426
  1. import pandas as pd
  2. import pymysql
  3. from sqlalchemy import create_engine
  4. import datetime
  5. from sqlalchemy.orm import sessionmaker
  6. import pdb
  7. # #建立引擎
  8. # engine = create_engine(str(r"mysql+mysqldb://%s:" + '%s' + "@%s/%s") % ('root', 'pengmin', 'localhost', 'qixiangdb'))
  9. # #连接到qx数据库
  10. # conn_qx = pymysql.connect(
  11. # host='rm-bp10j10qy42bzy0q77o.mysql.rds.aliyuncs.com',
  12. # user='qx_cas',
  13. # password='Qx@123456',#Qx@123456
  14. # database='qx_cas',
  15. # charset='utf8'
  16. # )
  17. # #连接到本地数据库,输出物
  18. # conn_local = pymysql.connect(
  19. # host='localhost',
  20. # user='root',
  21. # password='pengmin',
  22. # database='qixiangdb',
  23. # charset='utf8'
  24. # )
  25. #计算下一个soc
  26. def getNextSoc(start_soc):
  27. '''输入当前的soc,寻找目标soc函数'''
  28. if start_soc>80:
  29. next_soc=80
  30. elif start_soc>60:
  31. next_soc=60
  32. elif start_soc>40:
  33. next_soc=40
  34. elif start_soc>20:
  35. next_soc=20
  36. else:
  37. next_soc=5#下一次目标soc
  38. return next_soc
  39. #更新全部5个区间段的factor
  40. def updtSnFct(sn_factor_df,end_soc,delta_range,range_soc):
  41. '''输入当前的soc区间段,里程变量量,soc变化量,输出新的df
  42. sn_factor_df为dataframe,delta_range单位为km,range_soc单位为km/persoc'''
  43. if end_soc==80:
  44. updtFctByCol(sn_factor_df,'a0',delta_range,range_soc)
  45. elif end_soc==60:
  46. updtFctByCol(sn_factor_df,'a1',delta_range,range_soc)
  47. elif end_soc==40:
  48. updtFctByCol(sn_factor_df,'a2',delta_range,range_soc)
  49. elif end_soc==20:
  50. updtFctByCol(sn_factor_df,'a3',delta_range,range_soc)
  51. elif end_soc<20:
  52. updtFctByCol(sn_factor_df,'a4',delta_range,range_soc)
  53. return sn_factor_df
  54. #更新一列的factor
  55. def updtFctByCol(sn_factor_df,colmun_name,delta_range,range_soc):
  56. '''更新制定列的factor,sn_factor_df为dataframe,新的系数更新到第一行。delta_range单位为km,
  57. range_soc单位为km/persoc,默认按照100km更新续驶里程权重'''
  58. range_soc_old=sn_factor_df.loc[0,colmun_name]#读取第0行的老factor
  59. debounce_range=100#更新权重
  60. new_factor=range_soc*((delta_range)/debounce_range)+range_soc_old*(1-delta_range/debounce_range)
  61. #在第1行,存储新的factor
  62. sn_factor_df.loc[1,colmun_name]=new_factor
  63. return sn_factor_df
  64. #更新今日的factor
  65. def updtTodayFct(factor_input,sn_day_df):
  66. '''更新今日的Factor***'''
  67. sn_factor_df_last=factor_input
  68. start_soc=sn_day_df.loc[0,'soc']#首行soc
  69. next_soc=getNextSoc(start_soc)#下一个目标soc
  70. start_range=sn_day_df.loc[0,'vehodo']#首行vehodo
  71. sn=sn_day_df.loc[0,'name']#sn号
  72. for index in range(len(sn_day_df)-1):
  73. #寻找分割点,
  74. index_soc=sn_day_df.loc[index,'soc']#当前行soc
  75. next_index_soc=sn_day_df.loc[index+1,'soc']#下一行soc
  76. if (index_soc>=next_soc)&(next_index_soc<next_soc):
  77. #当前行soc>目标soc,下一行低soc<目标soc,说明到达了分割点80-60-40-20
  78. delta_soc_tonext=start_soc-next_soc#两个距离点的soc差,单位为%
  79. delta_range_tonext=sn_day_df.loc[index,'vehodo']-start_range#两个时间点的距离差,单位为m
  80. delta_range_tonext_km=delta_range_tonext/1000#两个时间点的距离差,单位为km
  81. range_soc_tonext=(delta_range_tonext_km)/delta_soc_tonext#单位soc可行驶的公里数
  82. # print(sn+'start_soc: '+str(start_soc),'next_soc: '+str(next_soc),'delta_vehodo; '+str(round(delta_range_tonext_km,3))
  83. # +'km'+' range_soc:'+str(round(range_soc_tonext,3)))#调试用语句,看单次factor变化量
  84. if (delta_range_tonext_km>1)&(delta_range_tonext_km<5*delta_soc_tonext):
  85. #里程变化量>1km。且<5倍的soc变化量,大于此值认为不合理。
  86. sn_factor_df_last=updtSnFct(sn_factor_df_last,next_soc,delta_range_tonext_km,range_soc_tonext)
  87. start_soc=next_index_soc#变更开始soc
  88. next_soc=getNextSoc(start_soc)#变更结束soc
  89. start_range=sn_day_df.loc[index+1,'vehodo']#变更开始里程
  90. return sn_factor_df_last
  91. #对driveinfo进行预处理
  92. def snDayDfPreProcess(sn_day_df):
  93. '''预处理,判断是否在dirvemode,获取drivemode条件下的累计行驶距离。
  94. 增加delta_soc列,drive_flg列,vehodo列'''
  95. sn_day_df=sn_day_df.reset_index(drop=True)#重置index
  96. #增加列,计算delta_soc
  97. for index in range(len(sn_day_df)):
  98. if index==0:
  99. sn_day_df.loc[index,'delta_soc']=0
  100. else:
  101. sn_day_df.loc[index,'delta_soc']=sn_day_df.loc[index,'soc']-sn_day_df.loc[index-1,'soc']
  102. #增加列,判断是否在drive状态
  103. drive_flg=False
  104. accum_distance=0
  105. for index in range(len(sn_day_df)):
  106. if index==0:
  107. sn_day_df.loc[index,'drive_status']=drive_flg
  108. sn_day_df.loc[index,'vehodo']=0
  109. else:
  110. if (sn_day_df.loc[index,'delta_soc']<-0.1)|\
  111. ((sn_day_df.loc[index,'delta_soc']<=0)&(sn_day_df.loc[index,'distance']>500)):#soc处于下降状态,说明在drive
  112. drive_flg=True#置true
  113. elif sn_day_df.loc[index,'delta_soc']>0.1:#soc处于上升状态,说明不在drive
  114. drive_flg=False#置false
  115. accum_distance=0#清零
  116. sn_day_df.loc[index,'drive_flg']=drive_flg
  117. accum_distance+=sn_day_df.loc[index,'distance']#对行驶里程进行累加
  118. sn_day_df.loc[index,'vehodo']=accum_distance
  119. #筛选所有的drive信息行
  120. sn_day_drive_df=sn_day_df.loc[sn_day_df['drive_flg']==True,:]
  121. #按时间进行一次筛选,此处丢弃了晚上0点以后的行车数据
  122. sn_day_drive_df=sn_day_drive_df.reset_index(drop=True)#重置index
  123. return sn_day_drive_df
  124. #更新所有sn,连读多天的的factor
  125. def updtAllSnFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name='tb_sn_factor'):
  126. '''计算开始时间到结束时间的,所有sn的factor'''
  127. start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')#开始时间
  128. end_date_datetime=datetime.datetime.strptime(end_date,'%Y-%m-%d')#开始时间
  129. delta_day=(end_date_datetime-start_date_datetime).days#间隔天数
  130. i=1
  131. while i<=delta_day:
  132. end_date=(start_date_datetime+datetime.timedelta(days=i)).strftime("%Y-%m-%d")
  133. updtAllSnTodayFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name)#调用函数
  134. # print('update all sn factor from '+start_date+" to "+end_date)
  135. start_date=end_date
  136. i+=1#自加
  137. #更新所有sn,一天的factor
  138. def updtAllSnTodayFct(start_date,end_date, db_engine, db_local, db_qx, sn_table_name):
  139. ''''更新今天所有sn的factorx信息,start_date和end_date相隔一天。此处还可优化'''
  140. # conn_local = pymysql.connect(
  141. # host='localhost',
  142. # user='root',
  143. # password='pengmin',
  144. # database='qixiangdb',
  145. # charset='utf8'
  146. # )
  147. start_date_str="'"+start_date+"'"
  148. end_date_str="'"+end_date+"'"
  149. sql_cmd="select * from drive_info where time between "+start_date_str+" and "+end_date_str+" and distance!=0;"
  150. range_soc_df = pd.read_sql(sql_cmd, db_qx)#使用read_sql方法查询qx数据库
  151. #筛选出所有当日数据之后,筛选当日有更新的sn
  152. today_sn_list=range_soc_df['name'].unique().tolist()#[:100]#先一次更新5个
  153. #建立空的dataframe,用于承接所有更新的factor信息
  154. today_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])
  155. for sn in today_sn_list:
  156. #寻找factor_df,里面是否有sn号,如果没有sn对应信息,则新增信息。
  157. sn_str="'"+sn+"'"
  158. update_today_factor_flg=True
  159. sql_cmd3="select sn,date,a0,a1,a2,a3,a4 from {} where date=".format(sn_table_name)+start_date_str+" and sn="+sn_str
  160. factor_today_df=pd.read_sql(sql_cmd3, db_local)#使用read_sql方法查询local数据库
  161. if len(factor_today_df)>=1:
  162. # print(sn+' '+start_date_str+' factor exist in table! Factor not update.')
  163. update_today_factor_flg=False
  164. sql_cmd2="select sn,date,a0,a1,a2,a3,a4 from {} where date<".format(sn_table_name)+start_date_str+" and sn="+sn_str
  165. #此处可以限定每次查询的数量,例如不高于5行
  166. factor_df=pd.read_sql(sql_cmd2, db_local)#使用read_sql方法查询local数据库
  167. #按照sn号和日期进行去重,避免运行时重复产生factor数据,保留第一次出现的行。
  168. factor_df=factor_df.drop_duplicates(subset=['sn','date'],keep='first')
  169. if len(factor_df)==0:
  170. #如果没有搜索到factor历史数据,则声明一个新的进行初始化
  171. start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
  172. yesterday=(start_date_datetime+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
  173. #为sn申请一个新的factor,初始值为1
  174. factor_df=pd.DataFrame({'sn':sn,'date':yesterday,'a0':[1],'a1':[1],'a2':[1],'a3':[1],'a4':[1]})
  175. sn_factor_df=factor_df.loc[factor_df['sn']==sn,:]#筛选sn对应的factor
  176. sn_factor_df=sn_factor_df.sort_values(by='date',ascending='True')#按照日期排序
  177. sn_factor_df_last=sn_factor_df.tail(1).copy()#寻找最后一行,代表最近日期
  178. sn_factor_df_last=sn_factor_df_last.append(sn_factor_df_last)#新增加一行,用于存储新的factor
  179. sn_factor_df_last=sn_factor_df_last.reset_index(drop=True)#重置index
  180. sn_factor_df_last.loc[1,'date']=start_date#更改后一行的date为当前日期
  181. #筛选对应车辆的信息
  182. condition_sn=(range_soc_df['name']==sn)
  183. sn_day_df=range_soc_df.loc[condition_sn,:].copy()
  184. sn_day_df=sn_day_df.reset_index(drop=True)
  185. #使用updtTodayFct函数更新今天的factor
  186. if len(sn_day_df)>=2:
  187. #使用process函数,进行预处理
  188. sn_day_df=snDayDfPreProcess(sn_day_df)#预处理函数
  189. # 临时措施,删除每天晚上0点以后的数据,5点以前的数据,防止对驾驶cycle判断产生影响。
  190. day_start_time=datetime.datetime.strptime(start_date,'%Y-%m-%d')
  191. day_morning_time=day_start_time+datetime.timedelta(hours=5)
  192. morning_time_str=day_morning_time.strftime('%Y-%m-%d %H:%M:%S')
  193. sn_day_df=sn_day_df.loc[sn_day_df['time']>morning_time_str,:]#去除掉了每天晚上0点以后的数据,短期措施
  194. sn_day_df=sn_day_df.reset_index(drop=True)#重置index
  195. if len(sn_day_df)>=2:
  196. sn_factor_df_new=updtTodayFct(sn_factor_df_last,sn_day_df)#
  197. if (len(sn_factor_df_new)>=2)&(update_today_factor_flg):#如果factor
  198. today_sn_fct_df=today_sn_fct_df.append(sn_factor_df_new.loc[1,:])#筛选第一行,进行拼接,最后写入到数据库中
  199. #将today_sn_fct_df写入到数据库中,今天所有factor更新的系数,一次写入。
  200. if len(today_sn_fct_df)>=1:
  201. today_sn_fct_df.to_sql(sn_table_name,con=db_engine,chunksize=10000,if_exists='append',index=False)
  202. #更新一个sn,连续多天的factor
  203. def updtOneSnFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name='tb_sn_factor'):
  204. '''计算开始时间到结束时间的,一个sn的所有factor。
  205. 重复多次调用,updtOneSnTodayFct。
  206. '''
  207. start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')#开始时间
  208. end_date_datetime=datetime.datetime.strptime(end_date,'%Y-%m-%d')#开始时间
  209. delta_day=(end_date_datetime-start_date_datetime).days#间隔天数
  210. i=1
  211. while i<=delta_day:
  212. end_date=(start_date_datetime+datetime.timedelta(days=i)).strftime("%Y-%m-%d")
  213. # print('update one '+sn+'factor from '+start_date+" to "+end_date)
  214. updtOneSnTodayFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name)#调用函数,更新当日的factor。
  215. start_date=end_date
  216. i+=1#自加
  217. #更新一个sn,一天的factor
  218. def updtOneSnTodayFct(sn,start_date,end_date,db_engine, db_local, db_qx, sn_table_name):
  219. '''更新一个sn,一天的factor。'''
  220. #重新建立连接,更新数据库
  221. # conn_local = pymysql.connect(
  222. # host='localhost',
  223. # user='root',
  224. # password='pengmin',
  225. # database='qixiangdb',
  226. # charset='utf8'
  227. # )
  228. start_date_str="'"+start_date+"'"
  229. end_date_str="'"+end_date+"'"
  230. sn_str="'"+sn+"'"
  231. sql_cmd="select * from drive_info where time between "+start_date_str+" and "+end_date_str+\
  232. " and distance!=0 and name="+sn_str
  233. range_soc_df = pd.read_sql(sql_cmd, db_qx)#使用read_sql方法查询qx数据库
  234. if len(range_soc_df)>0:
  235. #筛选出所有当日数据之后,筛选当日有更新的sn
  236. today_sn_list=range_soc_df['name'].unique().tolist()
  237. #建立空的dataframe,用于承接所有更新的factor信息
  238. today_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])
  239. for sn in today_sn_list:
  240. #寻找factor_df,里面是否有sn号,如果没有sn对应信息,则新增信息。
  241. sn_str="'"+sn+"'"
  242. update_today_factor_flg=True
  243. sql_cmd3="select sn,date,a0,a1,a2,a3,a4 from {} where date=".format(sn_table_name)+start_date_str+" and sn="+sn_str
  244. factor_today_df=pd.read_sql(sql_cmd3, db_local)#使用read_sql方法查询local数据库
  245. if len(factor_today_df)>=1:
  246. # print(sn+' '+start_date_str+' factor exist in table! Factor not update.')
  247. update_today_factor_flg=False
  248. sql_cmd2="select sn,date,a0,a1,a2,a3,a4 from {} where date<=".format(sn_table_name)+start_date_str+" and sn="+sn_str
  249. factor_df=pd.read_sql(sql_cmd2, db_local)#使用read_sql方法查询local数据库
  250. #按照sn号和日期进行去重,避免运行时重复产生factor数据,保留第一次出现的行。
  251. factor_df=factor_df.drop_duplicates(subset=['sn','date'],keep='first')
  252. # pdb.set_trace()
  253. if len(factor_df)==0:
  254. #如果没有搜索到factor历史数据,则声明一个新的进行初始化
  255. start_date_datetime=datetime.datetime.strptime(start_date,'%Y-%m-%d')
  256. yesterday=(start_date_datetime+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
  257. factor_df=pd.DataFrame({'sn':sn,'date':yesterday,'a0':[1],'a1':[1],'a2':[1],'a3':[1],'a4':[1]})
  258. today_sn_fct_df=today_sn_fct_df.append(factor_df.loc[0,:])#将初始化的行记录到数据库
  259. sn_factor_df=factor_df.loc[factor_df['sn']==sn,:]#筛选sn对应的factor
  260. sn_factor_df=sn_factor_df.sort_values(by='date',ascending='True')#按照日期排序
  261. sn_factor_df_last=sn_factor_df.tail(1).copy()#寻找最后一行,代表最近日期
  262. sn_factor_df_last=sn_factor_df_last.append(sn_factor_df_last)#新增加一行,用于存储新的factor
  263. sn_factor_df_last=sn_factor_df_last.reset_index(drop=True)#重置index
  264. sn_factor_df_last.loc[1,'date']=start_date#更改后一行的date为当前日期
  265. #筛选对应车辆的信息
  266. condition_sn=(range_soc_df['name']==sn)
  267. sn_day_df=range_soc_df.loc[condition_sn,:].copy()
  268. sn_day_df=sn_day_df.reset_index(drop=True)
  269. #使用updtTodayFct函数更新今天的factor
  270. if len(sn_day_df)>=2:
  271. #使用process函数,进行预处理
  272. sn_day_df=snDayDfPreProcess(sn_day_df)#!!!!!!!!!!!增加
  273. # 临时措施,删除每天晚上0点以后的数据,5点以前的数据,防止对驾驶cycle判断产生影响。
  274. day_start_time=datetime.datetime.strptime(start_date,'%Y-%m-%d')
  275. day_morning_time=day_start_time+datetime.timedelta(hours=5)
  276. morning_time_str=day_morning_time.strftime('%Y-%m-%d %H:%M:%S')
  277. sn_day_df=sn_day_df.loc[sn_day_df['time']>morning_time_str,:]#去除掉了每天晚上0点以后的数据,短期措施
  278. sn_day_df=sn_day_df.reset_index(drop=True)#重置index
  279. if len(sn_day_df)>=2:
  280. sn_factor_df_new=updtTodayFct(sn_factor_df_last,sn_day_df)#更新fator的主函数
  281. if (len(sn_factor_df_new)>=2)&(update_today_factor_flg):#如果今日factor没有更新
  282. today_sn_fct_df=today_sn_fct_df.append(sn_factor_df_new.loc[1,:])#筛选第一行,进行拼接,最后写入到数据库中
  283. # #将today_sn_fct_df写入到数据库中
  284. if len(today_sn_fct_df)>=1:
  285. today_sn_fct_df.to_sql(sn_table_name,con=db_engine,chunksize=10000,if_exists='append',index=False)
  286. # print(sn+' factor will be update in table tb_sn_factor!')
  287. return sn_factor_df_new
  288. #更新最新的factor,一天调用一次。
  289. def updtNewestFctTb(current_time, db_local, sn_table_name='tb_sn_factor'):
  290. '''更新tb_sn_factor_newest,只保留最新日期的factor。
  291. 从tb_sn_factor中,筛选最新的日期。
  292. 函数每天运行一次,从tb_sn_factor中筛选最新日期的factor。'''
  293. current_time=current_time#当前时间
  294. current_time_str=current_time.strftime('%Y-%m-%d %H:%M:%S')#时间格式化为字符串,年-月-日 时-分-秒
  295. current_time_str="'"+current_time_str+"'"
  296. sql_cmd_4="select sn,date,a0,a1,a2,a3,a4 from {} where date<".format(sn_table_name)+current_time_str
  297. factor_all_df = pd.read_sql(sql_cmd_4, db_local)#使用read_sql方法查询qx数据库
  298. #筛选今天之前的所有factor,只保留最近的一天。
  299. sn_list=factor_all_df['sn'].unique().tolist()#筛选sn序列
  300. newest_sn_fct_df=pd.DataFrame([],columns=['sn','date','a0','a1','a2','a3','a4'])#声明空df
  301. for sn in sn_list:
  302. condition_sn=(factor_all_df['sn']==sn)
  303. factor_pick_df=factor_all_df.loc[condition_sn,:]#按照sn进行筛选
  304. factor_pick_df=factor_pick_df.sort_values(by='date')#按照日期排序
  305. factor_last_df=factor_pick_df.tail(1)#选择最后日期
  306. newest_sn_fct_df=newest_sn_fct_df.append(factor_last_df)#拼接到空df中
  307. #按照日期排序,只保留最近的一天,输出factor_unique_df,方法为replace。
  308. #本函数,每天需要运行一次,用于更新factor。
  309. # newest_sn_fct_df.to_sql(sn_newest_table_name,con=db_engine,chunksize=10000,\
  310. # if_exists='replace',index=False)
  311. return newest_sn_fct_df
  312. #使用factor和soc推荐剩余续驶里程
  313. def calDistFromFct(input_df):
  314. '''根据sn-time-soc-a0-a1-a2-a3-a4,使用factor正向计算计算VehElecRng。'''
  315. row_df=input_df.copy()
  316. soc=row_df['soc']#获取soc
  317. factor=[]
  318. factor.append(row_df['a4'])#0~20之间的factor
  319. factor.append(row_df['a3'])#20~40之间的factor
  320. factor.append(row_df['a2'])#40~60之间的factor
  321. factor.append(row_df['a1'])#60~80之间的factor
  322. factor.append(row_df['a0'])#80~100之间的factor
  323. gap=20
  324. yushu=soc%gap#余数部分
  325. zhengshu=soc//gap#整数部分
  326. i=0
  327. range=0
  328. while i<zhengshu:
  329. dur_factor=factor[i]#当前权重
  330. range+=dur_factor*gap#分段累加里程
  331. i=i+1
  332. if yushu>0.01:#避免soc=100时报错
  333. range=range+yushu*factor[zhengshu]#最后把余项对应的里程加上
  334. row_df['vehelecrng']=range#给VehElecRng列赋值
  335. return row_df
  336. #更新当前时间对应的里程,每5min调用一次
  337. def updtVehElecRng(db_qx, db_local, sn_newest_table_name='tb_sn_factor_newest', input_time='2021-07-29 12:01:00'):
  338. '''更新续驶里程,到tb_sn_factor_soc_range。
  339. 部署时设置每5min更新一次。
  340. '''
  341. #设置一个时间作为结束时间
  342. # current_time=datetime.datetime.now()
  343. current_time_raw=input_time#当前时间
  344. current_time=datetime.datetime.strptime(current_time_raw,'%Y-%m-%d %H:%M:%S')#字符串转时间
  345. #结束时间往前4min,59s,作为起始时间
  346. before6min_time_str=(current_time+datetime.timedelta(minutes=-4,seconds=-59)).strftime('%Y-%m-%d %H:%M:%S')#6min前
  347. before6min_time_str="'"+before6min_time_str+"'"
  348. current_time_str=current_time.strftime('%Y-%m-%d %H:%M:%S')#时间格式化为字符串
  349. current_time_str="'"+current_time_str+"'"
  350. #从drive_info里面读取,该时间段内的name,time,soc三列
  351. <<<<<<< HEAD
  352. sql_cmd="select name,time,soc from drive_info where time between "+before6min_time_str+" and "+current_time_str
  353. =======
  354. sql_cmd="select name,time,soc from drive_info where time between " + before6min_time_str + " and " + current_time_str
  355. >>>>>>> master
  356. # print(sql_cmd)
  357. range_soc_df = pd.read_sql(sql_cmd, db_qx)#使用read_sql方法查询qx数据库
  358. range_soc_df.rename(columns={'name':'sn'},inplace=True)#将name列重命名为sn列
  359. #任务2,从tb_sn_factor_newest里面读取最新的factor,获取距离今天最近的一个factor list
  360. sql_cmd_1="select sn,a0,a1,a2,a3,a4 from {}".format(sn_newest_table_name)
  361. # print(sql_cmd_1)
  362. sn_factor_newest_df_raw = pd.read_sql(sql_cmd_1, db_local)#使用read_sql方法查询qx数据库
  363. #任务3,将range_soc_df和sn_factor_newest_df_raw,双表合并成为一个新表格。
  364. sn_soc_factor_df=pd.merge(range_soc_df,sn_factor_newest_df_raw,how='left',on='sn')
  365. sn_soc_factor_df.fillna(1,inplace=True)#如果range_soc_df中有sn号,但sn_factor_newest_df_raw中没有。用1填充。
  366. # sn_soc_factor_df.head()
  367. #填充完成后,sn-time-soc-a0-a1-a2-a3-a4都已经齐全。
  368. #任务4,调用函数,将VehElecRng计算出来
  369. sn_soc_factor_range_df=pd.DataFrame([],columns=['sn','time','soc','a0','a1','a2','a3','a4','vehelecrng'])
  370. for index in sn_soc_factor_df.index.tolist():
  371. input_df=sn_soc_factor_df.loc[index,:]#挑选
  372. sn_soc_factor_range_row=calDistFromFct(input_df)#计算VehElecRng
  373. sn_soc_factor_range_df=sn_soc_factor_range_df.append(sn_soc_factor_range_row)#拼接
  374. ##任务5,将sn_soc_factor_range_df写入到tb_sn_factor_soc_range中,使用替换关系。
  375. # sn_soc_factor_range_df.to_sql(range_table_name,con=db_engine,chunksize=10000,\
  376. # if_exists='replace',index=False)
  377. return sn_soc_factor_range_df