kw_tools/hhh/SEO/report_to_sheet.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 * FROM hhh.rank_details where dt > '2021-06-30 00:00:00' order by dt desc")
    cursor=db.query("SELECT * FROM hhh.rank_summary where dt > '2021-06-30 00:00:00' order by dt desc")

    win_lst={}
    c100_lst={}
    searchome_lst={}
    hhh_lst={}
    other_lst={}
    lost_lst={}
    df_records=[]
    for c in cursor:
#        print(c['designer'],c['title'],c['dt'],c['searchome'],c['c_100'],c['hhh'])
        title=c['title']
        dt=c['dt']
        searchome=int(c['searchome'])
        c_100=int(c['c_100'])
        hhh=int(c['hhh'])
        designer=c['designer']
#        df_records.append({'designer':designer,'title':title,'dt':dt,'searchome':searchome,'c_100':c_100,'hhh':hhh})

        if hhh > 0 and c_100==-1 and searchome ==-1:
            win_lst[designer]=hhh
            df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'幸福空間('+str(hhh)+')','a2':'競業未進首頁','a3':'競業未進首頁'})
    
        if hhh > 0 and searchome ==-1:
            if c_100 !=-1 and hhh < c_100:
                win_lst[designer]=hhh
                df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'幸福空間('+str(hhh)+')','a2':'100設計('+str(c_100)+')','a3':'競業未進首頁'})

            if c_100 !=-1 and hhh > c_100:
                c100_lst[designer]=1
                df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'100設計('+str(c_100)+')','a2':'幸福空間('+str(hhh)+')','a3':'競業未進首頁'})

        if hhh > 0 and searchome!=-1 and searchome<hhh:
            if win_lst.get(designer) is None:
                if c100_lst.get(designer) is None:
                    searchome_lst[designer]=searchome
                    lost_lst[designer]=hhh
                    df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'設計家('+str(searchome)+')','a2':'幸福空間('+str(hhh)+')','a3':'競業未進首頁'})


        if hhh > 0 and searchome!=-1 and hhh<searchome:
            if win_lst.get(designer) is None:
                if searchome_lst.get(designer) is None:
                    hhh_lst[designer]=hhh
                    df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'幸福空間('+str(hhh)+')','a2':'設計家('+str(searchome)+')','a3':'競業未進首頁'})

        if win_lst.get(designer) is None and  hhh_lst.get(designer) is None and searchome_lst.get(designer) is None and c100_lst.get(designer) is None:
            print(designer)
            other_lst[designer]=1
            df_records.append({'designer':designer,'title':title,'dt':dt,'a1':'幸福空間未進首頁','a2':'','a3':''})


    print(len(win_lst.keys()))
    print(len(searchome_lst.keys()))
    print(len(hhh_lst.keys()))
    print(len(c100_lst.keys()))

    print(len(other_lst.keys()))

    vv=0
    for k,v in lost_lst.items():
        vv+=v

    ww=0
    for k,v in searchome_lst.items():
        ww+=v

    print(vv/len(lost_lst.items()))
    print(ww/len(searchome_lst.items()))

    print(hhh_lst)




    idx=0
    df = pd.DataFrame(columns=('designer','a1','a2','a3','dt','url'))
    uniq={}
    for r in df_records:
        if uniq.get(r['designer']) is None:
            uniq[r['designer']]=1
        else:
            continue
#        if r['searchome']==-1:
#            r['searchome']='未進首頁'
#        if r['c_100']==-1:
#            r['c_100']='未進首頁'
#        if r['hhh']==-1:
#            r['hhh']='未進首頁'

        df.loc[idx]=[r['designer'],r['a1'],r['a2'],r['a3'],r['dt'],'https://www.google.com/search?q='+r['designer']]
    #    df.loc[idx]=['okok',333]
        idx+=1

#    idx=0

#    for c in cursor:
#        df.loc[idx]=[c['query'],c['users'],c['newusers'],c['pageviews'],c['bouncerate'],c['pvpersession'],c['url']]
    #    df.loc[idx]=['okok',333]
#        idx+=1
    save_sheet(df,'SEO_designer_results','ranking')
#    save_sheet(df,'ga_x_gsc','with_url')

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