123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186 |
- import YoConfig
- from typing import Optional
- from fastapi import FastAPI
- from fastapi.middleware.cors import CORSMiddleware
- from pytrends.request import TrendReq
- from datetime import tzinfo
- import datetime
- import mysql.connector
- from mysql.connector import Error
- from opencc import OpenCC
- app = FastAPI()
- cc = OpenCC('s2t')
- origins = [
- "*"
- ]
- app.add_middleware(
- CORSMiddleware,
- allow_origins=origins,
- allow_credentials=True,
- allow_methods=["*"],
- allow_headers=["*"],
- )
- 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']
- )
- cursor = connection.cursor(buffered=True)
- @app.get("/")
- def read_root():
- return {"Hello": "World"}
- @app.get("/items/{item_id}")
- def read_item(item_id: int, q: Optional[str] = None):
- return {"item_id": item_id, "q": q}
- @app.get("/ts_top")
- def get_ts_tops(td: Optional[str] = str(datetime.date.today()), tc: Optional[int] = 3):
- if not connection.is_connected():
- connection.connect()
- cursor = connection.cursor(buffered=True)
- sql = "(SELECT * FROM trending_searches " + ((" where ts_date between '" + td + "' and '" + td + \
- "'+ INTERVAL 1 day ") if td != str(datetime.date.today()) else "") + \
- " ORDER BY ts_date DESC LIMIT 20) ORDER BY ts_date LIMIT " + str(tc)
- cursor.execute(sql)
- records = cursor.fetchall()
- return records
- @app.get("/related_queries/{Keyword}")
- def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
- save_userKw(Keyword, u)
- sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
- cursor.execute(sql)
- # print(str(cursor.rowcount))
- if not cursor.rowcount > 0:
- pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
- kw_list = []
- kw_list.append(Keyword)
- pytrends.build_payload(kw_list, cat=0, timeframe=fd +
- ' '+td, geo='TW', gprop='')
- KWORD = kw_list[0]
- rqdata = pytrends.related_queries()
- try:
- 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] + '\',\'' + \
- 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] + '\',\'' + \
- str(item[1]) + '\',\'top\' '
- sql = cc.convert(sql)
- cursor.execute(sql)
- except:
- print(sql)
- connection.commit()
- else:
- records = cursor.fetchall()
- return records
- sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
- cursor.execute(sql)
- records = cursor.fetchall()
- return records
- @app.get("/related_topics/{Keyword}")
- def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
- save_userKw(Keyword, u)
- sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
- cursor.execute(sql)
- # print(str(cursor.rowcount))
- if not cursor.rowcount > 0:
- pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
- kw_list = []
- kw_list.append(Keyword)
- pytrends.build_payload(kw_list, cat=0, timeframe=fd +
- ' '+td, geo='TW', gprop='')
- KWORD = kw_list[0]
- rtdata = pytrends.related_topics()
- 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] + '\',\'' + 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] + '\',\'' + \
- item[6] + '\' '
- sql = cc.convert(sql)
- cursor.execute(sql)
- except:
- print(sql)
- connection.commit()
- else:
- records = cursor.fetchall()
- return records
- sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
- cursor.execute(sql)
- records = cursor.fetchall()
- return records
- def save_userKw(Keyword: str, Username: str = ''):
- if not connection.is_connected():
- connection.connect()
- cursor = connection.cursor(buffered=True)
- sql = 'insert into user_searches(us_username,us_search_word) select \'' + \
- Keyword + '\',\'' + Username + '\' '
- cursor.execute(sql)
- return
- @app.get("/related_queries1/{Keyword}")
- def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
- save_userKw(Keyword, u)
- pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
- kw_list = []
- kw_list.append(Keyword)
- pytrends.build_payload(kw_list, cat=0, timeframe=fd +
- ' '+td, geo='TW', gprop='')
- KWORD = kw_list[0]
- rqdata = pytrends.related_queries()
- if not rqdata[KWORD]['rising'] is None:
- return rqdata[KWORD]['rising'].values.tolist()
- if not rqdata[KWORD]['top'] is None:
- return rqdata[KWORD]['top'].values.tolist()
- return
- @app.get("/related_topics1/{Keyword}")
- def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
- save_userKw(Keyword, u)
- pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
- kw_list = []
- kw_list.append(Keyword)
- pytrends.build_payload(kw_list, cat=0, timeframe=fd +
- ' '+td, geo='TW', gprop='')
- KWORD = kw_list[0]
- rtdata = pytrends.related_topics()
- if not rtdata[KWORD]['rising'] is None:
- return rtdata[KWORD]['rising'].values.tolist()
- if not rtdata[KWORD]['top'] is None:
- return rtdata[KWORD]['top'].values.tolist()
- return
|