GooglePoiCrawler/run4.py

# -*- coding: utf-8 -*-
#from selenium import webdriver
from tkinter.tix import TEXT
from seleniumwire import webdriver
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.common.by import By
import selenium
import traceback
from bs4 import BeautifulSoup

from utility import database_access as DA
from utility.parseutils import *
from utility.connect import *

from datetime import datetime
from requests import session
import pandas as pd
import dataset
import time
import json
import re
import sys, os
import socket
import brotli
import pickle
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities
import urllib.parse
chrome_window=False
globalkw=None
proxyport=8787


def write_to_file(jsobj,fname):
    with open(fname, 'wb') as handle:
        pickle.dump(jsobj, handle, protocol=pickle.HIGHEST_PROTOCOL)


def build_cache(db):
    global reviews_table
    id_dict={}
    cursor = db.query('SELECT fid FROM google_poi.shop_list3;')

    for c in cursor:
        key = '{}'.format(c['fid'])
        id_dict[key]=1
    return id_dict


def brower_start(port):
    global proxyport
    global chrome_window
    print(proxyport)
    options = webdriver.ChromeOptions()
    if chrome_window:
        browser = webdriver.Chrome(
            desired_capabilities=options.to_capabilities()
        )
    else:
        chrome_options = webdriver.ChromeOptions()
        chrome_options.add_argument('--proxy-server=host.docker.internal:'+str(proxyport))  # Specify your Kubernetes service-name here
        chrome_options.add_argument('--ignore-certificate-errors')
        chrome_options.add_argument("--no-sandbox")
        chrome_options.add_argument("--disable-dev-shm-usage")
        browser = webdriver.Remote(
            command_executor='http://127.0.0.1:'+str(port)+'/wd/hub',
            desired_capabilities=chrome_options.to_capabilities(),
            seleniumwire_options={'addr':'0.0.0.0','port':proxyport,'auto_config': False}

            )
        browser.set_window_size(1400,1000)
    return browser


def get_next_job(db):
    result = {}
    result = db.query('select * from error_list2 where check_=0 ORDER BY RAND() limit 10')
    url_pd = pd.DataFrame([dict(i) for i in result])
    url_pd['fid'] = url_pd['item_url'].apply(lambda x: x.split('data=')[1].split('!')[3])
    # 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))

    remove = db.query('select item_url from error_list3')
    remove = pd.DataFrame([dict(i) for i in remove])
    if len(remove) != 0:
        remove_fid_list = remove['item_url'].to_list()
        url_pd = url_pd[~url_pd['item_url'].isin(remove_fid_list)]

    return url_pd


def parsing_js(resp):
    txt = json.loads(resp[5::])

    output = {}

    output['name'] = txt[6][11]
    output['adress_name'] = txt[6][18]

    if txt[6][4]:
        if txt[6][4][7]:
            output['rating'] = str(txt[6][4][7])
        else:
            output['rating'] = ''

        if txt[6][4][8]:
            output['user_ratings_total'] = str(txt[6][4][8])
        else:
            output['user_ratings_total'] = ''

        if txt[6][4][2]:
            output['price_level'] = str(len(['$' for i in txt[6][4][2] if i == '$']))
        else:
            output['price_level'] = ''
    else:
        output['rating'] = ''
        output['user_ratings_total'] = ''
        output['price_level'] = ''

    if txt[6][37][0]:
        output['lon'] = txt[6][37][0][0][8][0][1]
        output['lat'] = txt[6][37][0][0][8][0][2]    
    else:
        output['lon'] = None
        output['lat'] = None

    if txt[6][178]:
        output['tel'] = txt[6][178][0][0]
    else:
        output['tel'] = ''
    
    if txt[6][13]:
        output['category'] = txt[6][13][0]
    else:
        output['category'] = ''

    try:
        location = txt[6][183][2][2][0]
        if location:
            location_s = location.split(' ')
            output['city'], output['area'] = location_s[-1], location_s[-2]
        else:
            output['city'], output['area'] = '', ''
    except:
        output['city'], output['area'] = '', ''

    if txt[6][100]:
        for item in txt[6][100][1]:
            name = item[1]
            if name not in intro_list.keys(): continue
            name_map = intro_list[name]
            c = 0
            detail = []
            for t in item[2]:
                value = t[1]
                if t[3] == 1:
                    detail += [{'id':c, name_map[1]:'不提供'+str(value)}]
                else:
                    detail += [{'id':c, name_map[1]:value}]
                c += 1
            output[name_map[0]] = str(detail)

    for key in intro_list:
        if intro_list[key][0] not in output.keys():
            output[intro_list[key][0]] = '[]'
            
    if txt[6][34]:
        output = time_parsing_js(txt[6][34], output)
    else:
        output['open_now'] = 'False'
        output['periods'] = ''
        output['weekday_text'] = ''
        output['time_status'] = ''
    
    if txt[6][72]:
        output['header_image'] = txt[6][72][0][0][6][0]
    else:
        output['header_image'] = ''

    print(output)
    # write_to_file(orig,'debug.pickle')
    return output 


