還在手工生成資料庫文件？3個步驟自動完成了解一下

目錄

• 1 前言
• 2 生成資料庫說明文件的3步驟
• 3 獲取資料庫表元資訊
  • 3.1 客戶端 pymysql 基本使用
  • 3.2 獲取資料庫表元資訊
• 4 獲取資料表欄位的元資訊
• 5 生成 excel 文件
  • 5.1 輸出表欄位資訊到 excel 文件
  • 5.2 把各功能連線起來
• 6 （可選）設定 excel 文件格式
• 7 總結
• 參考資料
• 往期文章

自動化生成資料庫文件，簡單的3個步驟即可完成，瞭解一下。

1 前言

平時工作中，大家應該都會遇到需要匯出資料庫說明文件（也叫資料字典）的情況，即把各資料表的欄位資訊整理成一個個的表說明，然後用 excel/word/html/md 等文件格式進行儲存。很多小夥伴還在用原始的手工方式，複製貼上資料庫的欄位說明（名稱、型別、長度、註釋等），不得不說這種方式效率實在太低。作為程式設計師，能用程式設計解決的問題，就不是問題。下面介紹的方法很簡單，只需要3個步驟。本文將對這3個步驟使用 python 進行編碼實現，把資料表資訊說明輸出到 excel 文件中。因此，主要包含以下內容：

• 生成資料庫文件的3步驟說明
• 獲取資料庫表元資訊
• 獲取資料表列的元資訊
• 生成資料庫說明 excel 文件
• （可選）設定 excel 文件格式

2 生成資料庫說明文件的3步驟

由於資料庫都會儲存相應的後設資料資訊（即描述資料庫、資料表、資料欄位本身的資訊，如表名，欄位表、型別等等），因此，總的來說，生成資料庫說明文件的思路很簡單，分為3步：

• 1）根據資料庫名，從資料庫中獲取資料表元資訊，主要是表名，表註釋等
• 2）根據資料表名，獲取資料欄位的元資訊，主要是欄位名、欄位型別、是否可空、欄位註釋等
• 3）根據後設資料資訊生成文件

根據這個思路，把這3個步驟通過編碼即可自動生成文件。獲取後設資料資訊，各種資料庫會有不同的查詢語句，具體可以查詢相關官方文件，下面簡單列一下 mysql 及 oracle 的：

# mysql 查詢表資訊及欄位資訊
SELECT * FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = %db_name%
SELECT * FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = %db_name% AND TABLE_NAME = %table_name%

# oracle 查詢表資訊及欄位資訊
SELECT * FROM all_tables WHERE where owner= %db_name%
SELECT * FROM all_COL_COMMENTS WHERE owner = %db_name%  and TABLE_NAME=%table_name%

實現方式也可以根據各人喜歡的程式語言來實現。在本文中，以 MySQL 為例，使用 python 程式設計實現，把資料資訊輸出到 excel 文件（具體 excel 操作，可參考我上一篇文章《 Python 處理 Excel 檔案》）。輸出效果如下所示：

下面就跟著我進行實現吧。

3 獲取資料庫表元資訊

3.1 客戶端 pymysql 基本使用

使用 python 進行 MySQL 讀寫操作，使用的是 pymysql，讀者可以訪問它的官方文件瞭解它的安裝和使用。簡單來說，對資料庫進行讀資料，需要以下幾步：

• 連線資料庫：connect
• 獲取讀資料的遊標（ cursor ）：connection.cursor()
• 執行 SQL 語句獲取資料：cursor.execute(sql,args)，cursor.fetchall()，cursor.fetchone(),cursor.fetchmany()
• 關閉遊標和連線：connection.close()
• 獲取正在使用的工作表：workbook.active，cursor.close()

因此，我們在類的初始化（ __init__ ）和關閉（__del__ ）時，進行資料庫連線和關閉操作。程式碼如下：

def __init__(self, host, port, user, password, db_name, charset):
    # 初始化資料庫操作

    self.db = pymysql.connect(host=host, port=port, user=user,password=password, database=db_name, charset=charset)
    self.cursor = self.db.cursor()

def __del__(self):
    # 關閉資料庫連線

    self.db.close()
    self.cursor.close()

3.2 獲取資料庫表元資訊

根據上面說的 pymysql 的基本操作，只需要執行查詢資料表元資訊的 SQL 即可。前面已經提到，MySQL 的查詢表元資訊的 SQL 語句是SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = %db_name% ，而我們只需要表名及表註釋即可。因此實現如下：

def get_table_info(self, db_name):
    # 獲取資料表資訊

    sql = '''SELECT table_schema, table_name, table_comment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = %s order by table_name'''
    params = [db_name]
    # 查詢資料
    self.cursor.execute(sql, params)
    return self.cursor.fetchall()

此函式功能：傳入資料庫名，返回所有表資訊。

4 獲取資料表欄位的元資訊

獲取到表資訊後，同樣的道理，需要遍歷每一個表，根據表名獲取每個表的欄位資訊。前面已經到，MySQL 獲取表的欄位是查詢表 information_schema.COLUMNS即可，而對於欄位資訊， 我們主要關注欄位名、欄位型別、 是否允許為空、欄位的註釋描述等資訊。程式碼如下：

