ranking_report.py 900 B

123456789101112131415161718192021222324252627
  1. import pandas as pd
  2. import dataset
  3. import pymysql
  4. pymysql.install_as_MySQLdb()
  5. name='辜琪鈞'
  6. date = '1128'
  7. def day():
  8. db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/seo?charset=utf8mb4')
  9. cursor = db.query(f'select term, domain from seo.selected_kw where client ="{name}"')
  10. kw_lst = []
  11. for c in cursor:
  12. kw_lst.append(c['term'])
  13. df = pd.DataFrame(kw_lst,columns=['kw'])
  14. rank_lst = []
  15. for i in kw_lst:
  16. cursor_general = db.query(f'select kw,ranking from seo.general_log where kw = "{i}" order by dt desc limit 1')
  17. for c in cursor_general:
  18. rank_lst.append([c['kw'],c['ranking']])
  19. db.close()
  20. df1 = pd.DataFrame(rank_lst,columns=['kw','ranking'])
  21. df_result = pd.merge(df, df1, on='kw', how='outer').fillna(0)
  22. df_result.to_csv(f"C:\/Users\/s1301\/Documents\/{date}{name}關鍵字排名.csv", index=False)