原 Mysql元数据导出为Mardown格式数据字典

最近在整理数据库数据字典，N多表需要整理， 太费功夫，写一个脚本从MySQL元数据直接生成Markdown文件。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
from DBUtils.PooledDB import PooledDB

host = 'mysql_host'
user = 'test'
password = 'test'
db = 'test'
port = 3306
table_schema = 'test'
pool = PooledDB(MySQLdb, 2, host=host, user=user, passwd=password, db=db, port=port, charset="utf8")


def export_metadata():
    conn = pool.connection()
    cursor = conn.cursor()
    write_file = open("metadata.md", "w")
    try:
        query_table_sql = 'SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = %s'
        cursor.execute(query_table_sql, (table_schema,))
        tables = cursor.fetchall()
        if tables:
            for table_item in tables:
                table_name = table_item[0]
                index_dict = dict()
                index_list_tmp = list()
                exsit_mul = False
                query_index_sql = "show index from " + table_name
                cursor.execute(query_index_sql)
                indexs = cursor.fetchall()
                if indexs:
                    for index in indexs:
                        if index[2] in index_dict:
                            index_dict[index[2]].append(index[4].encode("utf-8"))
                        else:
                            index_list = list()
                            index_list.append(index[4].encode("utf-8"))
                            index_dict[index[2]] = index_list
                    for index_name in index_dict:
                        if len(index_dict[index_name]) == 1:
                            index_list_tmp.append(index_dict[index_name][0])
                        elif len(index_dict[index_name]) > 1:
                            exsit_mul = True
                md_table_head = '字段名 | 数据类型 | 默认值 | 允许非空 | 是否递增 | 是否索引 | 备注\n'
                md_table_head2 = '--- | --- | --- | --- | --- | --- | --- | --- \n'
                md_table_name = "###" + table_name
                write_file.writelines(md_table_name + '\n\n')
                write_file.writelines('#####表信息' + '\n\n')
                write_file.writelines(md_table_head)
                write_file.writelines(md_table_head2)
                query_column_sql = "SELECT  COLUMN_NAME, COLUMN_TYPE, CASE WHEN COLUMN_DEFAULT is null THEN 0 ELSE COLUMN_DEFAULT END, CASE IS_NULLABLE WHEN 'Yes' THEN '是' ELSE '否' END IS_NULLABLE,  CASE EXTRA WHEN 'auto_increment' THEN '是' ELSE ' ' END EXTRA,  COLUMN_COMMENT  " \
                                   "FROM  information_schema.`COLUMNS`  WHERE  TABLE_NAME = %s  AND TABLE_SCHEMA = %s"
                cursor.execute(query_column_sql, (table_name,table_schema,))
                columns = cursor.fetchall()
                if columns:
                    column_str = '%s | %s | %s | %s | %s | %s | %s \n'
                    for column in columns:
                        index_tmp = ""
                        if column[0] in index_list_tmp:
                            index_tmp = "是"
                        md_column = str(column_str % (column[0].encode("utf-8"), column[1].encode("utf-8"), column[2].encode("utf-8"), column[3].encode("utf-8"), column[4].encode("utf-8"), index_tmp, column[5].encode("utf-8")))
                        write_file.writelines(md_column)
                if index_dict and exsit_mul:
                    write_file.writelines('\n#####联合索引信息' + '\n\n')
                    md_index_head = '联合索引名 | 索引字段 \n'
                    md_index_head2 = '--- | ---  \n'
                    write_file.writelines(md_index_head)
                    write_file.writelines(md_index_head2)
                    index_str = '%s | %s \n'
                    for index_name in index_dict:
                        if len(index_dict[index_name]) > 1:
                            md_index = str(index_str % (index_name, '(' + ','.join(index_dict[index_name]) + ')'))
                            write_file.writelines(md_index)

                write_file.writelines('\n------\n\n')
    except Exception, e:
        print e
    write_file.close()
    if cursor:
        cursor.close()
    if conn:
        conn.close()

if __name__ == '__main__':
    export_metadata()

原文链接：Mysql元数据导出为Mardown格式数据字典，转载请注明来源！