def time_parsing_js(time_json, output):
    weekday_text = []
    periods = []

    for time_ in time_json[1]:
        week = time_[0]
        weekday_text += ['{}: {}'.format(week, ', '.join(time_[1]))]
        
        for t in time_[1]:
            if t == '24 小時營業':
                periods += [{
                                "open":{
                                    "day": week_list[week], 
                                    "time": '0000'
                                },
                                "close":{
                                    "day": week_list[week], 
                                    "time": ''
                                }
                            }]
            elif t == '休息':
                periods += [{
                            "open":{
                                "day": week_list[week], 
                                "time": ''
                            },
                            "close":{
                                "day": week_list[week], 
                                "time": ''
                            }
                        }]
            else:
                start, end = t.split('–')
                end_hour, end_min = end.split(':')
                start_hour, start_min = start.split(':')

                if end_hour < start_hour:
                    end_day = week_list[week] + 1 
                else:
                    end_day = week_list[week]

                periods += [{
                    "open":{
                        "day": week_list[week], 
                        "time": start.replace(':','')
                    },
                    "close":{
                        "day": end_day, 
                        "time": end.replace(':','')
                    }
                }]
                
    output['periods'] = str(periods)
    output['weekday_text'] = str(weekday_text)
    output['time_status'] = blank_check(time_json[4][4].split('⋅')[0])

    if output['time_status'].find('永久停業') != -1 or\
       output['time_status'].find('暫時關閉') != -1 or\
       output['time_status'].find('暫停營業') != -1:
        output['open_now'] = 'False'
    else:
        output['open_now'] = 'True'

    return output


def save_js_to_db(jsobj, fid):
    global shop_table
    global iddict
  
    jsobj['fid'] = fid
    if iddict.get(fid) is None:
        try:
            shop_table.insert(jsobj)
        except:
            traceback.print_exc()


def process_web_request_start(driver, fid):
    time.sleep(3)

    print("start&**********************")
    for request in driver.requests:
        if request.response:
            # print(request.url)
            if 'place?' in request.url :
                print('parsing js:')
                front, _ = fid.split(':')
                if request.url.find(front) != -1:
                    print(request.url)
                    resp = brotli.decompress(request.response.body)
                    jstext = resp.decode('utf-8')
                    output = parsing_js(jstext)
                    time.sleep(1)
                    return output, request.url                 
    return 0, 0


def reviews_parsing_js(resp):
    columns_name = ['id','author_page','author_name', 'profile_photo_url', 'author_review_count',
              'created_at', 'text', 'photos', 'rating', 'store_review_time','store_review']
    jsobj = json.loads(resp[5::])
    result = []
    for i in range(len(jsobj[2])):
        tmp = []
        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]]
        tmp += [jsobj[2][i][1], jsobj[2][i][3]]

        # image
        image = []
        if jsobj[2][i][14]:
            for j in range(len(jsobj[2][i][14])):
                image += [jsobj[2][i][14][j][6][0]]
        tmp += [image]

        #rating
        tmp += [jsobj[2][i][4]]

        # store reply
        if jsobj[2][i][9]:
            tmp += [jsobj[2][i][9][0], jsobj[2][i][9][1]]
        else:
            tmp += ['', '']

        result.append(list(map(lambda x, y: {x:y}, columns_name, tmp)))

    return result 