def get_table_column_info(self, database_name, table_name):
    # 獲取資料表列資訊

    params = [database_name, table_name]
    sql = '''SELECT
                TABLE_SCHEMA AS '庫名',TABLE_NAME AS '表名',
                COLUMN_NAME AS '列名',ORDINAL_POSITION AS '列的排列順序',
                COLUMN_DEFAULT AS '預設值',IS_NULLABLE AS '是否為空',
                DATA_TYPE AS '資料型別',CHARACTER_MAXIMUM_LENGTH AS '字元最大長度',
                NUMERIC_PRECISION AS '數值精度(最大位數)',NUMERIC_SCALE AS '小數精度',
                COLUMN_TYPE AS '列型別',COLUMN_COMMENT AS '註釋'
            FROM information_schema.`COLUMNS`
            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
            ORDER BY TABLE_NAME, ORDINAL_POSITION'''
    # 查詢資料
    self.cursor.execute(sql, params)
    return self.cursor.fetchall()

此函式功能，根據資料庫名及表名，獲取此表的欄位資訊。

5 生成 excel 文件

5.1 輸出表欄位資訊到 excel 文件

對於 excel 的操作，我們使用 openpyxl 進行讀寫操作，具體 excel 操作，可參考我上一篇文章《 Python 處理 Excel 檔案》。而現在我們需要實現的功能是把每個表的欄位資訊，以表格的方式寫入到 excel 表中，並按欄位名、允許為空、欄位型別、欄位描述進行輸出。

還有一點就是，我們經常在設計表的的過程中，基本都會有一些公共的欄位，比如 id ，建立時間、建立人、修改時間、修改人等，這些我們在匯出字典時，可以選擇過濾掉。因此，使用如下程式碼進行實現：

def create_file(self, file_path):
    # 獲取檔案，若檔案不存在則建立，存在則刪除後重新建立

    if os.path.exists(file_path):
        os.remove(file_path)
    wb = Workbook()
    wb.save(file_path)
    
def save_column_info_to_excel(self, table_name, table_comment, column_info, file_path, col_names_skip):
    # 寫入表資訊到excel檔案

    workbook = openpyxl.load_workbook(file_path)
    # 根據下標獲取（下標從0開始）
    sheet = workbook.worksheets[0]
    row_data = [table_name]
    if table_comment:
        row_data = [table_name + "(" + table_comment + ")"]
        sheet.append(row_data)
        rurrent_max_row = sheet.max_row
        # 空行分隔
        sheet.insert_rows(rurrent_max_row)
        # 列名
        col_name_data = ["欄位名", "允許為空", "型別", "欄位描述"]
        sheet.append(col_name_data)
        for row in column_info:
            # 需要過濾的
            if col_names_skip and row[2].lower() in col_names_skip:
                print("#" * 10, "跳過此欄位：", row[2])
                continue
            print(row[2] + "," + row[5] + "," + row[10] + "," + row[11])
            row_data = [row[2], row[5], row[10], row[11]]
            sheet.append(row_data)
        # 儲存文件
        workbook.save(file_path)

此處包含兩個函式，create_file 功能主要是建立文件，若文件存在則先刪除。save_column_info_to_excel功能是根據表欄位資訊及需要過濾的欄位名，使用 for 語句按行輸出到 excel 中，輸出過程中，若有需要過濾的欄位則跳過，最後把文件儲存到指定的路徑中。

5.2 把各功能連線起來

前面已經實現資料庫表元資訊獲取、資料表欄位元資訊獲取及欄位資訊輸出到 excel 文件三個功能。現在把這三個功能連線起來，就可以形成完整的資料庫文件匯出功能了。思路是遍歷生成的表元資訊( get_table_info )，根據表元資訊獲取表的欄位資訊( gen_table_column_info )，然後輸出 excel 文件( save_column_info_to_excel )，如下所示：

def gen_db_table_info_skip_col(self, db_name, file_path, col_names_skip):
    # 過濾指定列，匯出資料表資訊到文件

    table_info_rows = self.get_table_info(db_name)
    for table_row in table_info_rows:
        print("\n", "*" * 10, "生成表資訊：", table_row[1])
        self.gen_table_column_info(table_row, file_path, col_names_skip)

def gen_table_column_info(self, table_info_row, file_path, col_names_skip=None):
    # 匯出欄位資訊表到文件

    database_name = table_info_row[0]
    table_name = table_info_row[1]
    table_comment = table_info_row[2]
    # 從資料庫獲取表資訊
    column_info = self.get_table_column_info(database_name, table_name)
    # 寫入excel檔案
    self.save_column_info_to_excel(table_name, table_comment, column_info, file_path, col_names_skip)

此處包含兩個函式，gen_db_table_info_skip_col 功能是根據資料庫名、檔案儲存路徑、需要過濾的欄位名匯出表元資訊，然後使用 for 語句進行遍歷。gen_table_column_info 是根據表資訊及需要過濾的欄位，先讀表欄位資訊，然後寫入到 excel 文件。注意，此處col_names_skip 預設值為 None ，即如果不需要過濾，不輸入此引數即可。至此，我們自動生成資料庫文件的功能已完成。在__main__ 中執行看一下輸出情況：

