You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
280 lines
9.4 KiB
280 lines
9.4 KiB
import pymysql
|
|
from datetime import datetime, timedelta
|
|
import time
|
|
|
|
|
|
class MyDB:
|
|
def __init__(self, host, user, passwd, db):
|
|
self.conn = pymysql.connect(host, user, passwd, db)
|
|
self.cursor = self.conn.cursor()
|
|
|
|
def __del__(self):
|
|
self.conn.close()
|
|
|
|
# 获取当然日期
|
|
def get_cur_date(self):
|
|
date = datetime.today()
|
|
curdate = date.strftime('%Y-%m-%d')
|
|
return curdate
|
|
|
|
# 获取前N天的日期
|
|
def get_pren_date(self, n=1):
|
|
predate = datetime.today() + timedelta(-n)
|
|
predate = predate.strftime('%Y-%m-%d')
|
|
return predate
|
|
|
|
# 获取现有确诊人数最多的5个省份
|
|
def get_province_curconfirm_top5(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = "select provinceName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = "select provinceName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (predate + '%')
|
|
print('+++ presql: %s' % sql)
|
|
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n += 1
|
|
|
|
if n >= 30:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
|
|
#获取当前确诊人数前十的国家
|
|
def get_country_curconfirm_top10(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = "select country,curConfirm,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = "select country,curConfirm,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (predate + '%')
|
|
print('+++ presql: %s' % sql)
|
|
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n += 1
|
|
|
|
if n >= 30:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
# print(results)
|
|
return results
|
|
|
|
# 获取最新一天每个省份的现有确诊数量
|
|
def get_province_curConfirms(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = 'select provinceshortName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like "%s"' % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = 'select provinceshortName,currentConfirmedCount,pub_date from province_daily_datas where pub_date like "%s"' % (predate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n += 1
|
|
|
|
if n >= 15:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
# 获取最新一天每个国家的现有确诊数量
|
|
def get_country_curConfirms(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = 'select country,curConfirm,pub_date from foreign_datas where pub_date like "%s"' % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = 'select country,curConfirm,pub_date from foreign_datas where pub_date like "%s"' % (predate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n += 1
|
|
|
|
if n >= 15:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
# 获取最新一天广西的现有确诊数量
|
|
def get_guangxi_curConfirms(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = 'select cityName,confirmedCount,pub_date from city_daily_datas where province="广西" and pub_date like "%s"' % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = 'select cityName,confirmedCount,pub_date from city_daily_datas where province="广西" and pub_date like "%s"' % (predate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n += 1
|
|
|
|
if n >= 15:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
# 获取最新一天河南的现有确诊数量
|
|
def get_henan_curConfirms(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = 'select cityName,curedCount,pub_date from city_daily_datas where province="河南" and pub_date like "%s"' % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = 'select cityName,curedCount,pub_date from city_daily_datas where province="河南" and pub_date like "%s"' % (predate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n += 1
|
|
|
|
if n >= 15:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
# 获取现有确诊人数最多的5个省份
|
|
def get_province_top5(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = "select provinceName,currentConfirmedCount,confirmedCount,curedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = "select provinceName,currentConfirmedCount,confirmedCount,curedCount,pub_date from province_daily_datas where pub_date like '%s' order by currentConfirmedCount desc limit 5" % (predate + '%')
|
|
print('+++ presql: %s' % sql)
|
|
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n += 1
|
|
|
|
if n >= 30:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
# 获取现有确诊人数最多的10个国家的信息
|
|
def get_country_top10(self):
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
sql = "select country,curConfirm,confirmed,crued,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (curdate + '%')
|
|
print('+++ sql: %s' % sql)
|
|
|
|
results = []
|
|
try:
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
|
|
n = 1
|
|
|
|
while len(results) <= 0:
|
|
predate = self.get_pren_date(n)
|
|
sql = "select country,curConfirm,confirmed,crued,pub_date from foreign_datas where pub_date like '%s' order by curConfirm desc limit 10" % (predate + '%')
|
|
print('+++ presql: %s' % sql)
|
|
|
|
self.cursor.execute(sql)
|
|
results = self.cursor.fetchall()
|
|
n += 1
|
|
|
|
if n >= 30:
|
|
break
|
|
else:
|
|
time.sleep(1)
|
|
|
|
except Exception as e:
|
|
print(e)
|
|
#print(results)
|
|
return results
|
|
|
|
'''
|
|
if __name__ == "__main__":
|
|
mydb = MyDB('localhost', 'root', 'Aa183365', 'covid19_datas')
|
|
results = mydb.get_province_curconfirm_top5() # results : []
|
|
print(type(results[0]))
|
|
''' |