def insert_china_day():
"""
中国每日汇总数据表首次插入
:return: None
"""
conn = None
cursor = None
try:
data = get_tencent_data()[0]
conn, cursor = get_conn()
sql = '''
INSERT INTO china_day_list VALUES(%(ds)s, %(confirm)s, %(suspect)s, %(dead)s, %(heal)s,
%(now_confirm)s, %(now_severe)s, %(imported_case)s, %(dead_rate)s, %(heal_rate)s,
%(add_confirm)s, %(add_suspect)s,%(add_dead)s,%(add_heal)s, %(add_imported_case)s)
'''
print(f'{time.asctime()} china_day_list开始插入数据')
for k, v in data.items():
v.update({'ds': k})
cursor.execute(sql, v)
conn.commit()
print(f'{time.asctime()} china_day_list插入数据完毕')
except:
conn.rollback()
traceback.print_exc()
finally:
close_conn(conn, cursor)
def update_china_day():
"""
更新中国每日汇总数据(每日只更新一次)
:return: None
"""
conn = None
cursor = None
try:
data = get_tencent_data()[0]
conn, cursor = get_conn()
sql = '''INSERT INTO china_day_list VALUES(%(ds)s, %(confirm)s, %(suspect)s, %(dead)s, %(heal)s,
%(now_confirm)s, %(now_severe)s, %(imported_case)s, %(dead_rate)s, %(heal_rate)s,
%(add_confirm)s, %(add_suspect)s,%(add_dead)s,%(add_heal)s, %(add_imported_case)s)
'''
# 多条数据的有无判断
sql_query = '''
SELECT confirm FROM china_day_list where ds=%s
'''
cursor.execute(sql_query, list(data.items())[-1][0])
if not cursor.fetchone():
print(f'{time.asctime()} 开始更新china_day_list')
list(data.items())[-1][1].update({'ds': list(data.items())[-1][0]})
cursor.execute(sql, list(data.items())[-1][1])
conn.commit()
print(f'{time.asctime()} china_day_list更新完毕')
else:
print(f'{time.asctime()} china_day_list已是最新数据')
except:
traceback.print_exc()
finally:
close_conn(conn, cursor)
def insert_provience_day():
"""
更新省市每日汇总数据
:return: None
"""
conn = None
cursor = None
try:
data = get_tencent_data()[1]
conn, cursor = get_conn()
sql = '''
INSERT INTO provience_day_list(provience, city, confirm, confirm_add, heal, dead, update_time)
VALUES(%s, %s, %s, %s, %s, %s, %s)
'''
# 只有一条数据的有无判断
sql_query = 'select %s=(select update_time from provience_day_list order by id desc limit 1)'
cursor.execute(sql_query, data[1][-1])
if not cursor.fetchone()[0]:
print(f'{time.asctime()}:provience_day_list数据开始更新')
for item in data:
cursor.execute(sql, item)
conn.commit()
print(f'{time.asctime()}:provience_day_list更新最新数据完毕')
else:
print(f'{time.asctime()}:provience_day_list已是最新数据')
except:
traceback.print_exc()
finally:
close_conn(conn, cursor)
def update_hotsearch(context, broswer):
cursor = None
conn = None
try:
print(f"{time.asctime()}数据更新完毕")
conn, cursor = utils.get_conn()
sql = "insert into hotsearch(dt,content) values(%s,%s)"
ts = time.strftime("%Y-%m-%d %X")
for i in context:
cursor.execute(sql, (ts, i))
conn.commit()
print(f"{time.asctime()}数据更新完毕")
except:
traceback.print_exc()
finally:
utils.close_conn(conn, cursor)
broswer.quit()
def update_details(details):
try:
conn, cursor = utils.get_conn()
sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values (%s,%s,%s,%s,%s,%s,%s)"
sql_query = "select %s=(select update_time from details order by id desc limit 1)"
cursor.execute(sql_query, details[0][0])
if not cursor.fetchone()[0]:
print(f"{time.asctime()}开始更新数据")
for item in details:
cursor.execute(sql, item)
conn.commit()
print(f"{time.asctime()}数据更新完毕")
else:
print(f"{time.asctime()}当前已经是最新数据")
except:
traceback.print_exc()
finally:
utils.close_conn(conn, cursor)
def get_books_data():
try:
conn, cursor = get_conn()
sql = "select url,low,mid,high from book_data"
count = cursor.execute(sql)
# res = cursor.fetchone()
res = cursor.fetchall()
data_list = []
for out in res:
data_list.append({
"url": out[0],
"low": out[1],
"mid": out[2],
"high": out[3]
})
close_conn(conn, cursor)
return data_list
except Exception as e:
print(e)
line = line.strip('\n').split(' ')
x = list((line[0], line[1]))
sql = "insert into echarts4(skill,num) values(%s,%s)"
try:
cursor.execute(sql, x)
conn.commit()
except:
conn.rollback()
file.close()
file = open("static/data/city.txt", 'r')
sql = "truncate table city"
cursor.execute(sql)
lines = file.readlines()
if lines:
for line in lines:
line = line.strip('\n').split(' ')
x = list((line[0], line[1]))
sql = "insert into city(city,num) values(%s,%s)"
try:
cursor.execute(sql, x)
conn.commit()
except:
conn.rollback()
file.close()
# sql = " UPDATE city,tb_city SET tb_city.count=city.num where tb_city.shortname=city.city"
# cursor.execute(sql)
# conn.commit()
utils.close_conn(conn, cursor)
def query(sql, *args):
conn, cursor = utils.get_conn()
cursor.execute(sql, args)
res = cursor.fetchall()
utils.close_conn(conn, cursor)
return res