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_history_china():
history_china = get_history_china()
# 获取数据库连接
conn, cursor = utils.get_conn()
# 时间是字符串数据，不能直接写入到数据库中
# insert into history_china(date) values ('2011-04-08 00:00:00');
sql = '''insert into
history_china(date,confirmed_count,confirm_add,suspect,suspect_add,cure,cure_add,dead,dead_add,
current_confirmed_count,current_confirmed_Incr) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
cursor.execute('delete from history_china')
# 提交事务
conn.commit()
for i in history_china:
# 日期字符串转为y-m-d形式，固定写法
datastr = str(i['dateId'])
tup = time.strptime(datastr, "%Y%m%d")
dt = time.strftime("%Y-%m-%d", tup)
cursor.execute(sql, [
dt, i['confirmedCount'], i['confirmedIncr'], i['suspectedCount'],
i['suspectedCountIncr'], i['curedCount'], i['curedIncr'],
i['deadCount'], i['deadIncr'], i['currentConfirmedCount'],
i['currentConfirmedIncr']
])
# 提交事务
conn.commit()
print("中国历史数据数据插入成功")
utils.close(conn, cursor)
def mutate(self, info, title_ident, review_fail_reason):
user = get_user(info)
if user is None:
raise Exception('此操作需要用户登录')
# if user.role != 'manager':
# raise Exception('您没有评审权限')
conn = utils.get_conn()
with conn.cursor() as cur:
cur.execute(
'''
update title_bank
set label_review_user = %(username)s,
label_review_status = '评审未通过',
review_fail_reason = %(review_fail_reason)s,
label_tag_status = '待修改'
where title_ident = %(title_ident)s
''',
dict(
title_ident=title_ident,
review_fail_reason=review_fail_reason,
username=user.username,
)
)
if cur.rowcount != 1:
raise Exception('没有成功更新 title_bank 记录状态')
conn.commit()
res = update_question_status_to_es(title_ident)
return NotPassQuestionDetailReview(status=res['result'], title=get_title(title_ident))
def mutate(self, info, discourse_code, review_fail_reason):
user = get_user(info)
# if user.role != 'manager':
# raise Exception('您没有评审权限')
conn = utils.get_conn()
with conn.cursor() as cur:
cur.execute(
'''
update discourse_quata
set discourse_review_user = %(username)s,
review_status = '评审未通过',
review_fail_reason = %(review_fail_reason)s,
tag_status = '待修改'
where discourse_code = %(discourse_code)s
''',
dict(
discourse_code=discourse_code,
review_fail_reason=review_fail_reason,
username=user.username,
)
)
if cur.rowcount != 1:
raise Exception('没有成功更新 discourse_quata 记录状态')
conn.commit()
res = update_discourse_status_to_es(discourse_code)
return NotPassDiscourseReview(status=res['result'], discourse=get_discourse(discourse_code))
def update_question_status_to_es(title_ident):
with utils.get_conn().cursor() as cur:
title = get_title(title_ident)
cur.execute(
'select * from question_detail where title_ident=%(title_ident)s',
dict(title_ident=title_ident)
)
rows = cur.fetchall()
questions = [dict(zip([c.name for c in cur.description], r)) for r in rows]
for question in questions:
question.pop('id')
question.pop('title_ident')
updateMap = dict(
# 必传参数
title_ident=title_ident,
label_tag_status=title.label_tag_status,
label_tag_user=title.label_tag_user,
question_detail=questions,
)
if title.label_review_status and title.label_review_user:
# 点击 评审通过 按钮时，传入如下参数：
updateMap["label_review_status"] = title.label_review_status
updateMap["label_review_user"] = title.label_review_user
res = update_question_status(updateMap)
return res
def resolve_gp_titles2(self, info, title_ident__in=None, **kwargs):
url = 'http://54.223.130.63:5000/getEsInfo'
import requests
resp = requests.post(url, data=kwargs).json()
es_rows = resp['result']
# import pdb; pdb.set_trace()
# resp = {'total_num': 4, 'page_size': 4, 'page_num': 1}
# es_rows = [{'title_ident': 'u3_9'}, {'title_ident': 'u3_10'}, {'title_ident': 'u3_2'}, {'title_ident': 'u3_7'}, ]
with utils.get_conn().cursor() as cur:
if not es_rows:
return TitleList(
total_num=resp['total_num'],
page_size=kwargs['page_size'],
page_num=kwargs['page_num'],
titles=[]
)
cur.execute(
'''
select *
from {table_name}
where title_ident in %(title_ident__in)s
'''.format(**dict(table_name='title_bank')),
dict(title_ident__in=tuple([r['title_ident'] for r in es_rows]))
)
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
title_idents = [r['title_ident'] for r in es_rows]
return TitleList(
total_num=resp['total_num'],
page_size=kwargs['page_size'],
page_num=kwargs['page_num'],
titles=sorted([Title(**d) for d in dicts], key=lambda t: title_idents.index(t.title_ident))
)
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 insert_taday_province_china():
details_china = get_history_china_details()
# 获取数据库连接
conn, cursor = utils.get_conn()
cursor.execute('delete from today_province_china')
# 提交事务
conn.commit()
sql = '''insert into
today_province_china(id,update_time,province,city,current_confirmed_count,current_confirmed_Incr,cure,cure_add,dead,dead_add) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
# 获取当前时间戳
data = time.strftime("%Y-%m-%d %X") # 代表小时和分钟
i = 1
for province in details_china:
cursor.execute(sql, [
i, data, province['provinceName'], province['provinceName'],
province['currentConfirmedCount'], province['confirmedIncr'],
province['curedCount'], province['curedIncr'],
province['deadCount'], province['deadIncr']
])
i += 1
conn.commit()
# 提交事务
conn.commit()
print("中国各省数据插入成功")
utils.close(conn, cursor)
def get_stocks_id():
with get_conn() as conn:
with conn.cursor() as cursor:
sql = "select commodity_id from stock.commodity where market_type = '上市'"
cursor.execute(sql)
rows = cursor.fetchall()
rows = [row[0] for row in rows]
return rows
def resolve_discourse(self, info):
with utils.get_conn().cursor() as cur:
cur.execute('''
select *
from {table_name}
where discourse_code = %(discourse_code)s
'''.format(**dict(table_name='discourse_quata')), dict(discourse_code=self.discourse_code))
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Discourse(**d) for d in dicts][0]
def resolve_questions(self, info):
with utils.get_conn().cursor() as cur:
cur.execute('''
select *
from {table_name}
where title_ident = %(title_ident)s
'''.format(**dict(table_name='question_detail')), dict(title_ident=self.title_ident))
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Question(**d) for d in dicts]
def export_labels(req):
import json
query_params = json.loads(req.GET['query_params'])
CSV_FIELD_NAMES = [
("p_skill_type", "标签分类"),
("p_label_type", "语言技能"),
("p_label_name", "一级标签"),
("c_label_name", "二级标签"),
]
with utils.get_conn().cursor() as cur:
sql = '''
select p.skill_type p_skill_type, p.label_type p_label_type, p.label_name p_label_name, c.label_name c_label_name
from label_dict p, label_dict c
where p.id = c.parent_id
{0}
order by p_skill_type, p_label_type, p_label_name, c_label_name
'''.format(' '.join(
map(
lambda xs: ' '.join(xs),
zip(
repeat('and'),
map(lambda x: 'p.%s = %%(%s)s' % (x, x),
query_params.keys())))))
print(sql)
cur.execute(sql, query_params)
rows = cur.fetchall()
VAL_MAP = {
"TL": "听力",
"YD": "阅读",
"XZ": "写作",
"KY": "口语",
"WJN": "微技能",
"NRKJ": "内容框架"
}
dicts = [
dict(
zip([c.name for c in cur.description],
map(lambda x: VAL_MAP[x] if x in VAL_MAP else x, row)))
for row in rows
]
CSV_FIELD_NAME_DICT = dict(CSV_FIELD_NAMES)
CSV_FIELD_NAMES_ = {fn for (fn, cn) in CSV_FIELD_NAMES}
f = get_StringIO_obj([cn for (fn, cn) in CSV_FIELD_NAMES], [
dict(
map(lambda x: (CSV_FIELD_NAME_DICT[x[0]], x[1]),
filter(lambda x: x[0] in CSV_FIELD_NAMES_, r.items())))
for r in dicts
])
resp = StreamingHttpResponse(f.getvalue(), content_type="text/csv")
resp[
'Content-Disposition'] = 'attachment; filename=export_%s.csv' % datetime.datetime.now(
).strftime('%Y%m%d_%H%M')
return resp
def testManyVirtio(self):
d = VirtualDisk(conn=utils.get_conn(), bus="virtio",
path="/default-pool/testvol1.img")
targetlist = []
for ignore in range(0, (26 * 2) + 1):
d.target = None
d.generate_target(targetlist)
targetlist.append(d.target)
self.assertEquals("vdaa", targetlist[26])
self.assertEquals("vdba", targetlist[26 * 2])
def get_discourse(discourse_code):
with utils.get_conn().cursor() as cur:
cur.execute(
'''
select *
from {table_name}
where discourse_code= %(discourse_code)s
'''.format(**dict(table_name='discourse_quata')),
dict(discourse_code=discourse_code)
)
row = cur.fetchone()
return Discourse(**dict(zip([c.name for c in cur.description], row)))
def get_title(title_ident):
with utils.get_conn().cursor() as cur:
cur.execute(
'''
select *
from {table_name}
where title_ident = %(title_ident)s
'''.format(**dict(table_name='title_bank')),
dict(title_ident=title_ident)
)
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Title(**d) for d in dicts][0]
def get_sub_labels(label_id):
with utils.get_conn().cursor() as cur:
cur.execute(
'''
select *
from {table_name}
where parent_id = %(label_id)s
'''.format(**dict(table_name='label_dict')),
dict(label_id=label_id)
)
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Label(**d) for d in dicts]
def toggle_switch(device_address):
current_state = request.args.get('state') == 'True' or False
address = ('0' if current_state else '1') + device_address
conn = utils.get_conn()
energie.message(address, conn)
# Turning on the heating?
if address == '1110':
# When turning on the heating, turn on the lights
energie.message('1111', conn)
conn.close()
return redirect('/#' + address)
def main():
"""Timelapse: Takes pictures adds them to the database."""
conn = utils.get_conn()
print("Database connection established.")
while True:
if utils.is_camera_on():
filename = take_picture()
update_table(filename, conn)
else:
print('Camera is off at', datetime.now())
time.sleep(3)
def insert_hotdata():
#获取数据库连接
conn, cursor = utils.get_conn()
sql = 'insert into hotsearch(dt,content) values (%s,%s)'
datas = get_hotdata()
#获取当前时间戳
dt = time.strftime("%Y-%m-%d %X")
for item in datas:
cursor.execute(sql, (dt, item))
conn.commit()
print('数据插入成功')
utils.close(conn, cursor)
def insert_hotdata():
# 获取数据库连接
conn, cursor = utils.get_conn()
# %s代表占位
sql = 'insert into hot_search(date,content) values(%s,%s)'
# 获取当前时间戳
data = time.strftime("%Y-%m-%d %X") # 代表小时和分钟
datas = get_hotdata()
for item in datas:
cursor.execute(sql, (data, item))
# 提交事务
conn.commit()
print("热搜数据插入成功")
utils.close(conn, cursor)
def get_labels(label_ids):
if not label_ids:
return []
with utils.get_conn().cursor() as cur:
cur.execute(
'''
select *
from {table_name}
where id in %(label_ids)s
'''.format(**dict(table_name='label_dict')),
dict(label_ids=tuple(map(int, filter(lambda x: x, label_ids))))
)
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Label(**d) for d in dicts]
def testCreateDisk(self):
"""
Doesn't really belong here, but what the hell :)
"""
path = "/tmp/__virtinst_create_test__.img"
sizegigs = .001
sizebytes = long(sizegigs * 1024L * 1024L * 1024L)
for sparse in [True, False]:
disk = VirtualDisk(conn=utils.get_conn(), path=path, size=sizegigs,
sparse=sparse)
disk.setup()
actualsize = long(os.path.getsize(path))
os.unlink(path)
self.assertEquals(sizebytes, actualsize)
def update_details():
conn, cursor = utils.get_conn()
details = get_details()
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()}已是最新数据")
utils.close(conn, cursor)
def resolve_gp_labels(self, info, **kwargs):
table_name = 'label_dict'
with utils.get_conn().cursor() as cur:
if kwargs:
cur.execute(
''' select * from {table_name} where {conditions} '''.format(**dict(
table_name=table_name,
conditions='\n and '.join(
map(lambda x: '{0} = %({0})s'.format(x), kwargs.keys())
))),
kwargs
)
else:
cur.execute('select * from {table_name}'.format(**dict(table_name=table_name)))
rows = cur.fetchall()
dicts = [dict(zip([c.name for c in cur.description], row)) for row in rows]
return [Label(**d) for d in dicts]
def testCpustrToTuple(self):
conn = utils.get_conn()
base = [False] * 16
expect = base[:]
expect[1] = expect[2] = expect[3] = True
self.assertEquals(tuple(expect),
virtinst.Guest.cpuset_str_to_tuple(conn, "1-3"))
expect = base[:]
expect[1] = expect[3] = expect[5] = expect[10] = expect[11] = True
self.assertEquals(tuple(expect),
virtinst.Guest.cpuset_str_to_tuple(conn, "1,3,5,10-11"))
self.assertRaises(ValueError,
virtinst.Guest.cpuset_str_to_tuple,
conn, "16")
def insert_hotdata():
# 获取数据库连接
conn, cursor = utils.get_conn()
sql = 'insert into hotdata(dt,content) values(%s,%s)'
sql_trunc = "truncate table hotdata"
datas = get_hotdata()
# dt当前时间
dt = time.strftime("%Y-%m-%d %X")
cursor.execute(sql_trunc)
for item in datas:
cursor.execute(sql, (dt, item))
conn.commit()
print("热搜数据插入成功！")
utils.close(conn, cursor)
def insert_details():
conn, cursor = utils.get_conn()
details = get_details()
# 执行插入数据
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("开始更新数据！")
for item in details:
cursor.execute(sql, item)
conn.commit()
print("数据更新成功！")
else:
print("已经是最新数据，不需要更新！")
def delete_mutate(MutationClass, table_name, Class, kwargs, info, status='ok'):
conn = utils.get_conn()
with conn.cursor() as cur:
cur.execute(
'''
delete from {table_name}
where {conditions}
'''.format(
**dict(
table_name=table_name,
conditions='\n and '.join(
map(lambda x: '{0} = %({0})s'.format(x), kwargs.keys())
))),
kwargs
)
conn.commit()
return MutationClass(status=status)
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)
