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)