油烟在线监测数据爬取
blame | 历史 | 原始文档
zmc
2023-08-08 e792e9a60d958b93aef96050644f369feb25d61b 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

U  


k§ÐdHã@sVddlTddlZddlmZddlZddlmZmZddlZej     


d¡ddlmm Zdd„Zdd    „Zd


d„Zdd „Zdd„Zdd„Zdd„Zdd„Zdd„Zdd„Zdd„Zdd„Zdd„Zd d!„Zd"d#„Zd$d%„Zd&d'„Zd(d)„Z d*d+„Z!d,d-„Z"d.d/„Z#d0d1„Z$e %d2d3¡a&e %d4d3¡a'e %d5d3¡a(e %d6d3¡a)ed7ƒZ*e* +¡a,d8d9„Z-dS):é)Ú*N)Ú create_engine)ÚdatetimeÚ    timedeltaz../../cCs<t |d¡}t |d¡}||}|tddkp:|tddkS)Nú%Y-%m-%d %H:%Mé


©Úminutesiöÿÿÿ©rÚstrptimer©Údatestr1Údatestr2Údate1Údate2Ú    time_diff©rú7D:\z\workplace\VsCode\pyvenv\venv\src\write_to_MySql.pyÚ!is_time_difference_equals_10_minssrcCsfd}d}g}|dd…D]8}t||d|dƒdkr@| |¡|d}|d}qtdƒt|ƒ|S)NrééFu间断点为：)rÚappendÚprint)ÚlistÚiÚjÚbreak_pointÚitemrrrÚfind_break_points


 


rcCs>g}d}|D]}| |||…¡|}q| ||d…¡|S)Nr)r)rÚb_pointÚresultÚ


last_indexÚindexrrrÚpoint_write,sr#cCsBtj|ddddddgd}tdƒt|ƒ|jd    |d


ddddS) NÚdev_idÚ    exceptionÚexception_typeÚregionÚ


begin_timeÚend_time©Úcolumnsú


 


Ú abnormal_datarF©ÚnameÚconÚ    if_existsr"Úindex_label©ÚpdÚ    DataFramerÚto_sql©rr0ÚdatarrrÚabnormal_write_to_SQL7sr9cCsÊt|ƒ}t||ƒ}tdƒg}|D]v}g}| |dd¡| d¡| d¡| d¡| |t|ƒdd¡| |dd¡| |¡t|ƒq"tdƒ|D]}t|ƒq¦t||ƒtd    ƒdS)


NÚ


ru数据异常Ú0õ    å¾æ±‡åŒºrru!超标异常时间段数据为：u(超标油烟数据异常表写入完成!)rr#rrÚlenr9)rr0rÚ


split_listÚabnormalrÚtemprrrrr%Cs&


 


 


 


 


 


 


 


r%cCs.t |d¡}t |d¡}||}|tddkS)Nrérr


rrrrÚ!is_time_difference_equals_30_mins^srBcCsg}|dd}td|ƒ|dd…D]®}t|d|ƒrÌg}| |d¡| d¡| d¡| d¡| |d¡t |d    ¡td


d}tdt|ƒƒtd tt|ƒƒƒ| t|ƒdd…¡| |¡|d}q&tdƒ|D]}t|ƒqât||ƒtdƒdS)Nréu开始时间：rrõ设备故障Ú1r<rrru相减后结果：u相减后类型：éu设备故障的数据为：u/供电异常/掉线信息写入异常表完成!)    rrBrrrrÚstrÚtypeÚ not_Key_period_exceed_30_minutes)rr0Údevice_failureÚ    startTimerr@ÚstartTimeSubrrrrÚis_minutes_exceed_30es,


 


 


 


 


 


 


 


rMcCs„t |dd…d¡}t |dd…d¡}|tkr8|tkpf|tkrH|tkpf|tkrX|tkpf|tkof|tkdkrxtdƒdStdƒdS)NrCú%H:%MFu-开始或结束时间时间在非重点时段Tu处于重点时段©rrÚKey_period_noon_beginÚKey_period_noon_endÚKey_period_night_beginÚKey_period_night_endr©r(r)Úbegin1Úend1rrrÚis_time_not_between_key_period~sDrWcCs€t |dd…d¡}t |dd…d¡}|tkrH|tkrH|tkrH|tksh|tkrt|tkrt|tkrt|tkrttdƒdStdƒdS)NrCrNu'开始或结束时间处于重点时段Tu处于非重点时段FrOrTrrrÚis_time_between_key_periodŒs@rXcCsg}|D]Ö}t|d|dƒrvg}| |d¡| d¡| d¡| d¡| |d¡| |d¡| |¡qt|d|dƒrg}| |d¡| d¡| d¡| d¡| |d¡| |d¡| |¡qtdƒ|D]}t|ƒqìt||ƒtd    ƒdS)


NéérrDrEr<Ú2u供电异常的数据为：u+供电异常的信息写入异常表完成!)rWrrXrr9)rr0Úpower_supply_abnormalrr@rrrrrIœs2


 


 


 


 


 


 


 


rIcCsrg}| |d¡| |d¡| |d¡| |d¡| |d¡| |d¡| |d¡t|ƒ|S)NrérCéérYrZ©rr©rr@rrrÚ    refind_ex¾srbc    CsLtj|dddddddgd}td    ƒt|ƒ|jd


