#!/usr/bin/env python
#-*-coding:utf-8-*-
import pymysql
import json
import collections
conn = pymysql.connect(host='xxx.xxx.xxx.xxx',
user='xxx',
passwd='xxx',
port=3308 ,
charset='xxx',
database='xxx',
cursorclass = pymysql.cursors.DictCursor)
sql='select a.*,b.end_time,b.ret_msg,b.sql_sql,b.ddl_ret_status,b.task_id,b.dba_audit_res,c.audit_user as leader_audit_user,c.audit_status as leader_audit_status,c.audit_describe as leader_audit_status from sql_single as a join sql_ddl_information as b on a.`sql_request_id`=b.`sql_request_id` join sql_single_aduit as c on b.`sql_request_id`=c.`sql_request_id`'
cursor = conn.cursor()
cursor.execute(sql)
title=[]
ret_msg=[]
for i in cursor.description:
title.append(i[0])
for i in range(cursor.rowcount):
new_dic = collections.OrderedDict()
count_msg=cursor.fetchone()
for key in title:
new_dic[key] = count_msg[key]
ret_msg.append(new_dic)
metadata = json.dumps(ret_msg,ensure_ascii=False)
print(title)
print(metadata)
输出结果:
title: ['id', 'username', 'start_time', 'single_describe', 'db_instance_group', 'sql_request_id', 'sql_request_state', 'sql_request_type', 'dc_name', 'dba_describe', 'audit_user', 'select_req_end_time', 'end_time', 'ret_msg', 'sql_sql', 'ddl_ret_status', 'task_id', 'dba_audit_res', 'leader_audit_user', 'leader_audit_status', 'leader_audit_status'] metadata: [ { "id": 7, "username": "xxx", "start_time": "2018-12-21 17:29:38", "single_describe": "的地方", "db_instance_group": "didapinche_test5的Master", "sql_request_id": "ddl-fbfcc4c802824dbc84c895ff8923cddd", "sql_request_state": "n", "sql_request_type": "ddl", "dc_name": "testing", "dba_describe": "没有理由.!!!", "audit_user": "chensanhu", "select_req_end_time": null, "end_time": "", "ret_msg": null, "sql_sql": "dXNlIHRlc3QyOwp0cnVuY2F0ZSB0ZXN0Mw==", "ddl_ret_status": "", "task_id": null, "dba_audit_res": null, "leader_audit_user": "zhangkuikui", "leader_audit_status": "n" }, { "id": 15497, "username": "xxx", "start_time": "2019-01-09 17:52:12", "single_describe": "asdasd", "db_instance_group": "didapinche_test5的Master", "sql_request_id": "ddl-08187452d019480eb90e041cebf422ae", "sql_request_state": "y", "sql_request_type": "ddl", "dc_name": "testing", "dba_describe": "123", "audit_user": "chensanhu", "select_req_end_time": null, "end_time": "2019-01-23 12:57:41", "ret_msg": "eyJleGVjdXRlX2ZhaWxl", "sql_sql": "dXNlIHRlc3Q=", "ddl_ret_status": "ok", "task_id": "1548219460.441335", "dba_audit_res": "N0YWdlIjogIkNIRUNLRUQifV0=", "leader_audit_user": "chenlin", "leader_audit_status": "w" } ]