def process_web_request_reviews(driver, output, start_js):
    time.sleep(3)

    print("reviews&**********************")
    for request in driver.requests:
        if request.response:
            # print(request.url)
            if 'listentitiesreviews?' in request.url :
                print('parsing js:')
                if start_js.find(request.url.split('!')[-2]) != -1:
                    print(request.url)
                    resp = brotli.decompress(request.response.body)
                    jstext = resp.decode('utf-8')
                    result = reviews_parsing_js(jstext)
                    output['reviews'] = str(result)
                    time.sleep(1)
                    return output
    return 0


def photos_parsing_js(resp):
    def image_url_change_size(url):
        if url.find('streetviewpixels') != -1:
            return url
        else:
            url_split = url.split('=')
            new_url = url_split[0] + '=s600-' + '-'.join(url_split[-1].split('-')[-2::])
            return new_url

    jsobj = json.loads(resp[5::])
    # write_to_file(jsobj,'tmp/debug_{}.pickle'.format(c))

    menu = []
    all = []
    photo_category_map = {}
    for row in jsobj[12][0]:
        photo_category_map[row[0]] = row[2]

    if photo_category_map[jsobj[13][0]] == '全部':
        for img in jsobj[0][:5]:
            all += [image_url_change_size(img[6][0])]

    elif photo_category_map[jsobj[13][0]] == '菜單':
        for img in jsobj[0][:5]:
            menu += [image_url_change_size(img[6][0])]

    return menu, all
    

def process_web_request_photo(driver, output, fid):
    try:
        driver.find_element(By.CSS_SELECTOR, "button[data-tab-index='0']")
        photo_soup = BeautifulSoup(driver.page_source, 'html.parser')
        tab_dict = {}
        for tab_index in [0, 1, 2]:
            selector = photo_soup.select("button[data-tab-index='{}']".format(tab_index))
            if len(selector) != 0:
                photo_name = selector[0].text
                if photo_name == '菜單':
                    tab_dict[photo_name] = tab_index
                elif photo_name == '全部':
                    tab_dict[photo_name] = tab_index
    except:
        tab_dict = {}
    print(tab_dict)

    for tab_ in tab_dict:
        tab_index = tab_dict[tab_]
        print(tab_index)
        wait = WebDriverWait(driver, 60)
        wait.until(
            EC.element_to_be_clickable((By.CSS_SELECTOR, "button[data-tab-index='{}']".format(tab_index)))
        )
        element = driver.find_element(By.CSS_SELECTOR, "button[data-tab-index='{}']".format(tab_index))
        ActionChains(driver).move_to_element(element).click(element).perform()
        time.sleep(1)

    print("photo&**********************")
    menu_list = []
    all_list = []
    for request in driver.requests:
        if request.response:
            # print(request.url)
            if 'photo?' in request.url :
                print('parsing js:')
                front, _ = fid.split(':')
                if request.url.find(front) != -1:
                    print(request.url)
                    resp = brotli.decompress(request.response.body)
                    jstext = resp.decode('utf-8')
                    menu, all = photos_parsing_js(jstext)
                    menu_list += menu
                    all_list += all

    output['shop_photo'] = str(all_list[:5])
    output['menu_photo'] = str(menu_list[:5])

    return output
    

def main():
    global chrome_window
    global store_list_table
    global shop_table
    global proxyport
    global iddict

    localip=socket.gethostbyname(socket.gethostname())

    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/google_poi?charset=utf8mb4')
    store_list_table = db['swire_store_list']
    shop_table = db['shop_list3']
    error_table = db['error_list2']

    iddict=build_cache(db)
    
    port=4444
    if len(sys.argv) == 3 :
        port=int(sys.argv[1])
        proxyport=int(sys.argv[2])
    if not chrome_window:
        print('restart docker pw{}'.format(port))
