def task():
qyer_list = []
daodao_list = []
for line in db.QueryBySQL(
'select source_id from hourong.attr_unid where source="qyer|daodao"'
):
source_id = json.loads(line['source_id'])
daodao_id = source_id['daodao']
qyer_id = source_id['qyer']
qyer_list.append(qyer_id)
daodao_list.append(daodao_id)
qyer_info = get_qyer(qyer_list)
daodao_info = get_daodao(daodao_list)
datas = []
for line in db.QueryBySQL(
'select id,source_id,city_id,city_name,country_name from hourong.attr_unid where source="qyer|daodao"'
):
miaoji_id = line['id']
source_id = json.loads(line['source_id'])
daodao_id = source_id['daodao']
name, name_en, site = daodao_info.get(daodao_id)
qyer_id = source_id['qyer']
q_name, q_name_en, q_site = qyer_info.get(qyer_id)
city_id = line['city_id']
city_name = line['city_name']
country_name = line['country_name']
data = (miaoji_id, name, name_en, 'daodao', daodao_id, site, city_id,
city_name, country_name)
datas.append(data)
data = (miaoji_id, q_name, q_name_en, 'qyer', qyer_id, q_site, city_id,
city_name, country_name)
datas.append(data)
print(insert_db(datas))
def get_comment_dict():
comment_cn_dict = defaultdict(list)
comment_en_dict = defaultdict(list)
sql = 'select miaoji_id,review_text from Comment.{0} where language="en" and miaoji_id like "r%"'.format(
COMMENT_TABLE)
for line in db_localhost.QueryBySQL(sql):
comment_en_dict[line['miaoji_id']].append(line['review_text'])
sql = 'select miaoji_id,review_text from Comment.{0} where language="zhCN" and miaoji_id like "r%"'.format(
COMMENT_TABLE)
for line in db_localhost.QueryBySQL(sql):
comment_cn_dict[line['miaoji_id']].append(line['review_text'])
return comment_en_dict, comment_cn_dict
def get_img_flag():
sql = 'select * from image_info'
count = 0
for line in db_localhost.QueryBySQL(sql):
redis_dict.set(line['file_name'], line['file_info'])
count += 1
print(count)
def get_source_id(urls):
url_source_id = {}
sql = 'select id,url from tp_shop_basic_0801 where url in (%s)' % ','.join(
["\"" + x + "\"" for x in urls])
for line in db.QueryBySQL(sql):
url_source_id[line['url']] = line['id']
return url_source_id
def get_id_tag_dict():
tag_tag_en_dict = {}
sql = 'select id,tag from tag.restaurant_tagS'
for line in db.QueryBySQL(sql):
source_id = line['id']
tag = line['tag']
tag_tag_en_dict[source_id] = tag
return tag_tag_en_dict
def get_max_id():
id_set = set()
for line in db.QueryBySQL('select id from attr_merge.attr_unid'):
try:
id_set.add(int(line['id'][1:]))
except:
continue
return 'v' + str(max(id_set))
def task():
id_site = get_source_id_site()
datas = []
sql = 'select source_id from shop_merge.shop_unid'
for line in db.QueryBySQL(sql):
site = id_site.get(line['source_id'], '')
data = (site, line['source_id'])
datas.append(data)
print(update_db(datas))
def get_qyer(qyer_list):
sql = 'select id,name,name_en,site from hourong.qyer where id in (%s)' % ','.join(
["\"" + x + "\"" for x in qyer_list])
source_id_info = {}
for line in db.QueryBySQL(sql):
source_id = line['id']
name = line['name']
name_en = line['name_en']
site = line['site']
source_id_info[source_id] = (name, name_en, site)
return source_id_info
def get_daodao(daodao_list):
sql = 'select id,name,name_en,site from hourong.tp_attr_basic_0801 where id in (%s)' % ','.join(
["\"" + x + "\"" for x in daodao_list])
source_id_info = {}
for line in db.QueryBySQL(sql):
source_id = line['id']
name = line['name']
name_en = line['name_en']
site = line['site']
source_id_info[source_id] = (name, name_en, site)
return source_id_info
def task():
data_dict = {}
for line in db.QueryBySQL(
"select * from attr_merge.attraction_middle where city_id in ('10023','10120','10159','10175','10191','10196','10200','10206','10215','10218','10223','10233','10236','10237','10239','10248','10252','10254','10255','10257','10264','10265','10271','10273','10279','10286','10291','10295','10296','10297','10301','10306','10308','10312','10317','10323','10327','10329','10330','10331','10335','10337','10339','10342','10344','10346','10349','10350','10351','10353','10354','10358','10360','10361','10362','10364','10368','10369','10370','10371','10372','10377','10381','10382','10385','10386','10390','10391','10392','10395','10396','10397','10399','10401','10403','10404','10405','10406','10409','10410','10420','20046','20047','40004','10422','10425','40005','10430','10431','10433','10434','20048','20049','20050','10435','10437','10446','10459','10460','10466','10468','10470','10476','10477','10478','10486','10489','10490','10492','10495','10496','10497','10499','10500','10503','10504','10509','10511','10514','10518','10521','10524','10528','20052','10532','10533','10534','20053','20054','20055','10536','10537','10538','10539','20058','20059','10548','10552','10554','20061','20062','10560','20063','40009','20066','20067','20068','10564','40011','20069','10567','10570','10571','10577','10582','10586','10589','10590','20074','20075','20076','10599','10600','20084','10609','20085','10617','10634','10641','30038','20088','20089','20090','10646','10649','10654','10658','10659','10662','10664','20091','20093','20094','10682','10684','10690','10691','10694','10697','10698','10699','10703','10704','20099','20100','20101','20102','10712','10731','10733','10737','10759','10763','10775','10788','10795','10815','10834','10835','10848','10859','10872','10895','10903','10937','10960','10962','10971','10972','10999','11002','11025','11034','11035','11041','11046','11052','11056','11060','11062','11067','11078','11094','11098','11100','11105','11109','11116','11132','11135','11143','11149','11154','11156','11162','11173','11175','11184','11185','11200','30043','11207','11212','11214','11215','11217','11221','11226','11230','11231','11233','11237','11242','11248','11255','11260','50054','11285','11294','11298','11308','11326','11334','30036','30033','30031','30026','30023','30022','30014','30005','30002','50060','50064','50065','50081','50084','50099','50127','11433','11436','11437','11438','11441','11443','11445','11446','11447','50150','50161','50165','50166','50167','50170','50172','50177','50180','50186','50190','50192','50194','50219','50221','60002','50223','50224','50228','30045','30046','30050','30057','30058','30062','30063','30065','30070','30075','30078','30080','30082','30083','30084','30085','30090','50238','50242','50255','50273','50284','50305','50314','50324','50329','50331','50349','50366','50372','50375','50399','50405','50409','50416','50462','50463','50465','50478','50526','50530','50532','50565','50574','50577','50588','50590','50594','50632','50662','50679','50680','50683','50685','50696','50720','50727','20215','20226','20240','30115','30120','30122','30124','30136','30147','30148','50734','50738','50737','50735','30161','20372','20374','20377','20378','20379','20380','20381','20382','20385','20386','20387','20388','20389','20390','20392','20394','20396','10001','10002','10003','10004','10005','10006','10007','10008','10009','10010','10011','10012','10013','10014','10015','10016','10017','10018','10019','10020','10021','10022','10024','10025','10026','10027','10029','10030','10031','10032','10033','10035','10036','10037','10038','10040','10041','10042','10043','10044','10045','10046','10047','10048','10050','10051','10052','10053','10054','10055','10056','10057','10058','10059','10060','10061','10062','10063','10065','10066','10067','10068','10069','10070','10071','10072','10073','10075','10078','10079','10080','10082','10086','10087','10088','10089','10090','10092','10094','10095','10098','10099','10100','10101','10102','10103','10104','10105','10107','10109','10110','10115','10116','10118','10121','10122','10123','10125','10126','10127','10128','10130','10131','10132','10133','10135','10137','10139','10140','10141','10145','10147','10149','10152','10156','10158','10161','10163','10168','10171','10180','10182','10183','10184','10186','10187','10189','10190','10192','10194','10199','10205','10208','10210','10219','10221','10222','10224','10230','10232','10242','10249','10261','10263','10272','10275','10276','10281','10282','10283','10293','10300','10303','10305','10314','10315','10328','10333','10356','10384','10388','10402','20043','10415','20045','10423','10426','10427','10428','30041','10443','10483','10487','10494','10540','50001','50002','50003','10549','10550','10551','10553','10555','10556','10561','20064','50004','50005','50006','50007','50009','50010','50012','50013','50014','50016','50017','50018','50019','50020','50021','20070','20071','20072','10598','20080','20082','20083','10605','10615','10621','20139','20087','30039','10683','10701','50771','10729','11095','11096','11123','50027','50028','50029','50030','50032','50034','50036','50038','50040','50041','50045','50046','50047','50048','50049','50050','50055','50056','50057','50059','11300','11322','11333','30035','30032','30030','30025','30019','30020','30018','30010','30042','50061','50062','50063','11365','50070','50072','50073','50075','50077','50078','50079','50080','50082','50083','50086','50088','50090','50091','50093','50094','50095','50148','50097','50098','50100','50101','50102','50104','50105','50108','50109','50110','50111','50113','50114','50115','50116','50146','50122','50125','50129','50130','50131','50134','50138','50139','50142','50147','50149','50039','11413','11429','11010','20140','20141','20142','20145','20146','20148','20149','20150','20152','20156','20159','20162','20165','20166','20167','20169','20170','20171','20172','20173','20177','20178','20180','20183','50153','50176','50183','50184','50203','50215','50220','50245','50256','50301','50307','50379','50460','50477','50557','50624','50767','20209','20065','20212','20214','20216','20270','20269','20296','20301','20308','50744','50745','50746','11488','20393','50775','50778','50795','50801','50809','50818','50837','50838','11513','11517','11518','11519','11520','11522','11526','11528','11530','11531','11534','11535','11538','11566','11542','11545','11548','11550','11556','11560','11562','11568','11569','10074','10084','10085','10093','10096','11471','10117','10119','10134','10136','10138','10142','10144','10146','10148','10151','10153','10154','10160','10166','10167','10169','10170','10172','10174','10176','10177','10185','10188','10195','10197','10198','10201','10203','10204','10207','10209','10213','10214','10216','10220','10225','10228','10229','10231','10234','10238','10246','10247','10250','10251','10256','10258','10259','10262','10267','10268','10269','11477','10274','10277','10285','10288','10292','10294','10298','10299','10302','10307','10309','10310','10311','10316','10319','10321','10325','10326','10332','10334','10336','10338','10340','10341','10345','10352','10355','10359','10363','10365','10366','10373','10374','10375','10380','10383','10387','10389','10393','10394','10398','10407','10411','10414','10417','10418','10419','40001','40002','40003','30040','60001','10469','10512','20056','20057','40007','40012','40014','40015','40018','40020','11478','20078','20081','40023','10603','10604','10606','10607','10613','10616','40031','10651','20098','11472','10736','10767','10787','10842','11388','11470','11134','11267','11271','11337','11341','11344','30015','30011','30003','30001','11473','50068','11404','11401','11400','11399','11396','11394','11391','11392','50143','11406','11408','11410','11411','11418','11420','11423','11426','11427','11428','11430','11432','11440','11442','11451','50768','50173','50193','50200','50208','50211','50217','50233','11479','30055','30056','30059','30067','30068','30086','30087','11387','50247','50482','50555','50649','20189','20197','20198','20199','20220','20221','20222','20223','20224','20225','20227','20228','20229','20230','11480','20237','20238','30091','30092','30093','30094','30095','30096','30097','30098','30099','30100','30101','30102','30103','30104','30105','30106','30107','30108','30110','30111','30112','30113','30114','30117','30119','30121','30125','30127','30129','30132','30134','30138','30143','30144','30145','30146','40025','40028','30149','30150','30152','60003','60005','60006','60007','60008','50742','50740','11474','20314','20315','20316','20318','20319','20321','20322','20323','20325','20326','20328','20330','20332','20333','20335','20337','20338','20339','20340','20341','20342','20343','20345','20346','20347','20350','20351','20353','30157','30163','30164','30165','30170','30172','30173','30174','30175','20354','20236','20357','20358','20359','20360','20361','20364','20366','20368','50748','50749','50750','40030','50752','50753','50754','50755','50757','50758','50759','50760','50761','50765','30177','60009','60010','60011','60012','60014','60017','60018','60019','60020','11481','11482','11485','11486','11487','11489','11491','11492','11493','11494','11495','11496','11497','11498','11499','11501','11502','11503','11504','11505','11506','40032','40033','40034','40035','40036','40038','40039','40040','40041','40043','40044','40046','40047','40048','40049','40054','40055','40056','40058','40059','40060','40061','40062','40063','40064','40065','40066','40067','40068','40070','40071','40072','40073','40074','40075','40076','40078','40079','40080','40081','40082','40083','40084','40085','40086','40087','40088','40089','50774','50782','50791','50798','50803','50804','50805','50811','50815','50816','50835','50840','50857','50862','20397','20398','20399','20400','20401','20402','20403','20404','20405','20406','20407','20408','20409','20410','20411','20412','20413','20414','20415','20416','20417','20418','20419','20420','20421','20422','20423','20424','20425','20426','20427','40090','40091','40092','40093','40094','40095','40096','40097','11571','11572','11573','11574','11575','11576','11577','11578','11579','11580','11581','11582','11583','20428','20429','20430','20431','20432','20433','20434','20435','20436','20437','20438','40098','40099','40100','40101','40102','40103','40104','60021','60022','60023','60024','60025','60026','60027','60028','60029','60030','60031','40105','40106','40107','40108','40109','20439','20440','20441','20442','20443','20444','20445','20446','20447','20448','20449','11584','11585','11586','11587','11588','11589','11590','11591','11592','11593','11594','11595','11596','11597','11598','11599','20450','20451','20452','20453','20454','20455','20456','20457','20458','20459','20460','20461','20462','20463','20464','20465','20466','20467','20468','20469','20470','20471','20472','20473','20474','20475','20476','20477','20478','20479','20480','20481','20482','20483','20484','20485','20486','20487','20488','50865','50866','50867','50868','50869','50870','50871','50872','40110','11600','11601','11602','11603','11604','11605','11606','11607','11608','11609','11610','11611','11612','11613','11614','11615','11616','11617','11618','11619','11620','11621','11622','11623','11624','11625','11626','11627','11628','11629','11630','11631','11632','11633','11634','11635','11636','11637','11638','11639','11640','11641','11642','11643','11644','11645','11646','11647','11648','11649','11650','11651','11652','11653','11654','11655','11656','11657','11658','11659','11660','11661','11662','11663','11664','11665','11666','11667','11668','11669','11670','11671','11672','11673','11674','11675','11676','11677','11678','11679','11680','11681','11682','11683','11684','11685','11686','11687','11688','11689','11690','11691','11692','11693','11694','11695','11696','11697','11698','11699','11700','11701','11702','11703','11704','11705','11706','11707','11708','11709','30181','30182','40111','40112','40113','11710','11711','11712','11713','11714','11715','11716','11717','11718','40114','40115','40116','40117','40118','40119','40120','40121','11719','11720','11721','40122','40123','40124','40125','40126','40127','40128','40129','40130','40131','40132','40133','40134','40135','40136','50873','50874','50875','50876','50877','50878','50879','50880','50881','50882','50883','50884','50885','50886','50887','50888','50889','50890','50891','50892','50893','50894','50895','50896','50897','50898','50899','50900','50901','50902','50903','50904','50905','50906','50907','40137','40138','40139','40140','40141','40142','40143','40144','40145','40146','40147','40148','40149','40150','40151','40152','11722','11723','11724','11725','11726','11727','11728','11729','11730','11731','11732','11733','40153','40154','40155','40156','40157','40158','40159','40160','40161','40162','40163','40164','40165','40166','40167','40168','40169','40170','40171','40172','20489','20490','20491','40173','40174','40175','40176','40177','40178','40179','11734','11735','11736','11737','11738','11739','20492','20493','20494','20495','20496','20497','20498','20499','20500','20501','20502','20503','20504','20505','20506','20507','20508','20509','20510','20511','20512','20513','20514','20515','20516','20517','20518','20519','20520','20521','20522','20523','20524','20525','20526','20527','20528','20529','20530','20531','20532','20533','20534','20535','20536','20537','20538','20539','20540','20541','20542','20543','20544','20545','20546','20547','20548','20549','20550','20551','20552','20553','20554','20555','20556','20557','20558','20559','20560','20561','20562','20563','20564','20565','20566','20567','20568','20569','20570','20571','20572','20573','20574','20575','20576','20577','20578','20579','20580','20581','20582','20583','20584','20585','20586','20587','20588','20589','20590','20591','20592','20593','20594','20595','20596','20597','20598','20599','20600','20601','20602','20603','20604','20605','20606','20607','20608','20609','20610','20611','20612','20613','20614','20615','20616','20617','20618','20619','20620','20621','20622','20623','20624','20625','20626','20627','20628','20629','20630','20631','20632','20633','20634','20635','20636','20637','20638','20639','20640','20641','20642','20643','20644','20645','20646','20647','20648','20649','20650','20651','20652','20653','20654','20655','20656','20657','20658','20659','20660','20661','20662','20663','20664','20665','20666','20667','20668','20669','20670','20671','20672','20673','20674','20675','20676','20677','20678','20679','20680','20681','20682','20683','20684','20685','20686','20687','20688','20689','20690','20691','50908','50909','50910','50911','50912','50913','50914','50915','50916','50917','50918','50919','50920','50921','50922','60032','60033','60034','60035','60036','60037','60038','60039','60040','60041','60042','60043','60044','60045','40180','40181','20692','20693','20694','20695','20696','20697','20698','20699','20700','20701','20702','50923','50924','50925','50926','50927','50928','50929','50930','50931','20703','20704','20705','20706','20707','20708','40182','40183','40184','40185','20709','20710','20711','20712','20713','20714','20715','20716','20717','20718','20719','20720','20721','60046','60047','60048','60049','60050','60051','60052','60053','60054','60055','60056','60057','60058','60059','50932','50933','50934','50935','50936','50937','50938','50939','50940','50941','50942','50943','50944','50945','50946','50947','50948','50949','50950','50951','50952','50953','50954','50955','50956','50957','50958','50959','40186','40187','40188','40189','40190','40191','40192','40193','40194','40195','40196','40197','40198','30183','20722','20723','20724','20725','20726','11740','11741','11742','11743','11744','11745','11746','11747','11748','11749','11750','11751','11752','11753','11754','11755','11756','11757','11758','11759','11760','11761','11762','11763','11764','11765','11766','11767','11768','11769','11770','11771','11772','11773','11774','11775','11776','50960','50961','50962','50963','50964','50965','50966','50967','60060','60061','40199','40200','40201','40202','40203','40204','40205','40206','40207','40208','40209','40210','40211','40212','40213','40214','40215','40216','40217','40218','40219','40220','40221','40222','40223','40224','40225','40226','40227','30184','20727','20728','20729','20730','20731','20732','40228','40229','40230','40231','40232','40233','40234','40235','40236','40237','40238','40239','40240','40241','11777','11778','11779','11780','11781','11782','11783','11784','11785','11786','11787','11788','11789','40242','40243','40244','40245','40246','40247','11790','11791','11792','11793','11794','11795','11796','11797','11798','11799','11800','11801','11802','11803','11804','11805','11806','11807','11808','11809','11810','11811','11812','11813','11814','11815','11816','40248','40249','40250','50968','50969','50970','50971','50972','50973','50974','50975','50976','50977','50978','50979','50980','50981','50982','50983','50984','50985','50986','50987','50988','50989','50990','50991','50992','50993','50994','50995','50996','50997','50998','50999','51000','51001','51002','51003','51004','51005','51006','40251','40252','40253','40254','40255','40256','40257','51007','51008','51009','51010','51011','51012','51013','51014','11817','11818','11819','11820','11821','11822','11823','11824','11825','11826','11827','11828','11829','11830','11831','11832','11833','11834','11835','11836','11837','11838','11839','11840','11841','11842','11843','11844','11845','11846','11847','11848','11849','11850','11851','11852','11853','11854','60062','60063','60064','60065','60066','60067','60068','60069','60070','60071','60072','20733','20734','20735','20736','20737','20738','20739','20740','20741','20742','20743','20744','20745','40258','40259','40260','40261','40262','40263','40264','40265','40266','51015','51016','51017','51018','51019','51020','51021','51022','51023','51024','51025','51026','51027','51028','51029','51030','40267','40268','40269','40270','40271','40272','40273','40274','40275','20746','20747','20748','20749','20750','20751','20752','20753','20754','20755','20756','20757','20758','20759','20760','20761','20762','20763','20764','20765','20766','20767','20768','20769','20770','20771','20772','20773','20774','20775','20776','20777','20778','20779','51031','51032','20780','20781','20782','20783','20784','20785','20786','20787','20788','20789','20790','20791','51033','51034','51035','51036','30185','30186','30187','30188','30189','30190','30191','30192','30193','30194','30195','30196','30197','60073','60074','60075','60076','60077','51037','51038','51039','51040','51041','51042','51043','51044','20792','60078','60079','60080','60081','60082','60083','60084','60085','60086','60087','60088','60089','60090','60091','60092','60093','60094','60095','60096','60097','60098','60099','60100','40276','40277','40278','40279','40280','40281','40282','40283','40284','40285','40286','40287','11855','11856','11857','11858','11859','11860','11861','11862','11863','11864','11865','11866','11867','11868','11869','11870','11871','11872','11873','11874','11875','11876','11877','11878','11879','11880','11881','11882','11883','11884','11885','11886','11887','11888','11889','11890','11891','11892','11893','11894','11895','11896','11897','11898','11899','11900','11901','11902','11903','11904','11905','11906','11907','11908','11909','11910','11911','11912','11913','11914','11915','11916','11917','11918','11919','11920','11921','11922','11923','11924','11925','11926','11927','11928','30198','30199','30200','30201','51045','51046','11929','11930','11931','11932','11933','11934','11935','11936','11937','11938','11939','11940','11941','11942','11943','11944','11945','11946','11947','11948','11949','11950','11951','11952','11953','11954','11955','11956','11957','11958','11959','11960','11961','11962','11963','11964','11965','11966','11967','11968','11969','11970','11971','11972','11973','11974','11975','11976','11977','11978','11979','11980','11981','11982','11983','11984','11985','11986','11987','11988','11989','11990','11991','11992','11993','11994','11995','11996','11997','11998','11999','12000','12001','12002','12003','12004','12005','12006','12007','12008','12009','12010','12011','12012','12013','12014','12015','12016','12017','12018','12019','12020','12021','12022','12023','12024','12025','12026','12027','12028','12029','12030','12031','12032','12033','12034','12035','12036','12037','12038','12039','12040','12041','12042','12043','12044','12045','12046','12047','12048','12049','12050','12051','12052','12053','12054','12055','12056','12057','12058','12059','12060','12061','12062','12063','12064','12065','12066','12067','12068','12069','12070','12071','12072','12073','12074','12075','12076','12077','12078','12079','12080','12081','12082','12083','12084','12085','12086','12087','12088','12089','12090','12091','12092','12093','12094','12095','12096','12097','12098','12099','12100','12101','12102','12103','12104','12105','12106','12107','12108','12109','12110','12111','12112','12113','12114','12115','12116','12117','12118','12119','12120','12121','12122','12123','12124','12125','12126','12127','12128','12129','12130','12131','12132','12133','12134','12135','12136','12137','12138','12139','12140','12141','12142','12143','12144','12145','12146','12147','12148','12149','12150','12151','12152','12153','12154','12155','12156','12157','12158','12159','12160','12161','12162','12163','12164','12165','12166','12167','12168','12169','12170','12171','12172','12173','12174','12175','12176','12177','12178','12179','12180','12181','12182','12183','12184','12185','12186','12187','12188','12189','12190','12191','12192','12193','12194','12195','12196','12197','12198','12199','12200','12201','12202','12203','12204','12205','12206','12207','12208','12209','12210','12211','12212','12213','12214','12215','12216','12217','12218','12219','12220','12221','12222','12223','12224','12225','12226','12227','12228','12229','12230','12231','12232','12233','12234','12235','12236','12237','12238','12239','12240','12241','12242','12243','12244','12245','12246','12247','12248','12249','12250','12251','12252','12253','12254','12255','12256','12257','12258','12259','12260','12261','12262','12263','12264','12265','12266','12267','12268','12269','12270','12271','12272','12273','12274','12275','12276','12277','12278','12279','12280','12281','12282','12283','12284','12285','12286','12287','12288','12289','12290','12291','12292','12293','12294','12295','12296','12297','12298','12299','12300','12301','12302','12303','12304','12305','12306','12307','12308','12309','12310','12311','12312','12313','12314','12315','12316','12317','12318','12319','12320','12321','12322','12323','12324','12325','12326','12327','12328','12329','12330','12331','12332','12333','12334','12335','12336','12337','12338','12339','12340','12341','12342','12343','12344','12345','12346','12347','12348','12349','12350','12351','12352','12353','12354','12355','12356','12357','12358','12359','12360','12361','12362','12363','12364','12365','12366','12367','12368','12369','12370','12371','12372','12373','12374','12375','12376','12377','12378','12379','12380','12381','12382','12383','12384','12385','12386','12387','12388','12389','12390','12391','12392','12393','12394','12395','12396','12397','12398','12399','12400','12401','12402','12403','12404','12405','12406','12407','12408','12409','12410','12411','12412','12413','12414','12415','12416','12417','12418','12419','12420','12421','12422','12423','12424','12425','12426','12427','12428','12429','12430','12431','12432','12433','12434','12435','12436','12437','12438','12439','12440','12441','12442','12443','12444','12445','12446','12447','12448','30202','30203','12449','12450','12451','12452','12453','12454','12455','12456','12457','12458','12459','12460','12461','12462','12463','12464','12465','12466','12467','12468','12469','12470','12471','12472','12473','12474','12475','12476','12477','12478','12479','12480','12481','12482','12483','12484','12485','12486','12487','12488','12489','12490','12491','12492','12493','12494','12495','12496','12497','40288','40289','40290','40291','40292','20793','20794','20795','30204','40293','40294','40295','40296','40297','40298','40299','40300','40301','12498','12499','12500','12501','12502','12503','12504','12505','12506','12507','12508','12509','12510','12511','12512','12513','12514','12515','12516','12517','12518','12519','12520','12521','12522','12523','20796','20797','20798','20799','20800','20801','20802','20803','20804','20805','20806','20807','20808','20809','20810','20811','20812','20813','20814','20815','20816','20817','20818','20819','20820','20821','20822','20823','20824','20825','20826','20827','20828','20829','20830','20831','20832','20833','20834','20835','20836','20837','20838','20839','20840','20841','20842','20843','20844','20845','20846','20847','20848','20849','20850','20851','20852','20853','20854','20855','20856','20857','20858','20859','20860','20861','20862','20863','20864','20865','20866','20867','20868','20869','20870','20871','20872','20873','20874','30205','30206','30207','30208','30209','30210','30211','30212','30213','30214','30215','30216','30217','30218','30219','30220','30221','30222','30223','30224','30225','30226','30227','20875','20876','20877','20878','20879','20880','20881','20882','20883','20884','20885','20886','20887','20888','20889','20890','20891','20892','20893','20894','20895','20896','20897','20898','20899','20900','20901','20902','20903','20904','20905','20906','20907','20908','20909','20910','20911','20912','20913','20914','20915','20916','20917','20918','20919','20920','20921','20922','20923','20924','20925','20926','20927','20928','20929','20930','20931','20932','20933','20934','20935','20936','20937','20938','20939','20940','20941','20942','20943','20944','20945','20946','20947','20948','20949','20950','20951','20952','20953','20954','20955','20956','20957','20958','20959','20960','20961','20962','20963','20964','20965','20966','20967','20968','20969','20970','20971','20972','20973','20974','20975','20976','20977','20978','20979','20980','20981','20982','20983','20984','20985','20986','20987','20988','20989','20990','20991','20992','20993','20994','20995','20996','20997','20998','20999','21000','21001','21002','21003','21004','21005','21006','21007','21008','21009','21010','21011','21012','21013','21014','21015','21016','21017','21018','21019','21020','21021','21022','21023','21024','21025','21026','21027','21028','21029','21030','21031','21032','21033','21034','21035','21036','21037','21038','21039','21040','21041','21042','21043','21044','21045','21046','21047','21048','21049','21050','21051','21052','21053','21054','21055','21056','21057','21058','21059','21060','21061','21062','21063','21064','21065','21066','21067','21068','21069','21070','21071','21072','21073','21074','21075','21076','21077','21078','21079','21080','21081','21082','21083','21084','21085','21086','21087','21088','21089','21090','21091','21092','21093','21094','21095','21096','21097','21098','60101','60102','60103','60104','60105','60106','60107','60108','60109','60110','60111','60112','60113','60114','60115','60116','60117','60118','60119','60120','60121','60122','60123','60124','60125','60126','60127','21099','21100','21101','21102','21103','21104','21105','21106','21107','21108','21109','21110','21111','21112','21113','21114','21115','21116','51047','51048','12524','12525','12526','12527','12528','12529','12530','12531','12532','12533','12534','12535','12536','12537','12538','12539','12540','12541','12542','12543','12544','12545','12546','12547','12548','12549','12550','12551','12552','12553','12554','12555','12556','12557','12558','12559','12560','12561','12562','12563','12564','12565','12566','12567','12568','12569','12570','12571','12572','12573','40302','40303','40304','40305','40306','40307','40308','40309','40310','40311','40312','40313','40314','40315','40316','40317','30228','30229','21117','21118','21119','21120','12574','12575','12576','12577','12578','12579','12580','12581','12582','12583','12584','12585','12586','12587','12588','12589','12590','12591','12592','12593','12594','12595','12596','12597','12598','12599','12600','12601','12602','12603','12604','12605','12606','12607','12608','12609','12610','12611','12612','12613','12614','12615','12616','12617','12618','12619','12620','12621','12622','12623','12624','12625','12626','12627','12628','12629','12630','12631','12632','12633','12634','12635','12636','12637','12638','12639','12640','12641','12642','12643','12644','12645','12646','12647','12648','12649','12650','12651','12652','12653','12654','12655','12656','12657','12658','12659','12660','12661','12662','12663','12664','12665','12666','12667','12668','12669','12670','12671','12672','12673','12674','12675','12676','12677','12678','12679','12680','12681','12682','12683','12684','12685','12686','12687','12688','12689','12690','12691','12692','12693','12694','12695','12696','12697','12698','12699','12700','12701','12702','12703','12704','12705','12706','12707','12708','12709','12710','12711','12712','12713','12714','12715','12716','12717','12718','12719','12720','12721','12722','12723','12724','12725','12726','12727','12728','12729','12730','12731','12732','12733','12734','12735','12736','12737','12738','12739','12740','12741','12742','12743','12744','12745','12746','12747','12748','12749','12750','12751','12752','12753','12754','12755','12756','12757','12758','12759','12760','12761','12762','12763','12764','12765','12766','12767','12768','12769','12770','12771','12772','12773','12774','12775','12776','12777','12778','12779','12780','12781','12782','12783','12784','12785','12786','12787','12788','12789','12790','12791','12792','12793','12794','12795','12796','12797','12798','12799','12800','12801','12802','12803','12804','12805','12806','12807','12808','12809','12810','12811','12812','12813','12814','12815','12816','12817','12818','12819','12820','12821','12822','12823','12824','12825','12826','12827','12828','12829','12830','12831','12832','12833','12834','12835','12836','12837','12838','12839','12840','12841','12842','12843','12844','12845','12846','12847','12848','12849','12850','12851','12852','12853','12854','12855','12856','12857','12858','12859','12860','12861','12862','12863','12864','12865','12866','12867','12868','12869','12870','12871','12872','12873','12874','12875','12876','12877','12878','12879','12880','12881','12882','12883','12884','12885','12886','12887','12888','51049','60128','60129','30230','30231','30232','30233','12889','12890','12891','12892','12893','12894','12895','12896','51050','51051','51052','51053','51054','51055','51056','51057','51058','51059','51060','51061','51062','12897','12898','12899','12900','12901','12902','12903','12904','12905','12906','12907','12908','21121','21122','21123','21124','21125','21126','21127','21128','21129','21130','21131','21132','21133','21134','21135','21136','21137','21138','21139','21140','21141','21142','21143','21144','21145','21146','21147','21148','21149','21150','21151','21152','21153','21154','21155','21156','21157','21158','21159','21160','21161','21162','21163','21164','21165','21166','21167','21168','21169','21170','21171','21172','21173','21174','21175','21176','40318','40319','40320','40321','40322','40323','51063','51064','51065','51066','51067','51068','51069','51070','51071','51072','51073','51074','51075','51076','51077','30234','30235','30236','30237','30238','30239','30240','30241','30242','30243','30244','30245','30246','30247','30248','30249','30250','30251','30252','30253','30254','30255','30256','30257','30258','30259','30260','30261','30262','30263','30264','30265','30266','30267','30268','30269','30270','30271','30272','30273','30274','30275','30276','30277','30278','30279','30280','30281','30282','30283','30284','30285','30286','30287','30288','30289','30290','30291','30292','30293','30294','30295','30296','30297','30298','30299','30300','30301','30302','30303','30304','30305','30306','30307','30308','30309','30310','30311','30312','30313','30314','30315','30316','30317','30318','30319','30320','30321','30322','12909','12910','12911','12912','12913','12914','12915','12916','12917','12918','12919','12920','12921','12922','12923','12924','12925','12926','12927','12928','12929','12930','12931','12932','12933','12934','12935','12936','51078','51079','51080','51081','51082','51083','51084','51085','51086','51087','51088','51089','51090','51091','51092','51093','51094','40324','60130','60131','60132','60133','60134','60135','60136','60137','60138','60139','60140','60141','12937','12938','12939','12940','12941','12942','12943','12944','12945','12946','12947','12948','12949','12950','12951','12952','12953','12954','12955','12956','12957','12958','12959','12960','12961','12962','12963','12964','12965','12966','12967','12968','12969','12970','12971','12972','12973','12974','12975','12976','12977','12978','12979','12980','12981','12982','12983','12984','12985','12986','12987','12988','12989','12990','12991','12992','12993','12994','12995','12996','12997','12998','12999','13000','13001','13002','13003','13004','13005','13006','13007','13008','13009','13010','13011','13012','13013','13014','13015','13016','13017','13018','13019','13020','13021','13022','13023','13024','13025','13026','13027','13028','13029','13030','13031','13032','13033','13034','13035','13036','13037','13038','13039','13040','13041','13042','13043','13044','13045','13046','13047','13048','13049','13050','13051','13052','13053','13054','13055','13056','13057','13058','13059','13060','13061','13062','13063','13064','13065','13066','13067','13068','13069','13070','13071','13072','13073','13074','13075','13076','13077','13078','13079','13080','13081','13082','13083','13084','51095','51096','30323','30324','30325','40325','40326','40327','40328','40329','40330','40331','40332','13085','13086','13087','13088','13089','13090','13091','13092','13093','51097','30326','13094','21177','40333','40334','40335','40336','40337','40338','40339','40340','40341','13095','13096','13097','13098','13099','13100','13101','13102','60142','60143','60144','60145','60146','60147','60148','60149','60150','60151','60152','60153','60154','60155','60156','40342','40343','40344','40345','40346','40347','13103','13104','13105','13106','13107','40348','40349','40350','21178','21179','21180','21181','21182','21183','40351','40352','40353','40354','40355','40356','40357','40358','40359','40360','40361','40362','40363','40364','40365','30327','21184','21185','21186','21187','21188','21189','21190','21191','21192','21193','21194','21195','21196','21197','21198','21199','21200','21201','21202','21203','21204','21205','21206','21207','21208','21209','21210','21211','21212','21213','21214','21215','21216','21217','21218','21219','21220','21221','21222','21223','21224','21225','13108','13109','13110','13111','13112','13113','13114','13115','13116','13117','51098','51099','51100','51101','51102','51103','51104','51105','51106','51107','51108','51109','51110','51111','51112','51113','51114','51115','51116','51117','51118','51119','51120','51121','51122','51123','51124','51125','51126','51127','51128','51129','51130','51131','51132','51133','51134','51135','51136','51137','51138','51139','51140','51141','51142','51143','51144','51145','51146','51147','51148','51149','51150','51151','51152','51153','51154','51155','51156','51157','51158','51159','51160','51161','51162','51163','51164','51165','51166','51167','51168','51169','51170','51171','51172','51173','51174','51175','51176','51177','51178','51179','51180','51181','51182','51183','51184','51185','51186','51187','51188','51189','51190','51191','51192','51193','51194','51195','51196','51197','51198','51199','51200','51201','51202','51203','51204','51205','51206','51207','51208','51209','51210','51211','51212','51213','51214','51215','51216','51217','51218','51219','51220','51221','51222','51223','51224','51225','51226','51227','51228','51229','51230','51231','51232','51233','51234','51235','51236','51237','51238','51239','51240','51241','51242','51243','51244','51245','51246','51247','51248','51249','51250','51251','51252','51253','51254','51255','51256','51257','51258','51259','51260','51261','51262','51263','51264','51265','51266','51267','51268','51269','51270','51271','51272','51273','51274','51275','51276','51277','51278','51279','51280','51281','51282','51283','51284','51285','51286','51287','51288','51289','51290','51291','51292','51293','51294','51295','51296','51297','51298','51299','51300','51301','51302','51303','51304','51305','51306','51307','51308','51309','51310','51311','51312','51313','51314','51315','51316','51317','51318','51319','51320','51321','51322','51323','51324','51325','51326','51327','51328','51329','51330','51331','51332','51333','51334','51335','51336','51337','51338','51339','51340','51341','51342','51343','51344','51345','51346','51347','51348','51349','51350','51351','51352','51353','51354','51355','51356','51357','51358','51359','51360','51361','51362','51363','51364','51365','51366','51367','51368','51369','51370','51371','51372','51373','51374','51375','51376','51377','51378','51379','51380','51381','51382','51383','51384','51385','51386','51387','51388','51389','51390','51391','51392','51393','51394','51395','51396','51397','51398','51399','51400','51401','51402','51403','51404','51405','51406','51407','51408','51409','51410','51411','51412','51413','51414','51415','51416','51417','51418','51419','51420','51421','51422','51423','51424','51425','51426','51427','51428','51429','51430','51431','51432','51433','51434','51435','51436','51437','51438','51439','51440','51441','51442','51443','51444','51445','30328','30329','21226','21227','21228','21229','21230','21231','21232','21233','21234','21235','40366','40367','40368','40369','40370','40371','40372','40373','40374','13118','13119','13120','13121','13122','13123','13124','13125','13126','13127','13128','13129','13130','13131','13132','13133','13134','13135','13136','13137','13138','13139','13140','13141','13142','13143','13144','13145','13146','13147','13148','13149','13150','13151','13152','40375','40376','40377','40378','40379','60157','60158','13153','13154','13155','13156','13157','13158','13159','13160','13161','13162','13163','13164','13165','13166','13167','13168','13169','13170','13171','13172','13173','13174','13175','13176','13177','13178','13179','13180','13181','13182','13183','13184','13185','13186','13187','13188','13189','13190','13191','13192','13193','13194','13195','13196','13197','13198','13199','13200','13201','13202','13203','13204','13205','13206','13207','13208','13209','13210','13211','13212','13213','13214','13215','13216','13217','13218','13219','13220','13221','13222','13223','13224','13225','13226','13227','13228','13229','13230','13231','13232','13233','13234','13235','13236','13237','13238','13239','13240','13241','13242','13243','13244','13245','13246','13247','13248','13249','13250','13251','13252','13253','13254','13255','13256','13257','13258','13259','13260','13261','13262','13263','13264','13265','13266','13267','13268','13269','13270','13271','13272','13273','13274','13275','13276','13277','13278','13279','13280','13281','13282','13283','13284','13285','13286','13287','13288','13289','13290','13291','13292','13293','13294','13295','13296','13297','13298','13299','13300','13301','51446','51447','13302','13303','13304','13305','13306','13307','13308','13309','13310','13311','13312','13313','13314','13315','13316','13317','13318','13319','13320','13321','13322','13323','13324','13325','13326','13327','13328','13329','13330','13331','13332','13333','13334','13335','13336','13337','13338','13339','13340','13341','13342','13343','13344','13345','13346','13347','13348','13349','13350','13351','13352','13353','13354','13355','13356','13357','13358','13359','13360','13361','13362','13363','13364','13365','13366','51448','40380','40381','40382','40383','40384','40385','40386','40387','40388','40389','40390','40391','40392','40393','40394','40395','40396','40397','40398','40399','40400','21236','21237','21238','21239','21240','21241','21242','21243','21244','21245','21246','21247','21248','21249','21250','21251','21252','21253','21254','21255','21256','21257','21258','21259','21260','21261','21262','21263','21264','21265','21266','21267','21268','51449','51450','51451','51452','51453','51454','51455','51456','51457','51458','51459','51460','13367','13368','13369','13370','13371','13372','13373','13374','13375','13376','13377','13378','13379','13380','13381','13382','13383','21269','21270','21271','21272','21273','21274','21275','21276','21277','21278','21279','21280','40401','40402','40403','13384','13385','13386','13387','13388','13389','13390','13391','13392','13393','13394','13395','13396','13397','13398','13399','13400','13401','13402','13403','13404','13405','13406','13407','13408','13409','13410','13411','13412','13413','13414','13415','13416','13417','13418','13419','13420','13421','13422','13423','13424','13425','13426','13427','13428','13429','13430','13431','13432','13433','13434','13435','13436','13437','13438','13439','13440','13441','13442','13443','13444','13445','13446','13447','13448','13449','13450','13451','13452','13453','13454','13455','13456','13457','13458','13459','13460','13461','13462','13463','13464','13465','13466','13467','13468','13469','13470','13471','13472','13473','13474','13475','13476','13477','13478','13479','13480','13481','13482','13483','13484','40404','40405','40406','40407','40408','40409','40410','40411','40412','40413','40414','40415','40416','40417','40418','40419','40420','40421','40422','40423','40424','40425','21281','21282','21283','21284','21285','21286','21287','21288','21289','21290','21291','21292','21293','21294','21295','21296','21297','21298','21299','21300','21301','21302','21303','21304','21305','21306','21307','21308','21309','21310','21311','21312','21313','21314','13485','13486','13487','13488','13489','13490','13491','13492','13493','21315','21316','21317','21318','21319','21320','21321','21322','21323','21324','40426','40427','40428','40429','40430','40431','40432','40433','40434','40435','40436','13494','13495','13496','21325','21326','21327','21328','21329','60159','60160','60161','60162','60163','60164','60165','60166','60167','60168','60169','60170','60171','60172','60173','60174','60175','60176','40437','21330','21331','21332','21333','21334','21335','21336','21337','21338','21339','51461','21340','21341','21342','21343','21344','21345','21346','21347','21348','21349','21350','21351','13497','13498','21352','21353','21354','21355','21356','21357','21358','21359','21360','40438','40439','40440','40441','40442','40443','40444','40445','51462','51463','21361','21362','21363','21364','21365','21366','21367','21368','21369','21370','21371','21372','21373','21374','21375','21376','21377','21378','21379','21380','21381','21382','21383','21384','21385','21386','21387','21388','21389','21390','21391','21392','21393','21394','21395','21396','21397','21398','21399','21400','21401','21402','21403','21404','21405','21406','21407','21408','21409','21410','21411','21412','21413','21414','21415','21416','21417','21418','21419','21420','21421','21422','21423','21424','21425','21426','21427','21428','21429','21430','21431','21432','21433','21434','21435','21436','21437','21438','21439','21440','21441','21442','21443','21444','21445','21446','21447','21448','21449','21450','21451','21452','21453','21454','21455','21456','21457','21458','21459','21460','30330','30331','30332','13499','13500','13501','13502','13503','13504','13505','40446','40447','40448','40449','40450','40451','40452','40453','40454','40455','40456','40457','40458','40459','40460','40461','40462','40463','40464','40465','40466','40467','40468','40469','40470','40471','40472','40473','40474','40475','21461','21462','21463','21464','21465','21466','21467','21468','21469','13506','13507','13508','13509','13510','13511','13512')"):
for json_name in json_name_list:
data_dict[json_name] = line[json_name]
for qyer_name in qyer_list:
data = json.loads(line[qyer_name])
if 'qyer' in data:
data_dict[qyer_name] = data['qyer'] or ''
elif 'daodao' in data:
data_dict[qyer_name] = data['daodao'] or ''
else:
if len(data.values()) == 0:
data_dict[qyer_name] = ''
else:
data_dict[qyer_name] = list(data.values())[0] or ''
for daodao_name in daodao_list:
data = json.loads(line[daodao_name])
if 'daodao' in data:
data_dict[daodao_name] = data['daodao'] or ''
elif 'qyer' in data:
data_dict[daodao_name] = data['qyer'] or ''
else:
if len(data.values()) == 0:
data_dict[daodao_name] = ''
else:
data_dict[daodao_name] = list(data.values())[0] or ''
# 特殊字段
miaoji_id = line['id']
data_source = line['data_source']
city_id = line['city_id']
city = line['city']
alias = '|'.join(filter(lambda x: x != data_dict['name'] and x != data_dict['name_en'],
set(list(json.loads(line['name']).values()) +
list(json.loads(line['name_en']).values()))
)
)
ori_grade = line['grade']
sql = 'insert ignore into attr_merge.attraction_tmp(`id`,`name`,`name_en`,`data_source`,`city_id`,`city`,`map_info`,`address`,`star`,`plantocount`,`beentocount`,`real_ranking`,`grade`,`commentcount`,`tagid`,`url`,`website_url`,`phone`,`introduction`,`open_desc`,`recommend_lv`,`visit_time`,`prize`,`traveler_choice`, `alias`, `image`, `ori_grade`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (
miaoji_id, data_dict['name'], data_dict['name_en'], data_source, city_id, city,
data_dict['map_info'], data_dict['address'],
data_dict['star'], data_dict['plantocount'], data_dict['beentocount'],
data_dict['real_ranking'], data_dict['grade'],
data_dict['commentcount'],
data_dict['tagid'], data_dict['url'], data_dict['website_url'], data_dict['phone'],
data_dict['introduction'],
data_dict['open_desc'], data_dict['recommend_lv'], data_dict['visit_time'], data_dict['prize'],
data_dict['traveler_choice'], alias, data_dict['image_list'], ori_grade)
db.ExecuteSQL(sql, data)
def get_poi_info():
writer = csv.writer(
open('/tmp/error_distance_poi_{0}.csv'.format(TYPE), 'w'))
writer_city = csv.writer(
open('/tmp/error_distance_city_{0}.csv'.format(TYPE), 'w'))
writer_reverse = csv.writer(
open('/tmp/error_distance_reverse_{0}.csv'.format(TYPE), 'w'))
writer_city_without_map_info = csv.writer(
open('/tmp/city_without_map_info_{0}.csv'.format(TYPE), 'w'))
writer.writerow(
['poi_id', 'poi_lat_lng', 'city_id', 'city_lat_lng', 'distance'])
writer_city.writerow(['city_id', 'city_name', 'count', 'total', 'percent'])
writer_reverse.writerow(
['poi_id', 'poi_map_info', 'city_id', 'city_map_info'])
writer_city_without_map_info.writerow(['city_id', 'city_name', 'map_info'])
city_id_map_info_dict, city_id_name_dict = get_city_id_map_info_name_dict()
sql = 'select city_id,count(*) as total,group_concat(id,"%%%",map_info separator "###") as value from {0} group by city_id'.format(
POI_TABLE)
for line in db.QueryBySQL(sql):
city_id = line['city_id']
total = line['total']
error_count = 0
values = line['value']
if city_id not in city_id_map_info_dict:
continue
city_map_info = city_id_map_info_dict[city_id]
city_name = city_id_name_dict[city_id]
try:
c_lat, c_lon = city_map_info.split(',')
except:
writer_city_without_map_info.writerow(
[city_id, city_name, city_map_info])
continue
for value in values.split('###'):
poi_id, poi_map_info = value.replace(' ', '').split('%%%')
if poi_map_info == '':
continue
poi_lat, poi_lon = poi_map_info.split(',')
distance = get_distance(c_lon, c_lat, poi_lon, poi_lat)
if distance > DISTANCE:
if get_distance(c_lon, c_lat, poi_lat, poi_lon) < DISTANCE:
writer_reverse.writerow(
[poi_id, poi_map_info, city_id, city_map_info])
else:
writer.writerow([
poi_id, ','.join(poi_map_info.split(',')[::-1]),
city_id, ','.join(city_map_info.split(',')[::-1]),
distance
])
error_count += 1
writer_city.writerow(
("%s %s %s %s %.02f%%" %
(city_id, city_name, error_count, total,
100 * float(error_count) / float(total))).split(' '))
def get_mid_set():
_set = set()
if S_TYPE == 'attr':
sql = 'select DISTINCT miaoji_id from attr_comment_1222 where miaoji_id like "v%"'
elif S_TYPE == 'rest':
sql = 'select DISTINCT miaoji_id from attr_comment_1222 where miaoji_id like "r%"'
elif S_TYPE == 'shop':
sql = 'select DISTINCT miaoji_id from attr_comment_1222 where miaoji_id like "sh%"'
else:
raise TypeError(S_TYPE)
for line in db_localhost.QueryBySQL(sql=sql):
_set.add(line['miaoji_id'])
return _set
def get_task(s_type):
if s_type == 'attr':
TABLE = ATTR_TABLE
elif s_type == 'rest':
TABLE = REST_TABLE
elif s_type == 'shop':
TABLE = SHOP_TABLE
else:
raise TypeError()
sql = 'select id from {0} where map_info="" or map_info="NULL"'.format(
TABLE)
for line in db.QueryBySQL(sql):
yield line['id']
def get_task():
sql = 'select id,name,name_en,res_url,city_id,website_url from poi.restaurant_tmp'
datas = []
for line in db.QueryBySQL(sql):
url = line['res_url']
miaoji_id = line['id']
name = line['name']
name_en = line['name_en']
source = 'daodao'
source_id = d_pattern.findall(url)[0]
site = line['website_url']
city_id = line['city_id']
data = (miaoji_id, name, name_en, source, source_id, site, city_id)
datas.append(data)
return insert_db(datas)
def get_img_url(source_id_set, s_type):
if s_type == 'attr':
sql = 'select id,imgurl from tp_attr_basic_0801 where id in ({0})'.format(
','.join(["\"" + x + "\"" for x in source_id_set]))
elif s_type == 'shop':
sql = 'select id,imgurl from tp_shop_basic_0801 where id in ({0})'.format(
','.join(["\"" + x + "\"" for x in source_id_set]))
elif s_type == 'rest':
sql = 'select id,image_urls from tp_rest_basic_0801 where id in ({0})'.format(
','.join(["\"" + x + "\"" for x in source_id_set]))
else:
raise Exception("Error Type")
for line in db_localhost.QueryBySQL(sql):
if s_type in ['attr', 'shop']:
for url in line['imgurl'].split('|'):
if url:
yield url, line['id']
elif s_type == 'rest':
for url in line['image_urls'].split('|'):
if url:
yield url, line['id']
def get_tasks():
url_source_id = get_source_id(get_urls())
datas = []
for line in db.QueryBySQL(
'select id,name,name_en,city_id,city,country,website_url,url from poi.shopping_tmp'
):
# get source_id by url
url = json.loads(line['url'])['daodao']
miaoji_id = line['id']
name = line['name']
name_en = line['name_en']
source = 'daodao'
source_id = url_source_id[url]
site = line['website_url']
city_id = line['city_id']
city_name = line['city']
country = line['country']
data = (miaoji_id, name, name_en, source, source_id, site, city_id,
city_name, country)
datas.append(data)
print(insert_db(datas))
def task():
name_dict = {}
en_dict = {}
site_dict = {}
sql = 'select id,name,name_en,website_url from chat_shopping'
for line in db_114_35_shop.QueryBySQL(sql):
miaoji_id = line['id']
name = line['name']
name_en = line['name_en']
site = get_modify_url(line['website_url'])
site_dict[site] = miaoji_id
name_dict[name] = miaoji_id
en_dict[get_similar_word(name_en)] = miaoji_id
rows = []
for line in db.QueryBySQL('select id,name,name_en,site from qyer_outlets'):
source_id = line['id']
name = line['name']
name_en = get_similar_word(line['name_en'])
site = get_modify_url(line['site'])
if (name in name_dict) and line['name'] != '':
rows.append((source_id, line['name'], line['name_en'],
line['site'], 'name', name, name_dict.get(name, '')))
elif (name_en in en_dict) and line['name_en'] != '':
rows.append(
(source_id, line['name'], line['name_en'], line['site'],
'name_en', name_en, en_dict.get(name_en, '')))
elif (site in site_dict) and line['site'] != '':
rows.append((source_id, line['name'], line['name_en'],
line['site'], 'site', site, site_dict.get(site, '')))
import csv
f = open('/tmp/outlets.csv', 'w')
writer = csv.writer(f)
writer.writerow(['qyer_id', '名称', '英文名', '官网', '匹配条件', '匹配项', 'ID'])
for i in set(rows):
writer.writerow(i)
def get_task():
sql = 'select url,id,type from switzerland where introduction1="" and introduction2="" and image_list="" and contact_info=""'
for line in db_localhost.QueryBySQL(sql):
yield line['url'], line['id'], line['type']
def get_task():
sql = 'select distinct city_id from rest_merge.rest_unid'
for line in db.QueryBySQL(sql):
city_id = line['city_id']
name, country = get_city_info(city_id)
print(update_db(country, name, city_id))
def already_crawl():
mid_set = set()
sql = 'select mid from image_recrawl'
for line in db_localhost.QueryBySQL(sql):
mid_set.add(line['mid'])
return mid_set
def get_yelp_task():
sql = 'select id,res_url from price_level.chat_restaurant where price_level="" and source="yelp"'
for line in db_localhost.QueryBySQL(sql):
yield line['id'], line['res_url'].strip()
def get_ready_id():
id_set = set()
sql = 'select distinct miaoji_id from attr_comment_1018'
for line in db_localhost.QueryBySQL(sql):
id_set.add(line['miaoji_id'])
return id_set
def get_daodao_task():
# sql = 'select id,res_url from price_level.chat_restaurant where price_level="" and source="daodao" and city_id in ("50573","50554","20310","50163","50161","50160","50167","50165","50164","50642","50643","50169","50168","50646","50647","50644","50645","50707","50720","50800","50432","50777","50425","50802","50490","50361","50803","50727","50360","50804","50726","50773","50440","50806","50441","50807","50497","50442","50705","50443","50444","50445","50264","50265","50266","50267","50260","50261","50262","50263","50347","50346","50345","50344","50268","50269","50341","50340","50674","50831","50436","50495","50840","50703","50830","50719","50864","50862","50863","50860","50861","50192","50190","50191","50196","50197","50195","50198","50199","50462","50358","50460","50461","50466","50467","50464","50359","50468","50469","50299","50298","50711","50291","50290","50293","50292","50295","50294","50297","50296","50713","50714","50715","50716","50717","50835","50127","50126","50125","50124","50123","50122","50121","50120","50686","50687","50684","50685","50682","50648","50129","50128","50649","50048","50049","50518","50519","50044","50045","50046","50047","50040","50041","50042","50043","50480","50841","50837","50640","50465","50641","50852","50633","50632","50631","50630","50637","50636","50635","50634","50639","50638","50696","50228","50229","50839","50595","50220","50221","50692","50226","50227","20312","20258","20259","20252","20250","20251","20256","50118","20254","20255","50119","50509","50508","50834","20311","50314","50315","50316","50317","50310","50311","50099","50098","50097","50095","50094","50093","50059","50091","50090","50111","50259","50258","50709","50708","50255","50254","50257","50256","50251","50250","50253","50252","50503","50502","50488","50489","20179","20178","20177","50485","20175","20174","20173","20172","20171","20170","50858","50552","50001","50002","50003","50004","50005","50006","50007","50008","50009","50558","50559","50381","50790","50791","50419","50380","50794","50795","50796","50797","50413","50412","50411","50410","50417","50416","50415","50414","50850","50842","50433","50843","50815","50801","50434","20257","50544","50389","50677","50676","50675","50388","50673","50569","50671","50670","50431","50568","50679","50430","50075","50074","50077","50076","50178","50070","50073","50072","50174","50175","50176","50177","50079","50078","50172","50563","50562","50793","20298","20299","20296","50579","20294","20292","20293","50211","50210","50213","50215","50214","50217","50216","50350","50351","50352","50353","50354","50355","50356","50357","50805","50814","50457","50324","50455","50859","50454","50312","50853","50453","50851","50313","50857","50856","50855","50452","50593","50844","50451","50322","50329","50328","50459","50458","50325","50318","50327","50326","50321","50320","50323","50092","50845","50288","50289","50282","50283","50280","50281","50286","50287","50284","50285","50846","50838","50130","50131","50132","50133","50134","50135","50136","50137","50138","50139","50847","50039","50038","50529","50528","50706","50031","50030","50033","50032","50035","50034","50037","50036","50704","50349","50571","50348","50702","50701","50700","50659","50658","50343","50342","50145","50144","50147","50146","50141","50140","50143","50142","50624","50625","50626","50627","50149","50148","50622","50623","50578","20267","20266","20265","20264","20263","20262","20261","20260","20269","20268","20308","20309","20304","20305","20306","20307","20300","20301","20302","20303","50657","50656","50517","50088","50089","50363","50362","50365","50364","50367","50366","50080","50081","50082","50083","50084","50085","50086","50087","50718","50109","50248","50249","50246","50247","50244","50245","50242","50243","50240","50241","50484","20176","50486","20168","20169","50108","20160","20161","20162","20163","20164","20165","20166","20167","50482","50483","50404","50405","50406","50407","50400","50401","50402","50403","50567","50566","50565","50564","50408","50409","50561","50560","50769","50768","50819","50818","50817","50816","50767","50106","50813","50812","50811","50810","50585","50584","50587","50586","50581","50580","50583","50582","20242","50589","50588","50745","50849","50836","20246","50660","50661","50662","50663","50664","50665","50666","50667","50668","50669","50744","50066","50067","50064","50065","50062","50063","50060","50061","50101","50100","50103","50102","50105","50104","50068","50069","50504","50553","50526","50550","50848","50523","50551","50522","50556","50521","50557","20184","50520","20182","20183","20180","20181","50527","50555","50107","50615","50614","50617","50616","50611","50525","50613","50612","50524","50619","50618","50514","50450","50202","50203","50200","50201","50206","50207","50204","50205","50209","50729","50823","50610","50418","50826","50782","50728","50824","50463","50825","50798","50446","50799","50781","50828","50448","50449","50723","50829","50725","50724","50338","50339","50336","50337","50334","50335","50332","50333","50330","50331","50447","50780","20151","20150","20153","20152","20155","20154","20157","20156","20159","20158","50854","50534","50535","50536","50537","50530","50531","50532","50533","50423","50538","50539","50383","50382","50028","50029","50387","50386","50385","50384","50435","50023","50020","50021","50026","50027","50024","50025","50420","50730","50743","50731","50710","50421","50747","50620","50506","50746","50156","50157","50154","50155","50153","50150","50151","50651","50650","50653","50652","50655","50654","50158","50159","50621","20270","50712","20072","20073","20070","20071","50822","50628","50629","50789","50505","50179","50372","50373","50370","50371","50376","50377","50374","50375","50378","50379","50672","50279","50278","50788","50273","50272","50271","50270","50277","50276","50275","50274","50820","50493","50792","50492","50507","50491","50181","50180","50183","50182","50185","50184","50187","50186","50188","50678","50496","50471","50470","50473","50472","50475","50474","50477","50476","50479","50478","50572","50494","50574","50575","50576","50577","50808","50809","50778","50779","50776","50071","50774","50775","50772","50499","50770","50771","50596","50597","50594","50498","50592","50501","50590","50591","50827","50598","50599","50487","50500","50699","50688","50695","50694","50697","50689","50691","50690","50693","50171","50053","50052","50051","50050","50057","50056","50055","50054","50112","50113","50110","50058","50116","50117","50114","50115","50439","50683","50698","50680","50681","50481","50545","50438","50456","50606","50607","50604","50605","50602","50603","50600","50601","50833","50608","50609","50541","50540","50543","50319","50516","50542","50239","50238","50237","50236","50235","50233","50232","50231","50230","20249","20248","50515","20241","50512","20243","50546","20245","20244","20247","50513","50510","50369","50548","50821","50511","50368","20297","50732","50733","50309","50308","50786","50303","50302","50301","50300","50307","50306","50305","50304","50832","50783","50437","20142","20143","20140","20141","20146","20147","20144","20145","20148","20149","50426","50427","50424","50017","50016","50015","50014","50013","50012","50547","50010","50549","50422","50019","50018","50787","50570","50785","50784","50398","50399","50428","50429","50394","50395","50396","50397","50390","50391","50392","50393")'
sql = 'select id,res_url from price_level.chat_restaurant where price_level="" and source="daodao"'
for line in db_localhost.QueryBySQL(sql):
yield line['id'], line['res_url'].strip()
def get_urls():
urls = []
sql = 'select url from poi.shopping_tmp'
for line in db.QueryBySQL(sql):
urls.append(json.loads(line['url'])['daodao'])
return urls
def get_task():
sql = 'select map_info,id from shop_merge.shopping_tmp'
for line in db_localhost.QueryBySQL(sql):
yield line['map_info'], line['id']
def get_city_id():
city_id = []
for line in db.QueryBySQL('select distinct city_id from poi.shopping_tmp'):
city_id.append(line['city_id'])
return ','.join(["\"" + x + "\"" for x in city_id])
def get_task_dict():
id_img_list_dict = defaultdict(list)
sql = 'select mid,img_list from image_recrawl'
for line in db_localhost.QueryBySQL(sql):
id_img_list_dict[line['mid']].extend(line['img_list'].split('|'))
return id_img_list_dict
def get_no_poi_set():
_set = set()
sql = 'select distinct city_id from data_prepare.attraction_tmp'
for line in db_localhost.QueryBySQL(sql):
_set.add(line['city_id'])
return _set
def finish_set():
qyer_set = set()
sql = 'select url from qyer_img'
for line in db_localhost.QueryBySQL(sql):
qyer_set.add(line['url'])
return qyer_set
def get_max_id():
id_set = set()
for line in db.QueryBySQL('select id from shop_merge.shop_unid'):
id_set.add(int(line['id'][2:]))
return 'sh' + str(max(id_set))
