run4.py 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674
  1. # -*- coding: utf-8 -*-
  2. #from selenium import webdriver
  3. #from tkinter.tix import TEXT
  4. from seleniumwire import webdriver
  5. from selenium.webdriver.common.action_chains import ActionChains
  6. from selenium.webdriver.common.keys import Keys
  7. from selenium.webdriver.support import expected_conditions as EC
  8. from selenium.webdriver.support.wait import WebDriverWait
  9. from selenium.webdriver.common.by import By
  10. from selenium.common.exceptions import TimeoutException
  11. from selenium.common.exceptions import WebDriverException
  12. import selenium
  13. import traceback
  14. from bs4 import BeautifulSoup
  15. import gzip
  16. from utility import database_access as DA
  17. from utility.parseutils import *
  18. from utility.connect import *
  19. import redis
  20. from datetime import datetime
  21. from requests import session
  22. import pandas as pd
  23. import dataset
  24. import time
  25. import json
  26. import re
  27. import sys, os
  28. import socket
  29. import brotli
  30. import pickle
  31. from selenium.webdriver.common.desired_capabilities import DesiredCapabilities
  32. import urllib.parse
  33. chrome_window=False
  34. globalkw=None
  35. proxyport=8787
  36. def write_to_file(jsobj,fname):
  37. with open(fname, 'wb') as handle:
  38. pickle.dump(jsobj, handle, protocol=pickle.HIGHEST_PROTOCOL)
  39. def build_cache(db):
  40. global reviews_table
  41. id_dict={}
  42. cursor = db.query('SELECT fid FROM google_poi.shop_list3;')
  43. for c in cursor:
  44. key = '{}'.format(c['fid'])
  45. id_dict[key]=1
  46. return id_dict
  47. #def brower_start(port):
  48. # global proxyport
  49. # global chrome_window
  50. # print(proxyport)
  51. # options = webdriver.ChromeOptions()
  52. # if chrome_window:
  53. # browser = webdriver.Chrome(
  54. # desired_capabilities=options.to_capabilities()
  55. # )
  56. # else:
  57. # chrome_options = webdriver.ChromeOptions()
  58. # chrome_options.add_argument('--proxy-server=host.docker.internal:'+str(proxyport)) # Specify your Kubernetes service-name here
  59. # chrome_options.add_argument('--ignore-certificate-errors')
  60. # chrome_options.add_argument("--no-sandbox")
  61. # chrome_options.add_argument("--disable-dev-shm-usage")
  62. # browser = webdriver.Remote(
  63. # command_executor='http://127.0.0.1:'+str(port)+'/wd/hub',
  64. # desired_capabilities=chrome_options.to_capabilities(),
  65. # seleniumwire_options={'addr':'0.0.0.0','port':proxyport,'auto_config': False}
  66. # )
  67. # browser.set_window_size(1400,1000)
  68. # return browser
  69. def brower_start(port):
  70. options = webdriver.ChromeOptions()
  71. # browser = webdriver.Chrome(options=options)
  72. options.add_argument('--ignore-certificate-errors')
  73. options.add_argument("--no-sandbox")
  74. options.add_argument("--headless")
  75. options.add_argument("--disable-gpu")
  76. options.add_argument("--disable-dev-shm-usage")
  77. browser = webdriver.Chrome(options=options)
  78. browser.set_window_size(1400,1000)
  79. # browser = webdriver.Remote(
  80. # command_executor='http://127.0.0.1:'+str(port)+'/wd/hub',
  81. # # command_executor='http://192.53.174.202:'+str(port)+'/wd/hub',
  82. # desired_capabilities=options.to_capabilities()
  83. # )
  84. return browser
  85. def get_next_job(db):
  86. result = {}
  87. # result = db.query('select * from error_list2 where check_=0 ORDER BY RAND() limit 100')
  88. result = db.query('SELECT * FROM swire_store_list a WHERE not exists (select 1 from error_list2 tei where tei.fid = a.fid limit 1 ) ORDER BY RAND() limit 30')
  89. url_pd = pd.DataFrame([dict(i) for i in result])
  90. url_pd['item_url'] = url_pd['place_id'].apply(lambda x: 'https://www.google.com/maps/place/?q=place_id:{}'.format(x) )
  91. # url_pd['fid'] = url_pd['item_url'].apply(lambda x: x.split('data=')[1].split('!')[3])
  92. # url_pd['item_url'] = url_pd['fid'].apply(lambda x: 'https://www.google.com.tw/maps/@24.1753633,120.6747136,15z/data=!4m5!3m4!1s{}!8m2!3d24.1760271!4d120.6705323'.format(x))
  93. remove = db.query('select item_url from error_list3')
  94. remove = pd.DataFrame([dict(i) for i in remove])
  95. if len(remove) != 0:
  96. remove_fid_list = remove['item_url'].to_list()
  97. url_pd = url_pd[~url_pd['item_url'].isin(remove_fid_list)]
  98. return url_pd
  99. def parsing_js(resp):
  100. txt = json.loads(resp[5::])
  101. output = {}
  102. output['name'] = txt[6][11]
  103. output['adress_name'] = txt[6][18]
  104. if txt[6][4]:
  105. if txt[6][4][7]:
  106. output['rating'] = str(txt[6][4][7])
  107. else:
  108. output['rating'] = None
  109. if txt[6][4][8]:
  110. output['user_ratings_total'] = str(txt[6][4][8])
  111. else:
  112. output['user_ratings_total'] = None
  113. if txt[6][4][2]:
  114. output['price_level'] = str(len(['$' for i in txt[6][4][2] if i == '$']))
  115. else:
  116. output['price_level'] = None
  117. else:
  118. output['rating'] = None
  119. output['user_ratings_total'] = None
  120. output['price_level'] = None
  121. if txt[6][37][0]:
  122. output['lon'] = txt[6][37][0][0][8][0][1]
  123. output['lat'] = txt[6][37][0][0][8][0][2]
  124. else:
  125. output['lon'] = None
  126. output['lat'] = None
  127. if txt[6][178]:
  128. output['tel'] = txt[6][178][0][0]
  129. else:
  130. output['tel'] = ''
  131. if txt[6][13]:
  132. output['category'] = txt[6][13][0]
  133. else:
  134. output['category'] = ''
  135. try:
  136. location = txt[6][183][2][2][0]
  137. if location:
  138. location_s = location.split(' ')
  139. output['city'], output['area'] = location_s[-1], location_s[-2]
  140. else:
  141. output['city'], output['area'] = '', ''
  142. except:
  143. output['city'], output['area'] = '', ''
  144. if txt[6][100]:
  145. for item in txt[6][100][1]:
  146. name = item[1]
  147. if name not in intro_list.keys(): continue
  148. name_map = intro_list[name]
  149. c = 0
  150. detail = []
  151. for t in item[2]:
  152. value = t[1]
  153. if t[3] == 1:
  154. detail += [{'id':c, name_map[1]:'不提供'+str(value)}]
  155. else:
  156. detail += [{'id':c, name_map[1]:value}]
  157. c += 1
  158. output[name_map[0]] = str(detail)
  159. for key in intro_list:
  160. if intro_list[key][0] not in output.keys():
  161. output[intro_list[key][0]] = '[]'
  162. if txt[6][34]:
  163. output = time_parsing_js(txt[6][34], output)
  164. else:
  165. output['open_now'] = 'False'
  166. output['periods'] = ''
  167. output['weekday_text'] = ''
  168. output['time_status'] = ''
  169. if txt[6][72]:
  170. output['header_image'] = txt[6][72][0][0][6][0]
  171. else:
  172. output['header_image'] = ''
  173. if txt[6][126]:
  174. output['google_url'] = txt[6][126][4]
  175. ludocid_str = [i for i in txt[6][126][4].split('&') if i.find('ludocid') != -1]
  176. if len(ludocid_str) != 0:
  177. ludocid = ludocid_str[0].split('=')[-1]
  178. output['ludocid'] = ludocid
  179. else:
  180. output['google_url'] = ''
  181. # write_to_file(orig,'debug.pickle')
  182. return output
  183. def time_parsing_js(time_json, output):
  184. weekday_text = []
  185. periods = []
  186. if time_json is None:
  187. output['open_now'] = 'False'
  188. output['periods'] = ''
  189. output['weekday_text'] = ''
  190. output['time_status'] = ''
  191. return output
  192. if time_json[1] is None:
  193. output['open_now'] = 'False'
  194. output['periods'] = ''
  195. output['weekday_text'] = ''
  196. output['time_status'] = ''
  197. return output
  198. for time_ in time_json[1]:
  199. week = time_[0]
  200. weekday_text += ['{}: {}'.format(week, ', '.join(time_[1]))]
  201. for t in time_[1]:
  202. if t == '24 小時營業':
  203. periods += [{
  204. "open":{
  205. "day": week_list[week],
  206. "time": '0000'
  207. },
  208. "close":{
  209. "day": week_list[week],
  210. "time": ''
  211. }
  212. }]
  213. elif t == '休息':
  214. periods += [{
  215. "open":{
  216. "day": week_list[week],
  217. "time": ''
  218. },
  219. "close":{
  220. "day": week_list[week],
  221. "time": ''
  222. }
  223. }]
  224. else:
  225. start, end = t.split('–')
  226. end_hour, end_min = end.split(':')
  227. start_hour, start_min = start.split(':')
  228. if end_hour < start_hour:
  229. end_day = week_list[week] + 1
  230. else:
  231. end_day = week_list[week]
  232. periods += [{
  233. "open":{
  234. "day": week_list[week],
  235. "time": start.replace(':','')
  236. },
  237. "close":{
  238. "day": end_day,
  239. "time": end.replace(':','')
  240. }
  241. }]
  242. output['periods'] = str(periods)
  243. output['weekday_text'] = str(weekday_text)
  244. output['time_status'] = blank_check(time_json[4][4].split('⋅')[0])
  245. if output['time_status'].find('永久停業') != -1 or\
  246. output['time_status'].find('暫時關閉') != -1 or\
  247. output['time_status'].find('暫停營業') != -1:
  248. output['open_now'] = 'False'
  249. else:
  250. output['open_now'] = 'True'
  251. return output
  252. def save_js_to_db(jsobj, fid):
  253. global shop_table
  254. global iddict
  255. jsobj['fid'] = fid
  256. if iddict.get(fid) is None:
  257. try:
  258. shop_table.insert(jsobj)
  259. except:
  260. traceback.print_exc()
  261. def process_web_request_start(driver, fid):
  262. time.sleep(3)
  263. print("start&**********************")
  264. for request in driver.requests:
  265. if request.response:
  266. # print(request.url)
  267. if 'place?' in request.url :
  268. # print('parsing js:')
  269. front, _ = fid.split(':')
  270. if request.url.find(front) != -1:
  271. print(request.url)
  272. # resp = brotli.decompress(request.response.body)
  273. resp=request.response.body
  274. if 'gzip' in request.response.headers.get('Content-Encoding'):
  275. resp = gzip.decompress(request.response.body)
  276. if 'br' in request.response.headers.get('Content-Encoding'):
  277. resp = brotli.decompress(request.response.body)
  278. # resp = brotli.decompress(request.response.body)
  279. jstext = resp.decode('utf-8')
  280. output = parsing_js(jstext)
  281. time.sleep(1)
  282. return output
  283. return 0
  284. def reviews_parsing_js(resp):
  285. columns_name = ['id','author_page','author_name', 'profile_photo_url', 'author_review_count',
  286. 'created_at', 'text', 'photos', 'rating', 'store_review_time','store_review']
  287. jsobj = json.loads(resp[5::])
  288. result = []
  289. for i in range(len(jsobj[2])):
  290. tmp = []
  291. tmp += [jsobj[2][i][6], jsobj[2][i][0][0], jsobj[2][i][0][1], jsobj[2][i][0][2], jsobj[2][i][12][1][1]]
  292. tmp += [jsobj[2][i][1], jsobj[2][i][3]]
  293. # image
  294. image = []
  295. if jsobj[2][i][14]:
  296. for j in range(len(jsobj[2][i][14])):
  297. image += [jsobj[2][i][14][j][6][0]]
  298. tmp += [image]
  299. #rating
  300. tmp += [jsobj[2][i][4]]
  301. # store reply
  302. if jsobj[2][i][9]:
  303. tmp += [jsobj[2][i][9][0], jsobj[2][i][9][1]]
  304. else:
  305. tmp += ['', '']
  306. result.append(list(map(lambda x, y: {x:y}, columns_name, tmp)))
  307. return result
  308. def process_web_request_reviews(driver, output, ludocid):
  309. time.sleep(3)
  310. print("reviews&**********************")
  311. for request in driver.requests:
  312. if request.response:
  313. # print(request.url)
  314. if 'listentitiesreviews?' in request.url :
  315. # print('parsing js:')
  316. if request.url.find(ludocid) != -1:
  317. print(request.url)
  318. # resp = brotli.decompress(request.response.body)
  319. # jstext = resp.decode('utf-8')
  320. # result = reviews_parsing_js(jstext)
  321. # resp = brotli.decompress(request.response.body)
  322. resp=request.response.body
  323. if 'gzip' in request.response.headers.get('Content-Encoding'):
  324. resp = gzip.decompress(request.response.body)
  325. if 'br' in request.response.headers.get('Content-Encoding'):
  326. resp = brotli.decompress(request.response.body)
  327. # resp = brotli.decompress(request.response.body)
  328. jstext = resp.decode('utf-8')
  329. result = parsing_js(jstext)
  330. output['reviews'] = str(result)
  331. time.sleep(1)
  332. return output
  333. return 0
  334. def photos_parsing_js(resp):
  335. def image_url_change_size(url):
  336. if url.find('streetviewpixels') != -1:
  337. return url
  338. else:
  339. url_split = url.split('=')
  340. new_url = url_split[0] + '=s600-' + '-'.join(url_split[-1].split('-')[-2::])
  341. return new_url
  342. jsobj = json.loads(resp[5::])
  343. # write_to_file(jsobj,'tmp/debug_{}.pickle'.format(c))
  344. menu = []
  345. all = []
  346. photo_category_map = {}
  347. for row in jsobj[12][0]:
  348. photo_category_map[row[0]] = row[2]
  349. if photo_category_map[jsobj[13][0]] == '全部':
  350. for img in jsobj[0]:
  351. all += [image_url_change_size(img[6][0])]
  352. elif photo_category_map[jsobj[13][0]] == '菜單':
  353. for img in jsobj[0]:
  354. menu += [image_url_change_size(img[6][0])]
  355. return list(set(menu)), list(set(all))
  356. def process_web_request_photo(driver, output, fid):
  357. try:
  358. driver.find_element(By.CSS_SELECTOR, "button[data-tab-index='0']")
  359. photo_soup = BeautifulSoup(driver.page_source, 'html.parser')
  360. tab_dict = {}
  361. for tab_index in [0, 1, 2]:
  362. selector = photo_soup.select("button[data-tab-index='{}']".format(tab_index))
  363. if len(selector) != 0:
  364. photo_name = selector[0].text
  365. if photo_name == '菜單':
  366. tab_dict[photo_name] = tab_index
  367. elif photo_name == '全部':
  368. tab_dict[photo_name] = tab_index
  369. except:
  370. tab_dict = {}
  371. print(tab_dict)
  372. for tab_ in tab_dict:
  373. tab_index = tab_dict[tab_]
  374. print(tab_index)
  375. wait = WebDriverWait(driver, 60)
  376. wait.until(
  377. EC.element_to_be_clickable((By.CSS_SELECTOR, "button[data-tab-index='{}']".format(tab_index)))
  378. )
  379. element = driver.find_element(By.CSS_SELECTOR, "button[data-tab-index='{}']".format(tab_index))
  380. ActionChains(driver).move_to_element(element).click(element).perform()
  381. time.sleep(1)
  382. print("photo&**********************")
  383. menu_list = []
  384. all_list = []
  385. for request in driver.requests:
  386. if request.response:
  387. # print(request.url)
  388. if 'photo?' in request.url :
  389. # print('parsing js:')
  390. front, _ = fid.split(':')
  391. if request.url.find(front) != -1:
  392. # resp = brotli.decompress(request.response.body)
  393. print(request.url)
  394. resp=request.response.body
  395. if 'gzip' in request.response.headers.get('Content-Encoding'):
  396. resp = gzip.decompress(request.response.body)
  397. if 'br' in request.response.headers.get('Content-Encoding'):
  398. resp = brotli.decompress(request.response.body)
  399. jstext = resp.decode('utf-8')
  400. menu, all = photos_parsing_js(jstext)
  401. menu_list += menu
  402. all_list += all
  403. output['shop_photo'] = str(all_list[:5])
  404. output['menu_photo'] = str(menu_list[:5])
  405. return output
  406. def main():
  407. global chrome_window
  408. global store_list_table
  409. global shop_table
  410. global proxyport
  411. global iddict
  412. localip=socket.gethostbyname(socket.gethostname())
  413. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/google_poi?charset=utf8mb4')
  414. store_list_table = db['swire_store_list']
  415. shop_table = db['shop_list3']
  416. error_table = db['error_list2']
  417. iddict=build_cache(db)
  418. print("iddict...{}".format(datetime.now()))
  419. port=4444
  420. if len(sys.argv) == 3 :
  421. port=int(sys.argv[1])
  422. proxyport=int(sys.argv[2])
  423. if not chrome_window:
  424. print('restart docker pw{}'.format(port))
  425. # os.system('sudo docker container restart p'+str(port))
  426. # os.system('sudo docker container restart pw'+str(port))
  427. # time.sleep(10)
  428. print('drvier start...')
  429. driver = brower_start(port)
  430. job = get_next_job(db)
  431. c = 0
  432. for row, group in job.iterrows():
  433. try:
  434. item_url = group['item_url']
  435. name = group['name']
  436. num = group['num']
  437. keyword = group['keyword']
  438. fid = group['fid']
  439. if name:
  440. db_name = name
  441. else:
  442. db_name = num
  443. print(fid, keyword, db_name)
  444. print(item_url)
  445. #shop_info
  446. print('parsing shop info....')
  447. for i in range(5):
  448. print('shop info try...{}'.format(i))
  449. print("shop info try...{}".format(datetime.now()))
  450. driver.get(item_url)
  451. time.sleep(3)
  452. wait = WebDriverWait(driver, 10)
  453. wait.until(
  454. EC.element_to_be_clickable((By.ID, 'sb_cb50'))
  455. )
  456. element = driver.find_element_by_id('sb_cb50')
  457. driver.implicitly_wait(9)
  458. ActionChains(driver).move_to_element(element).click(element).perform()
  459. time.sleep(1)
  460. driver.back()
  461. if driver.current_url == item_url:continue
  462. print(driver.current_url)
  463. try:
  464. output = process_web_request_start(driver, fid)
  465. if output != 0: break
  466. except:
  467. r = redis.Redis(host='db.ptt.cx', port=6379, db=1,password='choozmo9')
  468. msg=traceback.format_exc()
  469. r.set('google_error',msg)
  470. # reivews
  471. print('parsing reviews....')
  472. print("parsing reviews.....{}".format(datetime.now()))
  473. if not output['user_ratings_total']:
  474. output['reviews'] = ''
  475. else:
  476. for i in range(3):
  477. print('reviews try...{}'.format(i))
  478. print("reviews try.....{}".format(datetime.now()))
  479. try:
  480. wait = WebDriverWait(driver, 30)
  481. more_reviews_css = "button[jsaction='pane.rating.moreReviews']"
  482. wait.until(
  483. EC.element_to_be_clickable((By.CSS_SELECTOR, more_reviews_css))
  484. )
  485. element = driver.find_element_by_css_selector(more_reviews_css)
  486. driver.implicitly_wait(10)
  487. ActionChains(driver).move_to_element(element).click(element).perform()
  488. time.sleep(0.5)
  489. output_ = process_web_request_reviews(driver, output, output['ludocid'])
  490. if output_ != 0:
  491. output = output_
  492. break
  493. except:
  494. driver.get(item_url)
  495. time.sleep(0.5)
  496. # if 'reviews' not in output.keys():
  497. # continue
  498. # photo
  499. print('parsing photo....')
  500. if output['header_image'] != '':
  501. for i in range(3):
  502. print('photo try...{}'.format(i))
  503. print("photo try......{}".format(datetime.now()))
  504. driver.get(item_url)
  505. time.sleep(0.5)
  506. print(driver.current_url)
  507. try:
  508. wait = WebDriverWait(driver, 30)
  509. wait.until(
  510. EC.element_to_be_clickable((By.CSS_SELECTOR, "div[aria-label='{}的相片']".format(output['name'])))
  511. )
  512. element = driver.find_element(By.CSS_SELECTOR, "div[aria-label='{}的相片']".format(output['name']))
  513. ActionChains(driver).move_to_element(element).click(element).perform()
  514. output = process_web_request_photo(driver, output, fid)
  515. break
  516. except:
  517. pass
  518. else:
  519. output['shop_photo'] = '[]'
  520. output['menu_photo'] = '[]'
  521. output['item_url'] = item_url
  522. output['keyword'] = keyword
  523. if output['google_url'] == '':
  524. query_name = output['adress_name'].replace('(','').replace(')', '').replace(' ','')
  525. output['google_url'] = 'https://www.google.com.tw/search?q={}'.format(query_name)
  526. output['crawler_date'] = datetime.today().strftime("%Y/%m/%d %H:%M")
  527. print(output)
  528. save_js_to_db(output, fid)
  529. print("save_js_to_db......{}".format(datetime.now()))
  530. error_table.upsert({'item_url':item_url,'check_':1},['item_url'])
  531. print('*'*10)
  532. except TimeoutException as e:
  533. traceback.print_exc()
  534. break
  535. except TimeoutException as e:
  536. traceback.print_exc()
  537. break
  538. except:
  539. r = redis.Redis(host='db.ptt.cx', port=6379, db=1,password='choozmo9')
  540. msg=traceback.format_exc()
  541. r.set('google_error',msg)
  542. error_table3 = db['error_list3']
  543. error_table3.insert({'name':name,'keyword':keyword,'item_url':item_url,'crawler_date':datetime.today().strftime("%Y/%m/%d %H:%M")})
  544. traceback.print_exc()
  545. # sys.exit()
  546. if __name__ == '__main__':
  547. main()