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

table=db['gsc_weekly']

#report=webproperty.query.range('2021-01-01', '2020-01-02').dimension('page','query').get()
report=webproperty.query.range('2021-05-30', '2020-06-05').dimension('query').get()
total_pos=0
total_count=0
total_clicks=0
records=[]

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

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

print('complete')

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