分享一段用于導(dǎo)出數(shù)據(jù)庫中表和字段定義至Excel文檔的python代碼，此處是針對MySQL的蕊梧，其他數(shù)據(jù)庫同理岭埠，只要少量的改動。

效果

代碼

import pandas as pd
import sqlalchemy as sqla
username='root'
password='1234'
server='localhost:3306'
database='world'
charset='utf8'
engine = sqla.create_engine('mysql+pymysql://{}:{}@{}/{}?charset={}'.format(username,password,server,database,charset))

#從系統(tǒng)表查詢定義
tables=pd.read_sql('''
select TABLE_SCHEMA,TABLE_NAME,CREATE_TIME,TABLE_COMMENT
from information_schema.TABLES 
where TABLE_SCHEMA='%s'
order by TABLE_NAME;
'''%database,engine)

columns=pd.read_sql('''
select TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_KEY,COLUMN_DEFAULT, COLUMN_COMMENT
from information_schema.COLUMNS 
where TABLE_SCHEMA='%s'
order by TABLE_NAME,ORDINAL_POSITION;
'''%database,engine)

#導(dǎo)出數(shù)據(jù)至Excel
file_path='C:\\Users\\hp\\Desktop\\%s.xlsx'%database
writer=pd.ExcelWriter(file_path)
tables.to_excel(writer,'Index',index=False)
for i in range(len(tables)):
    table_name=tables["TABLE_NAME"].iloc[i]
    columns_=columns[columns["TABLE_NAME"]==table_name].iloc[:,1:]
    columns_.to_excel(writer,table_name,index=False)
writer.save()

#調(diào)整格式
from openpyxl import load_workbook
from openpyxl.styles import Font,colors,Border,Side

border = Border(left=Side(style='medium',color='FF000000'),
                right=Side(style='medium',color='FF000000'),
                top=Side(style='medium',color='FF000000'),
                bottom=Side(style='medium',color='FF000000'),
                diagonal=Side(style='medium',color='FF000000'),
                diagonal_direction=0,outline=Side(style='medium',color='FF000000'),
                vertical=Side(style='medium',color='FF000000'),
                horizontal=Side(style='medium',color='FF000000'))
font=Font(underline='single', color=colors.BLUE)

wb = load_workbook(file_path) 
ws = wb.get_sheet_by_name("Index")
#索引頁調(diào)整列寬
ws.column_dimensions["A"].width =30.0
ws.column_dimensions["B"].width =30.0
ws.column_dimensions["C"].width =30.0
ws.column_dimensions["D"].width =40.0
#增加邊框線
for n in range(2,len(tables)+2):
    for m in ['A','B','C','D']:
        ws[m+str(n)].border=border
#處理各表的頁
for i in range(len(tables)):
    #索引頁增加調(diào)轉(zhuǎn)指定表頁的鏈接
    table_name=tables["TABLE_NAME"].iloc[i]
    ws["B"+str(i+2)].hyperlink = "#"+table_name+"!F1"
    ws["B"+str(i+2)].font = font
    ws2=wb.get_sheet_by_name(table_name)
    #指定表頁增加返回索引頁的鏈接
    ws2["F1"]="back"
    ws2["F1"].hyperlink = "#Index!B"+str(i+2)
    ws2["F1"].font=font
    #指定表頁調(diào)整列寬
    ws2.column_dimensions["A"].width =30.0
    ws2.column_dimensions["B"].width =30.0
    ws2.column_dimensions["C"].width =20.0
    ws2.column_dimensions["D"].width =20.0
    ws2.column_dimensions["E"].width =40.0
    #增加邊框線
    columns_=columns[columns["TABLE_NAME"]==tables["TABLE_NAME"].iloc[i]].iloc[:,1:]
    for n in range(2,len(columns_)+2):
        for m in ['A','B','C','D','E']:
            ws2[m+str(n)].border=border
wb.save(file_path)