def get_tasks(all=False): """从数据库中查询基本符合要求的任务""" try: eng = get_engine() if all: sql = ("select id,task from auto_message_maneger.robot_tasks " " where (task -> '$.active') <> 'deleted'") result = eng.execute(sql) else: ts = get_timestamp() sql = ( "select id,task from auto_message_maneger.robot_tasks \n" "where (task -> '$.lastSendTs' + task -> '$.muteSeconds') < %s" " and (task -> '$.active') = 'active'") result = eng.execute(sql, ts) tasks = [] for _id, _task in result.fetchall(): task = json.loads(_task) task["id"] = _id tasks.append(task) eng.dispose() return tasks except Exception: add_local_errlog(format_exc(), path=ROBOT_ERROR_LOG) return []
def add_msg_log(robot, task_id, msg, test=False): txt = extract_msgtxt(msg) if test: txt = "[TEST]" + txt sql = "insert into auto_message_maneger.robot_said(robot,task_id,said) values(%s,%s,%s)" eng = get_engine() eng.execute(sql, (robot, task_id, txt)) eng.dispose()
def update_task(task): """把当前 task 的信息更新到数据库""" task = copy(task) task_id = task["id"] del task["id"] sql = "update auto_message_maneger.robot_tasks set task = %s where id = %s" eng = get_engine() eng.execute(sql, (dumps(task), task_id)) eng.dispose()
def read_mails_db(): sql = ("SELECT id,name,post_time,post_weekday,post_day,receiver," " complete_time,remarks,builder_name,info,creator " " FROM reports where delete_time is null;") eng = get_engine() items = eng.execute(sql) result = [list(i) for i in items.fetchall()] eng.dispose() return items.keys(), result
def add_post_log(computer, id_, name, is_empty): try: sql = ( "insert into local_post_logs(computer, report_id, report_name, is_empty)" " values(%s,%s,%s,%s)") eng = get_engine() eng.execute(sql, (computer, id_, name, is_empty)) eng.dispose() except Exception: pass
def get_task(tid): try: sql = "select id,task from auto_message_maneger.robot_tasks where id = %s" eng = get_engine() result = eng.execute(sql, (tid)) eng.dispose() for _id, _task in result.fetchall(): task = json.loads(_task) task["id"] = _id return task except Exception: raise KeyError("未查找到id为%d的任务." % tid)
def add_access_log(): try: data = json.loads(request.get_data()) data["ip"] = request.remote_addr sql = "insert into access_log(route,host,ip) values(%s,%s,%s)" eng = get_engine() params = (data["route"], data["host"], data["ip"]) eng.execute(sql, params) eng.dispose() return {"added_log": True} except Exception: pass
def read_cols(db, table): """读取一个表的所有列名""" try: sql = ("select column_name as col " "from information_schema.columns " "where table_schema = %s and table_name = %s order by col ") eng = get_engine() items = eng.execute(sql, (db, table)).fetchall() items = [x[0] for x in items] eng.dispose() return jsonify({db + table: items}) except Exception as e: return jsonify({"error": repr(e), "tb": format_exc()})
def robot_msg_history(): """根据机器人名称和任务id查询最近的100条发送记录""" try: task = json.loads(request.get_data()) sql = ("select robot, dt, said from robot_said " "where task_id = %s order by dt desc limit 100") eng = get_engine() result = eng.execute(sql, task["id"]) eng.dispose() values = [list(i) for i in result.fetchall()] for v in values: v[1] = str(v[1]) return jsonify(values) except Exception as e: return {"error": repr(e), "tb": format_exc()}
def robot_add_task(): """添加一个机器人任务""" try: task = json.loads(request.get_data()) task["updateTime"] = make_now_str() check_task(task) if "id" in task: del task["id"] sql = "insert into jy_mail.robot_tasks(task) values(%s)" eng = get_engine() eng.execute(sql, (dumps(task))) Id = eng.execute("SELECT LAST_INSERT_ID();").fetchall()[0][0] return jsonify({"response": "任务已创建.", "id": Id}) except Exception as e: return jsonify({"error": repr(e), "tb": format_exc()})
def create_mail_db(mail): fields = [ "name", "post_time", "receiver", "builder_name", "info", "remarks" ] maps = map(lambda x: mail[x], fields) name, post_time, receiver, builder_name, info, remarks = tuple(maps) info = json.dumps(info, ensure_ascii=False) sql = ("insert into " "reports(name,post_time,receiver,builder_name,info,remarks) " "values(%s,%s,%s,%s,%s,%s) ") eng = get_engine() params = (name, post_time, receiver, builder_name, info, remarks) eng.execute(sql, params) id_ = eng.execute("SELECT LAST_INSERT_ID();").fetchall()[0][0] eng.dispose() return id_
def read_db_set(): """读取数据库信息, 返回数据库名对应表名的字典""" try: sql = """SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES` WHERE TABLE_SCHEMA not in ('mysql', 'information_schema', 'performance_schema', 'sys')""" eng = get_engine() items = eng.execute(sql).fetchall() result = {} for db, table in items: if db not in result: result[db] = [] result[db].append(table) eng.dispose() return jsonify(result) except Exception as e: return jsonify({"error": repr(e), "tb": format_exc()})
def update_mail_db(mail): mail["info"] = json.dumps(mail["info"], ensure_ascii=False) sql = ("update reports set name = %s, post_time = %s, " "post_weekday= %s, post_day = %s, receiver = %s, " "info = %s, remarks = %s, creator = %s where id = %s") eng = get_engine() eng.execute( sql, ( mail["name"], mail["post_time"], mail["post_weekday"], mail["post_day"], mail["receiver"], mail["info"], mail["remarks"], mail["creator"], mail["id"], ), ) return jsonify({"response": "已修改."})
def delete_mail_db(id_): sql = "update reports set delete_time = now() where id = %s" eng = get_engine() deletes = eng.execute(sql, (id_)) return jsonify({"response": "%d个报表已删除." % deletes.rowcount})