kw_tools_yover/hhh/GA_Keywords.py

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


fname=os.path.abspath(__file__)
elmts=fname.split(os.path.sep)
path2=os.path.sep.join(elmts[0:-1])
keysdir=path2+os.path.sep+'keys'+os.path.sep

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

db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
#db = dataset.connect('mysql://choozmo:pAssw0rd@127.0.0.1:3306/hhh?charset=utf8mb4')
db.query('delete from hhh_weekly_keywords')
table=db['hhh_weekly_keywords']
records=[]

def gen_report(weeknum,begindate,enddate):
  #report=webproperty.query.range('2021-01-01', '2020-01-02').dimension('page','query').get()
  report=webproperty.query.range(begindate, enddate).dimension('query').get()

  ts = time.time()
  sessionid=int(ts)

  for r in report:
  #    print(r)
      query=r[0]
      clicks=int(r[1])
      impressions=r[2]
      ctr=r[3]
      position=r[4]
      if clicks >=200:
        records.append({'sessionid':sessionid,'query':query,'clicks':clicks,'impressions':int(impressions),'ctr':float(ctr),'position':float(position),'dt':datetime.datetime.now(),'weeknum':weeknum})


gen_report('21','2021-05-24','2020-05-30')
gen_report('22','2021-05-31','2020-06-06')
gen_report('23','2021-06-07','2020-06-13')

print('complete')

db.begin()
for r in records:
    try:
        table.insert(r)
        db.commit()
    except:
        db.rollback()
print('done')