def item_count_detail():
"""执行结果统计详细"""
spider_name = request.args.get("spider_name")
mysql = PureMysql(db_url=config.ITEM_LOG_DATABASE_URL)
cursor = mysql.execute(
"select * from {table} where spider_name=%(spider_name)s order by create_time desc limit 20"
.format(table=config.ITEM_LOG_TABLE),
data={"spider_name": spider_name})
create_times = []
durations = []
item_counts = []
log_errors = []
for row in cursor.fetchall():
create_times.insert(0, row["create_time"].strftime("%H:%M"))
durations.insert(0, row["duration"])
item_counts.insert(0, row["item_count"])
log_errors.insert(0, row["log_error"])
data = {
"spider_name": spider_name,
"create_times": create_times,
"durations": durations,
"item_counts": item_counts,
"log_errors": log_errors
}
mysql.close()
return jsonify(data)
def truncate_item():
"""清空统计列表"""
if config.ITEM_LOG_DATABASE_URL is None:
return jsonify({"code": -1})
mysql = PureMysql(db_url=config.ITEM_LOG_DATABASE_URL)
sql = "TRUNCATE table {table}".format(table=config.ITEM_LOG_TABLE)
cursor = mysql.execute(sql)
mysql.close()
return jsonify({"code": 0})
def item_count():
"""执行结果统计列表"""
if config.ITEM_LOG_DATABASE_URL is None:
return jsonify([])
mysql = PureMysql(db_url=config.ITEM_LOG_DATABASE_URL)
# sql = ""select * from {table} order by create_time desc limit 20".format(table=config.ITEM_LOG_TABLE))"
sql = """
select spider_name,
count(*) as total,
sum(item_count) as item_count,
sum(duration) as duration,
sum(log_error) as log_error,
max(create_time) as create_time
from {table}
GROUP BY spider_name
""".format(
table=config.ITEM_LOG_TABLE)
cursor = mysql.execute(sql)
data = []
count = 0
for row in cursor.fetchall():
count += 1
item = {
"id": count,
"create_time": row["create_time"].strftime("%Y-%m-%d %H:%M:%S"),
"duration": math.ceil((int(row["duration"]) / row['total'])),
"item_count": int(row["item_count"]),
"log_error": int(row["log_error"]),
"spider_name": row["spider_name"]
}
data.append(item)
mysql.close()
return jsonify(data)