|
|
import pymysql
|
|
|
from datetime import datetime, timedelta
|
|
|
import time
|
|
|
|
|
|
|
|
|
class Mydb():
|
|
|
def __init__(self,host,user,passwd,db):
|
|
|
self.con=pymysql.connect(host,user,passwd,db)
|
|
|
self.cursor=self.con.cursor()
|
|
|
|
|
|
def get_cur_date(self):
|
|
|
date = datetime.today()
|
|
|
curdate = date.strftime('%Y-%m-%d')
|
|
|
return curdate
|
|
|
|
|
|
def close(self):
|
|
|
self.cursor.close()
|
|
|
self.con.close()
|
|
|
|
|
|
def get_pren_date(self, n=1):
|
|
|
predate = datetime.today() + timedelta(-n)
|
|
|
predate = predate.strftime('%Y-%m-%d')
|
|
|
return predate
|
|
|
|
|
|
def curConfirm_top5_proince(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select relativeTime,curConfirm,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"
|
|
|
order by curConfirm desc limit 5'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select relativeTime,curConfirm,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"
|
|
|
order by curConfirm desc limit 5'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
def inside_conRelativeAndComein(self):
|
|
|
sql="""
|
|
|
select sum(confirmedRelative),relativeTime
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
result1=[]
|
|
|
result1=self.cursor.fetchall()
|
|
|
sql="""
|
|
|
select sum(confirmedRelative),relativeTime
|
|
|
from insidecity
|
|
|
where city="境外输入"
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
result2=[]
|
|
|
result2=self.cursor.fetchall()
|
|
|
result=[]
|
|
|
for i in result1:
|
|
|
for j in result2:
|
|
|
if i[1]==j[1]:
|
|
|
result.append((int(i[0]),int(j[0]),i[1]))
|
|
|
break
|
|
|
return tuple(result)
|
|
|
|
|
|
def Confirm_proince(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select relativeTime,confirmedRelative,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s" and confirmedRelative !=0'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select relativeTime,confirmedRelative,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s" and confirmedRelative !=0'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
def American_confirmrelative(self):
|
|
|
sql='''select relativeTime,confirmedRelative
|
|
|
from outsidecountry
|
|
|
where area="美国"'''
|
|
|
results=[]
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
return results
|
|
|
|
|
|
def confirmRelative_top10_country(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select relativeTime,confirmedrelative,area
|
|
|
from outsideCountry
|
|
|
where relativeTime like "%s"
|
|
|
order by confirmedrelative desc limit 10'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select relativeTime,confirmedrelative,area
|
|
|
from outsideCountry
|
|
|
where relativeTime like "%s"
|
|
|
order by confirmedrelative desc limit 10'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
def insideAndoutside_ConfirmRelative(self):
|
|
|
sqlout='''select sum(confirmedRelative),relativeTime,'outside' as local
|
|
|
from outsidecountry
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc'''
|
|
|
result=[]
|
|
|
self.cursor.execute(sqlout)
|
|
|
resultou = self.cursor.fetchall()
|
|
|
sqlin='''select sum(confirmedRelative),left(relativeTime,10),'inside' as local
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc'''
|
|
|
self.cursor.execute(sqlin)
|
|
|
resultin=self.cursor.fetchall()
|
|
|
result=[]
|
|
|
for o in resultou:
|
|
|
for i in resultin:
|
|
|
if o[1]==i[1]:
|
|
|
result.append((o[1],int(o[0]),int(i[0])))
|
|
|
break
|
|
|
return tuple(result)
|
|
|
|
|
|
def outside_confirmedAndcurfirmed(self):
|
|
|
sql="""select sum(confirmed),sum(curConfirm) ,relativeTime
|
|
|
from outsidecountry
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc"""
|
|
|
self.cursor.execute(sql)
|
|
|
resultsql=[]
|
|
|
resultsql=self.cursor.fetchall()
|
|
|
result=[]
|
|
|
for r in resultsql:
|
|
|
result.append((int(r[0]),int(r[1]),r[2]))
|
|
|
return tuple(result)
|
|
|
|
|
|
def outside_diedAndcrued(self):
|
|
|
sql="""select sum(died),sum(crued) ,relativeTime
|
|
|
from outsidecountry
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc"""
|
|
|
self.cursor.execute(sql)
|
|
|
resultsql=[]
|
|
|
resultsql=self.cursor.fetchall()
|
|
|
result=[]
|
|
|
for r in resultsql:
|
|
|
result.append((int(r[0]),int(r[1]),r[2]))
|
|
|
return tuple(result)
|
|
|
|
|
|
def chinaCurconfirmed(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select relativeTime,curConfirm,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select relativeTime,curConfirm,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
def chinaConfirmed(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select relativeTime,confirmed,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select relativeTime,confirmed,area
|
|
|
from insideProvince
|
|
|
where relativeTime like "%s"'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
def outsideSummary(self):
|
|
|
sql="select * from outsideSummary"
|
|
|
self.cursor.execute(sql)
|
|
|
result=[]
|
|
|
result=self.cursor.fetchall()
|
|
|
ans=[]
|
|
|
for r in result[0]:
|
|
|
ans.append(r)
|
|
|
return tuple(ans)
|
|
|
|
|
|
def inside_curConAndConfirmed(self):
|
|
|
sql="""
|
|
|
SELECT sum(confirmed),sum(curConfirm),relativeTime
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
result=[]
|
|
|
result=self.cursor.fetchall()
|
|
|
ans=[]
|
|
|
for r in result:
|
|
|
ans.append((int(r[0]),int(r[1]),r[2]))
|
|
|
return tuple(ans)
|
|
|
|
|
|
def inside_cruedAnddied(self):
|
|
|
sql="""
|
|
|
SELECT sum(crued),sum(died),relativeTime
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
result=[]
|
|
|
result=self.cursor.fetchall()
|
|
|
ans=[]
|
|
|
for r in result:
|
|
|
ans.append((int(r[0]),int(r[1]),r[2]))
|
|
|
return tuple(ans)
|
|
|
|
|
|
def inside_cruedAnddieRate(self):
|
|
|
sql="""
|
|
|
SELECT sum(crued),sum(died),relativeTime
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
result=[]
|
|
|
result=self.cursor.fetchall()
|
|
|
sql=""" SELECT sum(confirmed),relativeTime
|
|
|
from insideprovince
|
|
|
group by relativeTime
|
|
|
order by relativeTime asc
|
|
|
"""
|
|
|
self.cursor.execute(sql)
|
|
|
conf=self.cursor.fetchall()
|
|
|
ans=[]
|
|
|
for i in result:
|
|
|
for j in conf:
|
|
|
if i[2]==j[1]:
|
|
|
c=int(j[0])
|
|
|
ans.append((float(i[0]/c),float(i[1]/c),i[2]))
|
|
|
break
|
|
|
return tuple(ans)
|
|
|
|
|
|
def get_overseas_data(self):
|
|
|
curTime=self.get_cur_date()
|
|
|
sql='''select province,relativeTime,confirmed
|
|
|
FROM insidecity
|
|
|
where city='境外输入' and relativeTime like "%s"
|
|
|
order by confirmed desc limit 10'''%(curTime+"%")
|
|
|
results=[]
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout=1
|
|
|
while(len(results)<=0):
|
|
|
curTime=self.get_pren_date(cout)
|
|
|
sql='''select province,relativeTime,confirmed
|
|
|
FROM insidecity
|
|
|
where city='境外输入' and relativeTime like "%s"
|
|
|
order by confirmed desc limit 10'''%(curTime+"%")
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
cout += 1
|
|
|
|
|
|
if cout >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
return results
|
|
|
|
|
|
class OutsideSummaryClass:
|
|
|
def __init__(self, host, user, passwd, db): # 构造函数，连接数据库，建立cursor
|
|
|
self.connection = pymysql.connect(host, user, passwd, db)
|
|
|
self.cursor = self.connection.cursor()
|
|
|
|
|
|
def data_result(self):
|
|
|
sql = "select * from outsidesummary order by time DESC"
|
|
|
self.cursor.execute(sql)
|
|
|
result = self.cursor.fetchone()
|
|
|
return result
|
|
|
|
|
|
def data_7days(self):
|
|
|
sql = "select sum(curConfirm),relativetime from outsidecountry group by relativetime order by relativetime asc"
|
|
|
count = self.cursor.execute(sql)
|
|
|
if count>7:
|
|
|
results = self.cursor.fetchmany(7)
|
|
|
else:
|
|
|
results = self.cursor.fetchall()
|
|
|
|
|
|
ans = []
|
|
|
for i in results :
|
|
|
ans.append((int (i[0]),i[1]))
|
|
|
return ans
|
|
|
|
|
|
class TopOverseasInputClass:
|
|
|
def __init__(self, host, user, passwd, db):
|
|
|
self.conn = pymysql.connect(host, user, passwd, db)
|
|
|
self.cursor = self.conn.cursor()
|
|
|
|
|
|
def get_overseas_data(self):
|
|
|
sql = "select * from topOverseasInput order by value DESC"
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
return results
|
|
|
|
|
|
class InsideProvinceClass:
|
|
|
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
|
|
|
|
|
|
#获取某数据最多的n个省
|
|
|
def get_data(self,dataType,num):
|
|
|
curdate = self.get_cur_date() # 获取当天的日期
|
|
|
sql = "select area,%s,relativeTime from insideprovince where relativeTime like '%s' %s order by %s desc limit %d" \
|
|
|
% (dataType,(curdate + '%'),("AND AREA NOT IN ('香港','澳门','台湾')" if dataType=="confirmedrelative" else ""),dataType,num)
|
|
|
results = []
|
|
|
try:
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
|
|
|
n = 1
|
|
|
while len(results) <= 0:
|
|
|
predate = self.get_pren_date(n)
|
|
|
sql = "select area,%s,relativeTime from insideprovince where relativeTime like '%s' %s order by %s desc limit %d" \
|
|
|
% (dataType,(predate + '%'),("AND AREA NOT IN ('香港','澳门','台湾')" if dataType=="confirmedrelative" else ""),dataType,num)
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
n += 1
|
|
|
|
|
|
if n >= 30:
|
|
|
break
|
|
|
else:
|
|
|
time.sleep(1)
|
|
|
|
|
|
except Exception as e:
|
|
|
print(e)
|
|
|
|
|
|
return results
|
|
|
|
|
|
class InsideSummaryClass:
|
|
|
def __init__(self, host, user, passwd, db):
|
|
|
self.conn = pymysql.connect(host, user, passwd, db)
|
|
|
self.cursor = self.conn.cursor()
|
|
|
|
|
|
def get_input_data(self,dataType):
|
|
|
sql = "select %s,InsertTime from insidesummary order by maplastupdatedtime ASC"%dataType
|
|
|
|
|
|
self.cursor.execute(sql)
|
|
|
results = self.cursor.fetchall()
|
|
|
|
|
|
return results
|
|
|
|
|
|
|
|
|
if __name__=='__main__':
|
|
|
db = Mydb('localhost','root','123456','cov2019_data')
|
|
|
result = db.get_overseas_data()
|
|
|
print(result)
|
|
|
|
|
|
|