|dddd tdƒdS)NÚMV_Stat_CodeÚMV_Create_TimeÚMV_Data_TimeÚMV_Fan_ElectricityÚMV_Purifier_ElectricityÚMV_Fume_ConcentrationÚMV_Fume_Concentration2r*r,Úexceeding_st_datarFr.u超标表写入完成!r3r7rrrÚex_write_to_SQLÍs


rkcCstg}|D]*}t|dƒdkrtdƒ| t|ƒ¡q|D]}t|ƒq8t|ƒdkrht||ƒt||ƒntdƒdS)NrZru该条数据超标ru该店铺无超标数据)Úfloatrrrbr=r%rk)rr0Ú exceedingDatarrrrrÚisExceedingØs


 


rnc>Csæddddddddd    d


ddd ddddddddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-d.d/d0d1d2d3d4d5d6d7d8d9d:d;d<d=d>g>}g}tt ¡ƒ d?d@¡}tdAdBƒD]8}||dC|dCdC…}t|dBƒ}| ||dD¡q¢d@ |¡S)ENÚaÚbÚcÚdÚeÚfÚgÚhrrÚkÚlÚmÚnÚoÚpÚqÚrÚsÚtÚuÚvÚwÚxÚyÚzr;rEr[Ú3Ú4Ú5Ú6Ú7Ú8Ú9ÚAÚBÚCÚDÚEÚFÚGÚHÚIÚJÚKÚLÚMÚNÚOÚPÚQÚRÚSÚTÚUÚVÚWÚXÚYÚZÚ-ÚrrFré>)rGÚuuidÚuuid4ÚreplaceÚrangeÚintrÚjoin)ÚarrayOfrÚuirÚa1r„rrrÚgenerate_short_uuidïsŒÂ@


r´cCsPg}| tƒ¡| |d¡| |d¡| |d¡| d¡t|ƒ|S)Nrrr)rr´rrarrrÚ    refind_ea:s


rµcCsJtj|dddddgd}tdƒtd|ƒ|jd    |d


dddtd ƒdS)NÚDI_GUIDÚDI_CodeÚDI_NameÚDI_SupplierÚ    DI_Onliner*r,u"写入数据表 ï¼ŒDateFrame为：Úea_t_device_inforFr.u设备信息表写入完成!r3r7rrrÚea_write_to_SQLEs


 


r¼cCsätjdtd}|j ¡}tdƒ|D]}t|ƒq$tdt|ƒƒt |¡}t|ƒdkrÌ|dd…}tdƒt|ƒ|D]2}|dd…d    d


„|Dƒkrvt     ||dd…¡}qvtd|ƒt|ƒdkràt


||ƒntd|ƒt


||ƒdS) Nz8SELECT DI_Code,DI_Name,DI_Supplier FROM ea_t_device_info)r0u******** è®¾å¤‡ä¿¡æ¯******u!设备信息表记录条数为：ru去除重复为:rrYcss|]}|dd…VqdS©Nr)Ú.0r„rrrÚ    <genexpr>_sz+dev_info_data_if_exisitd.<locals>.<genexpr>u7设备信息表中有数据时，去重后的list为：u<设备表无数据，处理后待写入的设备信息为：)r4Úread_sqlÚcon_readÚvaluesÚtolistrr=ÚrdmÚremove_duplicates_dev_infoÚremove_given_data_dev_infor¼)rr0ÚdfÚresrÚlist1r@rrrrÚdev_info_data_if_exisitdOs&


 


 


 


 


rÊcCsDg}|D]}| t|ƒ¡qtdƒ|D]}t|ƒq(t||ƒdS)Nu设备数据转化后：)rrµrrÊ©rr0ÚstagingrrrrrÚea_t_devns


rÍcCs€g}| |d¡| |d¡| |d¡| |d¡| |d¡| |d¡| |d¡| |d¡t|ƒ|S)    Nrr]rCr^r_rYrZér`rarrrÚ    refind_fd~srÏc


CsNtj|ddddddddgd    }td


ƒt|ƒ|jd|dd d dtdƒdS)NrcrdrerfrgrhriZMV_Isduplicationr*u%写入分数数据表,DateFrame为：Úfd_t_minutevaluerFr.u分钟数据表写入完成!r3r7rrrÚfd_write_to_SQLŽs


rÑcCsDg}|D]}| t|ƒ¡qtdƒ|D]}t|ƒq(t||ƒdS)Nu分钟数据转化后：)rrÏrrÑrËrrrÚfd_t_minbute™s


rÒcCs,t||ƒt||ƒt||ƒt||ƒdSr½)rMrnrÍrÒ)Úhas_remove_duplicatesr0rrrÚ    write_all¨s


 


 


rÔz10:00rNz14:00z17:00z21:00úAmysql+mysqlconnector://root:1234@localhost:3306/fume?charset=utf8cCs&tdƒ}| ¡}t||ƒ| ¡dS)NrÕ)rÚconnectrÔÚclose)r8Úenginer0rrrÚwrite¹s


rÙ).ÚpymysqlÚpandasr4Ú


sqlalchemyrr«rrÚsysÚpathrÚ*src.core_modules.remove_duplicates_methodsÚcore_modulesÚremove_duplicates_methodsrÄrrr#r9r%rBrMrWrXrIrbrkrnr´rµr¼rÊrÍrÏrÑrÒrÔrrPrQrRrSrØrÖrÁrÙrrrrÚ<module>sH    "K