fastapi_yo.py 7.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186
  1. import YoConfig
  2. from typing import Optional
  3. from fastapi import FastAPI
  4. from fastapi.middleware.cors import CORSMiddleware
  5. from pytrends.request import TrendReq
  6. from datetime import tzinfo
  7. import datetime
  8. import mysql.connector
  9. from mysql.connector import Error
  10. from opencc import OpenCC
  11. app = FastAPI()
  12. cc = OpenCC('s2t')
  13. origins = [
  14. "*"
  15. ]
  16. app.add_middleware(
  17. CORSMiddleware,
  18. allow_origins=origins,
  19. allow_credentials=True,
  20. allow_methods=["*"],
  21. allow_headers=["*"],
  22. )
  23. connection = mysql.connector.connect(
  24. host=YoConfig['db']['choozmo_new']['MYSQL_HOST'],
  25. database=YoConfig['db']['choozmo_new']['MYSQL_DB'],
  26. user=YoConfig['db']['choozmo_new']['MYSQL_USER'],
  27. password=YoConfig['db']['choozmo_new']['MYSQL_PASSWORD']
  28. )
  29. cursor = connection.cursor(buffered=True)
  30. @app.get("/")
  31. def read_root():
  32. return {"Hello": "World"}
  33. @app.get("/items/{item_id}")
  34. def read_item(item_id: int, q: Optional[str] = None):
  35. return {"item_id": item_id, "q": q}
  36. @app.get("/ts_top")
  37. def get_ts_tops(td: Optional[str] = str(datetime.date.today()), tc: Optional[int] = 3):
  38. if not connection.is_connected():
  39. connection.connect()
  40. cursor = connection.cursor(buffered=True)
  41. sql = "(SELECT * FROM trending_searches " + ((" where ts_date between '" + td + "' and '" + td + \
  42. "'+ INTERVAL 1 day ") if td != str(datetime.date.today()) else "") + \
  43. " ORDER BY ts_date DESC LIMIT 20) ORDER BY ts_date LIMIT " + str(tc)
  44. cursor.execute(sql)
  45. records = cursor.fetchall()
  46. return records
  47. @app.get("/related_queries/{Keyword}")
  48. def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
  49. save_userKw(Keyword, u)
  50. sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
  51. cursor.execute(sql)
  52. # print(str(cursor.rowcount))
  53. if not cursor.rowcount > 0:
  54. pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
  55. kw_list = []
  56. kw_list.append(Keyword)
  57. pytrends.build_payload(kw_list, cat=0, timeframe=fd +
  58. ' '+td, geo='TW', gprop='')
  59. KWORD = kw_list[0]
  60. rqdata = pytrends.related_queries()
  61. try:
  62. if not rqdata[KWORD]['rising'] is None:
  63. for item in rqdata[KWORD]['rising'].values.tolist():
  64. sql = 'insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select \'' + \
  65. KWORD+'\',\'' + item[0] + '\',\'' + \
  66. str(item[1]) + '\',\'rising\' '
  67. sql = cc.convert(sql)
  68. cursor.execute(sql)
  69. if not rqdata[KWORD]['top'] is None:
  70. for item in rqdata[KWORD]['top'].values.tolist():
  71. sql = 'insert into related_queries(rq_kword,rq_relatedword,rq_count,rq_type) select \'' + \
  72. KWORD+'\',\'' + item[0] + '\',\'' + \
  73. str(item[1]) + '\',\'top\' '
  74. sql = cc.convert(sql)
  75. cursor.execute(sql)
  76. except:
  77. print(sql)
  78. connection.commit()
  79. else:
  80. records = cursor.fetchall()
  81. return records
  82. sql = 'select * from related_queries where rq_kword = \'' + Keyword + '\' '
  83. cursor.execute(sql)
  84. records = cursor.fetchall()
  85. return records
  86. @app.get("/related_topics/{Keyword}")
  87. def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
  88. save_userKw(Keyword, u)
  89. sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
  90. cursor.execute(sql)
  91. # print(str(cursor.rowcount))
  92. if not cursor.rowcount > 0:
  93. pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
  94. kw_list = []
  95. kw_list.append(Keyword)
  96. pytrends.build_payload(kw_list, cat=0, timeframe=fd +
  97. ' '+td, geo='TW', gprop='')
  98. KWORD = kw_list[0]
  99. rtdata = pytrends.related_topics()
  100. try:
  101. if not rtdata[KWORD]['rising'] is None:
  102. for item in rtdata[KWORD]['rising'].values.tolist():
  103. 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(
  104. item[0]) + '\',\'' + item[1] + '\',\'' + item[2] + '\',\'' + item[3] + '\',\'' + item[4] + '\',\'' + item[5] + '\' '
  105. sql = cc.convert(sql)
  106. cursor.execute(sql)
  107. if not rtdata[KWORD]['top'] is None:
  108. for item in rtdata[KWORD]['top'].values.tolist():
  109. 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 + \
  110. '\',\'top\',\'' + item[1] + '\',\'' + str(item[2]) + '\',\'' + item[3] + '\',\'' + \
  111. item[4] + '\',\'' + item[5] + '\',\'' + \
  112. item[6] + '\' '
  113. sql = cc.convert(sql)
  114. cursor.execute(sql)
  115. except:
  116. print(sql)
  117. connection.commit()
  118. else:
  119. records = cursor.fetchall()
  120. return records
  121. sql = 'select * from related_topics where rt_kword = \'' + Keyword + '\' '
  122. cursor.execute(sql)
  123. records = cursor.fetchall()
  124. return records
  125. def save_userKw(Keyword: str, Username: str = ''):
  126. if not connection.is_connected():
  127. connection.connect()
  128. cursor = connection.cursor(buffered=True)
  129. sql = 'insert into user_searches(us_username,us_search_word) select \'' + \
  130. Keyword + '\',\'' + Username + '\' '
  131. cursor.execute(sql)
  132. return
  133. @app.get("/related_queries1/{Keyword}")
  134. def get_related_queries(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
  135. save_userKw(Keyword, u)
  136. pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
  137. kw_list = []
  138. kw_list.append(Keyword)
  139. pytrends.build_payload(kw_list, cat=0, timeframe=fd +
  140. ' '+td, geo='TW', gprop='')
  141. KWORD = kw_list[0]
  142. rqdata = pytrends.related_queries()
  143. if not rqdata[KWORD]['rising'] is None:
  144. return rqdata[KWORD]['rising'].values.tolist()
  145. if not rqdata[KWORD]['top'] is None:
  146. return rqdata[KWORD]['top'].values.tolist()
  147. return
  148. @app.get("/related_topics1/{Keyword}")
  149. def get_related_topics(Keyword: str, u: Optional[str] = '', fd: Optional[str] = '2020-01-01', td: Optional[str] = str(datetime.date.today())):
  150. save_userKw(Keyword, u)
  151. pytrends = TrendReq(hl='zh-TW', tz=1200, geo='TW')
  152. kw_list = []
  153. kw_list.append(Keyword)
  154. pytrends.build_payload(kw_list, cat=0, timeframe=fd +
  155. ' '+td, geo='TW', gprop='')
  156. KWORD = kw_list[0]
  157. rtdata = pytrends.related_topics()
  158. if not rtdata[KWORD]['rising'] is None:
  159. return rtdata[KWORD]['rising'].values.tolist()
  160. if not rtdata[KWORD]['top'] is None:
  161. return rtdata[KWORD]['top'].values.tolist()
  162. return