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})