if __name__ == '__main__':
    # 輸出文件地址
    excel_path = "E:/pythontest/test_tableinfo.xlsx"
    # 資料庫連線資訊
    host = "localhost"
    port = 3306
    user = "root"
    password = "123456"
    db_name = "test"
    charset = 'utf8'
    # 需要過濾的欄位
    col_names_to_skip = ["id", "sys_create_time", "sys_create_user", "sys_update_time", "sys_update_user", "record_version"]
	# 初始化類，建立檔案，生成資料庫說明文件
    dbInfoGenerator = DbInfoGenerator(host, port, user, password, db_name, charset)
    dbInfoGenerator.create_file(excel_path)
    dbInfoGenerator.gen_db_table_info_skip_col(db_name, excel_path, col_names_to_skip)

結果如下：

表的欄位說明已輸出到 excel 文件中，對應的欄位也已過濾。只是格式不是很好看，因此，有需要的可以用 openpyxl 對 excel 文件的格式進行設定即可。

6 （可選）設定 excel 文件格式

如果需要對 excel 文件進行格式設定，以下是我的一個基本格式設定，有需要的可以參考一下，製作適合自己的文件格式。格式的設定思路主要如下：

• 設定各列的寬度
• 遍歷 excel 表每一行，如果是表名，則合併為一行作為表頭，設定表頭格式為加粗、加黑色邊框、居中對齊、填充背景色。
• 如果表欄位資訊內容，則設定黑色邊框即可。

def set_file_format(self, file_path):
    # 設定表格式

    if not os.path.exists(file_path):
        print("檔案不存在，不處理")
        return
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook.worksheets[0]
    # 設定各列寬
    sheet.column_dimensions["A"].width = 16
    sheet.column_dimensions["B"].width = 10
    sheet.column_dimensions["C"].width = 20
    sheet.column_dimensions["D"].width = 40

    # 設定表名格式
    max_row = sheet.max_row
    for i in range(1, max_row + 1):
        col1_value = sheet.cell(i, 1).value
        col2_value = sheet.cell(i, 2).value
        # 首列有資料，第2列無資料，則為表名
        if col1_value and not col2_value:
            # 合併表名
            sheet.merge_cells(start_row=i, start_column=1, end_row=i, end_column=4)
            # 加粗字型
            font = Font(name="微軟雅黑", size=12, bold=True, italic=False, color="000000")
            # 黑色邊框
            side_style = Side(style="thin", color="000000")
            border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)
            # 居中對齊
            cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            # 填充背景色
            p_fill = PatternFill(fill_type="solid", fgColor="BFBFBF")
            # 表名cell格式
            for j in range(1, 5):
                sheet.cell(i, j).font = font
                sheet.cell(i, j).border = border
                sheet.cell(i, j).alignment = cell_alignment
                sheet.cell(i, j).fill = p_fill
         # 若首列和第2列都有資料，則是表內容
         if col1_value and col2_value:
            # 黑色邊框
            side_style = Side(style="thin", color="000000")
            border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style)
            # 表名cell格式
            for j in range(1, 5):
                sheet.cell(i, j).border = border
    # 儲存文件
    workbook.save(file_path)

當生成資料庫說明文件後，呼叫此函式，即可修改其文件格式，效果如下：

7 總結

本文主要針對資料庫說明文件（資料字典）的自動化生成進行講解。通過使用 SQL 讀取資料庫表及欄位元資訊，然後輸出到 excel 文件的思路，以 python 的實現方式完成自動生成文件功能。如果你還在手工生成資料庫說明文件，可以試試這種方法，一定讓你效率大增。希望可以幫助到有需要的人。如果想看完整的程式碼，可到我 github 地址中檢視：https://github.com/mianshenglee/my-example/tree/master/python/tool-gen-db-doc

根據本文實現的思路，最後可以留幾個思考題給大家，想想如何做：

• 不使用 python ，用其它你熟悉的語言來實現此功能。
• 如何只需要生成指定表的欄位資訊或者過濾指定表，怎麼做？
• 資料庫表名一般都會有字首或字尾，能否根據字首或字尾來過濾生成或者過濾，怎麼做？
• 本文是生成 excel 文件，如果需要生成 word 、html 、md 、pdf 等格式的文件，怎麼做？

參考資料

• openpyxl官方文件: https://openpyxl.readthedocs.io/
• pymysql官方文件: https://pymysql.readthedocs.io/en/latest/

往期文章

• Python 處理 Excel 檔案
• python基本操作-檔案、目錄及路徑
• MinIO 的分散式部署
• 利用MinIO輕鬆搭建靜態資源服務
• 搞定SpringBoot多資料來源(3)：引數化變更源
• 搞定SpringBoot多資料來源(2)：動態資料來源
• 搞定SpringBoot多資料來源(1)：多套源策略
• java開發必學知識:動態代理
• 2019 讀過的好書推薦

我的公眾號（搜尋Mason技術記錄），獲取更多技術記錄：