import pymysql
# 連接數(shù)據(jù)庫
db = pymysql.connect(host='192.168.254.109', user='root', password='123456', database='blog')
# 使用cursor()方法創(chuàng)建一個游標對象
cursor = db.cursor()
# ?查詢語句
sql = """select * from a"""
# 執(zhí)行 sql語句
cursor.execute(sql)
# 查詢?nèi)?
results = cursor.fetchall()
# 獲取表結構 ?cursor.description
fields = [field[0] for field in cursor.description]
# 序列化 成字典 zip ?把兩個可迭代對象合并成2維元組。然后用dict 轉化為字典。
res = [dict(zip(fields, result)) for result in results]
print(res)
# 關閉游標和數(shù)據(jù)庫的連接
cursor.close()
db.close()
pro_res = """ [{'id': 1, 'name': '111', 'create_time': datetime.datetime(2022, 1, 6, 11, 6, 42), 'update_time': datetime.datetime(2022, 1, 6, 11, 6, 42)},
? ? ? ? ? ?{'id': 2, 'name': '222', 'create_time': datetime.datetime(2022, 1, 6, 11, 36, 4), 'update_time': datetime.datetime(2022, 1, 6, 11, 36, 4)}]"""

若只執(zhí)行 results = cursor.fetchall()

1 產(chǎn)出的結果都是元組且不包含字段名稱的.

2 使用pandas去解析這個 results也無法產(chǎn)出字段的.

python代碼獲取mysql字段名和注釋

# coding=utf-8
import pymysql
def get_mysql_zi_duan():
    conn = pymysql.connect(host='192.168.', port=3306, user='hs', passwd='xi', db='db_x', charset='utf8')
    cursor01 = conn.cursor()
    cursor01.execute(
        "select column_name, column_comment from information_schema.columns where table_schema ='db_xingyun' and table_name = 'api_ind_guan_yi_s_d'")
    all_info = cursor01.fetchall()  # 數(shù)據(jù)庫字段名和注釋
    # print(all_info)
    zi_duan_ming = []
    zhushi = []
    for data in all_info:
        zi_duan_ming.append(data[0])
        zhushi.append(data[1])
        # print(data[0])
    print(str(zi_duan_ming).replace('[','').replace(']','').replace("'",''))
    print(zhushi)
    cursor01.close()
    conn.close()
if __name__ == '__main__':
    # 獲取一個表的 所有字段名
    get_mysql_zi_duan()