kw_tools/SEO/ranking_report.py

import pandas as pd
import dataset
import pymysql
pymysql.install_as_MySQLdb()

name='寵星'
date = '0502'


def day():
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/seo?charset=utf8mb4')
    cursor = db.query(f"select term,domain from seo.selected_kw where client ='{name}'")
    # cursor = db.query("SELECT value,query FROM gtrend2.topics where sessionid='20240614-HHH' order by value desc")
    kw_lst = []

    for c in cursor:
        kw_lst.append(c['term'])
    print(kw_lst)

    df = pd.DataFrame(kw_lst,columns=['kw'])

    rank_lst = []
    for i in kw_lst:
        cursor_general = db.query(f'select kw,ranking,url from seo.google_bank where kw = "{i}" order by dt desc limit 1')
        for c in cursor_general:
            rank_lst.append([c['kw'],c['ranking'],c['url']])
    db.close()
    df1 = pd.DataFrame(rank_lst,columns=['kw','ranking','url'])
    df_result = pd.merge(df, df1, on='kw', how='outer').fillna(101)
    df_result.to_csv(f"C:\/Users\/s1301\/OneDrive\/Documents\/{date}{name}關鍵字排名.csv", index=False)
    print('完成')

day()