kw_tools/hhh/reports/top_keywords_ranking.py

#!/usr/bin/python3
import sys
import codecs
import traceback
import requests
import re
import pandas as pd
import random
import urllib
import dataset
import json
import gspread
import datetime
from gspread_pandas import Spread, Client
from oauth2client.service_account import ServiceAccountCredentials
import os
import threading

def save_sheet(df,filename,tabname,startpos='A1'):

    scope = ['https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive']

    credentials = ServiceAccountCredentials.from_json_keyfile_name('c:\\keys\\spread2.json', scope)
#    credentials = ServiceAccountCredentials.from_json_keyfile_name('/var/keys/spread2.json', scope)

    gc = gspread.authorize(credentials)
    spread = Spread(filename,creds=credentials)

    spread.df_to_sheet(df, index=False, sheet=tabname, start=startpos, replace=False)


def do_jobs():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/hhh?charset=utf8mb4')
#    cursor=db.query('SELECT distinct kw,if(ranking IS NULL ,"未上榜",ranking ) as r   FROM hhh.hhh_contentgap_serp where id >=155 order by r asc;')
#    cursor=db.query('SELECT distinct kw,if(ranking IS NULL ,"未上榜",ranking ) as r   FROM hhh.hhh_contentgap_serp where id >=394 order by r asc;')
#    cursor=db.query('SELECT distinct kw,if(ranking IS NULL ,"未上榜",ranking ) as r   FROM hhh.hhh_contentgap_serp where id >=444 order by r asc;')
#    cursor=db.query('SELECT distinct kw,if(ranking IS NULL ,101,ranking ) as r   FROM hhh.hhh_top_serp where id >=274 order by r asc;')

    cursor=db.query('SELECT distinct kw, if( min(ranking) IS NULL ,101,min(ranking) )   as r FROM hhh.hhh_top_serp where datediff(now(),dt) =0  group by kw order by id desc;')



#SELECT * FROM hhh.hhh_contentgap_serp where id >=444 order by ranking desc;



    df = pd.DataFrame(columns=('kw','r'))

    idx=0

    for c in cursor:
        df.loc[idx]=[c['kw'],c['r']]
        idx+=1
    save_sheet(df,'WeeklyReport','top')

t = threading.Thread(target = do_jobs)
t.start()
t.join()