#        os.system('sudo docker container restart p'+str(port))
        os.system('sudo docker container restart pw'+str(port))

        time.sleep(10)

    print('drvier start...')
    driver = brower_start(port)

    job = get_next_job(db)

    for row, group in job.iterrows():
        try:
            item_url = group['item_url']
            name = group['name']
            num = group['num']
            keyword = group['keyword']
            fid = group['fid']

            if name:
                db_name = name
            else:
                db_name = num

            print(fid, keyword, db_name)
            print(item_url)
            # ActionChains(driver).key_down(Keys.SHIFT).key_down(Keys.F5).perform()
            # driver.find_element_by_tag_name('body').send_keys(Keys.F5)
            # time.sleep(3)  
             
            #shop_info
            print('parsing shop info....')
            for i in range(5):
                print('shop info try...{}'.format(i))
                driver.get(item_url)
                time.sleep(3)                

                wait = WebDriverWait(driver, 10)
                wait.until(
                    EC.element_to_be_clickable((By.ID, 'sb_cb50'))
                )
                element = driver.find_element_by_id('sb_cb50')
                driver.implicitly_wait(10)
                ActionChains(driver).move_to_element(element).click(element).perform()
                time.sleep(3)
                driver.back()

                if driver.current_url == item_url:continue
                print(driver.current_url)
                output, start_js = process_web_request_start(driver, fid)
                if output != 0: break


            # reivews
            print('parsing reviews....')
            if output['user_ratings_total'] == '':
                output['reviews'] = ''
            else:
                for i in range(3):
                    print('reviews try...{}'.format(i))
                    try:
                        wait = WebDriverWait(driver, 30)
                        more_reviews_css = "button[jsaction='pane.rating.moreReviews']"
                        wait.until(
                            EC.element_to_be_clickable((By.CSS_SELECTOR, more_reviews_css))
                        )
                        element = driver.find_element_by_css_selector(more_reviews_css)
                        driver.implicitly_wait(10)
                        ActionChains(driver).move_to_element(element).click(element).perform()
                        time.sleep(0.5)

                        output_ = process_web_request_reviews(driver, output, start_js)
                        if output_ != 0:
                            output = output_
                            break
                    except:
                        driver.get(item_url)
                        time.sleep(0.5)

                if 'reviews' not in output.keys():
                    continue

            # photo
            print('parsing photo....')
            if output['header_image'] != '':
                for i in range(3):
                    print('photo try...{}'.format(i))
                    driver.get(item_url)
                    time.sleep(0.5)
                    print(driver.current_url)
                    try:
                        wait = WebDriverWait(driver, 30)
                        wait.until(
                            EC.element_to_be_clickable((By.CSS_SELECTOR, "div[aria-label='{}的相片']".format(output['name'])))
                        )
                        element = driver.find_element(By.CSS_SELECTOR, "div[aria-label='{}的相片']".format(output['name']))
                        ActionChains(driver).move_to_element(element).click(element).perform()

                        output = process_web_request_photo(driver, output, fid)
                        break
                    except:
                        pass

            else:
                output['shop_photo'] = '[]'
                output['menu_photo'] = '[]'

            print(output)
            query_name = output['adress_name'].replace('（','').replace('）', '').replace(' ','')
            output['item_url'] = item_url
            output['keyword'] = keyword
            output['google_url'] = 'https://www.google.com.tw/search?q={}'.format(query_name)
            output['crawler_date'] = datetime.today().strftime("%Y/%m/%d %H:%M")

            save_js_to_db(output, fid)
            error_table.upsert({'item_url':item_url,'check_':1},['item_url'])
            print('*'*10)

        except:
            error_table3 = db['error_list3']
            error_table3.insert({'name':name,'keyword':keyword,'item_url':item_url,'crawler_date':datetime.today().strftime("%Y/%m/%d %H:%M")})
            traceback.print_exc()

if __name__ == '__main__':
    main()