kw_tools/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')

#account = searchconsole.authenticate(client_config=keysdir+'client_secret.json',serialize=keysdir+'credentials.json')

#account = searchconsole.authenticate(client_config=keysdir+'client_secrets.json',
#                                     serialize=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()
#  report=webproperty.query.range(begindate, days=30).dimension('query').get()
#  report=webproperty.query.range(begindate, days=30).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 >=3:
        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')
#gen_report('24','2021-06-14','2020-06-18')
#gen_report('25','2021-06-19','2020-06-25')
#gen_report('25','2021-06-19','2020-06-25')
#gen_report('26','2021-06-01','2021-06-27')
#gen_report('27','2021-06-28','2021-07-02')
#gen_report('27','2021-08-08','2021-08-14')
#gen_report('28','2021-08-13','2021-08-19')
#gen_report('29','2021-08-20','2021-08-27')

#gen_report('28','2021-08-15','2021-08-21')
#gen_report('2','2021-12-29','2022-01-05')
#gen_report('2','2021-12-29','2022-01-05')
#gen_report('3','2022-01-06','2022-01-12')
#gen_report('4','2022-01-13','2022-01-21')
#gen_report('5','2022-01-22','2022-01-28')
#gen_report('6','2022-01-29','2022-02-04')
#gen_report('7','2022-02-05','2022-02-11')
#gen_report('8','2022-02-12','2022-02-18')
#gen_report('9','2022-02-19','2022-02-25')
#gen_report('10','2022-02-26','2022-03-04')
gen_report('11','2022-03-05','2022-03-11')


print('complete')

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