import traceback
import copy
import operator
import codecs
import sys
import os
import searchconsole
import dataset
import datetime
import random

db = dataset.connect('mysql://choozmo:pAssw0rd@139.162.121.30:33306/hhh?charset=utf8mb4')
table=db['monitor_kw_raw']


account = searchconsole.authenticate(client_config='c:/keys/service/client_secret.json',credentials='c:/keys/service/credentials.json')
webproperty = account['https://hhh.com.tw/']

allkw=['裝修設計','裝潢','老屋翻新','客廳','玄關設計','臥室','北歐風','廚房','裝潢費用','玄關','廚房收納','裝修','客廳擺設','高雄室內設計','室內設計','小坪數','設計師','廚房設計','客廳裝潢','客廳設計','裝潢設計']
# SELECT keyword,max(dt) FROM hhh.monitor_kw_raw group by keyword;

kw=random.choice(allkw)
#report=webproperty.query.range('2021-01-01', '2020-01-02').dimension('page','query').get()
#kw='裝修設計'

#for kw in all
report=webproperty.query.range('2021-01-01', '2020-05-06').dimension('query').filter('query',kw , 'contains').get()
data=[]
for r in report:
    print(r)

    query=r[0]
    clicks=r[1]
    impressions=r[2]
    ctr=r[3]
    position=r[4]
    data.append({'keyword':kw,'query':r[0],'clicks':r[1],'impressions':r[2],'ctr':ctr,'dt':datetime.datetime.now() })


for r in data:
    table.insert(r)