kw_tools/hhh/tests/seo_gsc_dump.py

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

#SELECT * FROM hhh.gsc_designer where
# (`page` = 'https://hhh.com.tw/designers/cases/491/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/31/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/293/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/278/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/24/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/594/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/356/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/307/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/491/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/33/1-page/new-sort/') or
# (`page` = 'https://hhh.com.tw/designers/cases/385/1-page/new-sort/') 
# order by impressions/position desc;



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.begin()

table=db['gsc_designer']

report=webproperty.query.range('2021-06-19', '2020-06-19').dimension('page','query').filter('page', 'https://hhh.com.tw/designers/cases/', 'contains').get()
total_pos=0
total_count=0
total_clicks=0
records=[]

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

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

for l in full_list:
    table.insert(l)
db.commit()
print(cnt)