gtrend_crawler/schedule/trending_searches.py

import datetime
from YoConfig import YoConfig
from datetime import tzinfo
from pytrends.request import TrendReq

import mysql.connector
from mysql.connector import Error

from opencc import OpenCC

cc = OpenCC('s2t')

DateFROM = '2020-01-01'
DateTO = str(datetime.date.today())

connection = mysql.connector.connect(
    host=YoConfig['db']['choozmo_new']['MYSQL_HOST'],
    database=YoConfig['db']['choozmo_new']['MYSQL_DB'],
    user=YoConfig['db']['choozmo_new']['MYSQL_USER'],
    password=YoConfig['db']['choozmo_new']['MYSQL_PASSWORD']
)

kw_list = []
pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')

""" pytrends.build_payload(kw_list, cat=0, timeframe=DateFROM +
                       ' '+DateTO, geo='TW', gprop='') """
i = 1
keyWords = pytrends.trending_searches(pn='taiwan')[0]
if connection.is_connected():
    cursor = connection.cursor()
    for word in keyWords:
        sql = "insert into trending_searches(ts_word, ts_rank) values('" + \
            word + "','" + str(i) + "')"
        i += 1
        cursor.execute(sql)
        kw_list = []
        kw_list.append(word)
        pytrends.build_payload(kw_list, cat=0, timeframe=DateFROM +
                               ' '+DateTO, geo='TW', gprop='')
        KWORD = kw_list[0]
        print(KWORD)
        rtdata = pytrends.related_topics()
        rqdata = pytrends.related_queries()
        iotdata = pytrends.interest_over_time()

        try:
            if not rtdata[KWORD]["rising"] is None:
                for item in rtdata[KWORD]["rising"].values.tolist():
                    sql = "insert into related_topics(rt_kword,rt_type,rt_value,rt_formattedValue,rt_link,rt_topic_mid,rt_topic_title,rt_topic_type) select '" + KWORD + "','rising','" + str(
                        item[0]) + "','" + item[1] + "','" + item[2] + "','" + item[3] + "','" + item[4].replace("'", "''") + "','" + item[5] + "' "
                    sql = cc.convert(sql)
                    cursor.execute(sql)
            if not rtdata[KWORD]["top"] is None:
                for item in rtdata[KWORD]["top"].values.tolist():
                    sql = "insert into related_topics(rt_kword,rt_type,rt_value,rt_hasData,rt_link,rt_topic_mid,rt_topic_title,rt_topic_type) select '" + KWORD + \
                        "','top','" + item[1] + "','" + str(item[2]) + "','" + item[3] + "','" + \
                        item[4] + "','" + item[5].replace("'", "''") + "','" + \
                        item[6] + "' "
                    sql = cc.convert(sql)
                    cursor.execute(sql)

            if not rqdata[KWORD]["rising"] is None:
                for item in rqdata[KWORD]["rising"].values.tolist():
                    sql = "insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select '" + \
                        KWORD+"','" + item[0].replace("'", "''") + "','" + \
                        str(item[1]) + "','rising' "
                    sql = cc.convert(sql)
                    cursor.execute(sql)
            if not rqdata[KWORD]["top"] is None:
                for item in rqdata[KWORD]["top"].values.tolist():
                    sql = "insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select '" + \
                        KWORD+"','" + item[0].replace("'", "''") + "','" + \
                        str(item[1]) + "','top' "
                    sql = cc.convert(sql)
                    cursor.execute(sql)

            i = 0
            for item in iotdata.index:
                if iotdata["isPartial"][i]:
                    sql = "insert into interest_over_time(iot_kword,iot_date,iot_value) select '" + KWORD+"','" + str(
                        item) + "','" + str(iotdata[KWORD][i]) + "' "
                    cursor.execute(sql)
                i += 1
        except:
            print(sql)

connection.commit()
if (connection.is_connected()):
    cursor.close()
    connection.close()