pymysql之常见数据库操作

导包并关联数据库

import pymysql

conn = pymysql.Connect(host='localhost',user='root',db='stu',port=3306,password='dong')
c = conn.cursor()

查询某数据库各个表名

sql = 'show tables from stu'
rows = c.execute(sql)  # 返回执行成功的结果条数
print(f'一共有 {rows} 张表')
for d in c.fetchall():
    print(d[0])

打印某表各个字段名

sql = "select column_name from information_schema.columns where table_schema='%s' and table_name='%s';"
sql = sql % (dbName, tabName)
cur.execute(sql)
rowList = cur.fetchall()
for row in rowList:
    print(row[0])

获得有序集合：

#!/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"
    }
]

参考文章：

​​​​https://blog.51cto.com/walkerqt/2150530​​