python 数据字典表导出markdown csv等格式

python 数据字典表导出markdown csv等格式

import pymysql
import importlib
import csv

def remove_newline(text):
    """
    去除文本中的换行符号
    """
    return text.replace("\r", "").replace("\n", "")



def generate_table(database_name, table_name):
    """
    生成数据库字典表
    :param database_name: 库名
    :param table_name: 表名
    :return:
    """
    importlib.reload(sys)
    # 使用前修改配置
    conn = pymysql.connect(
        host='127.0.0.1',
        port='3306',
        user='root',
        password='root',
        use_pure=True
    )
    cursor = conn.cursor()

    cursor.execute(
        "SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE table_type='BASE TABLE' AND TABLE_SCHEMA='%s'" % database_name
    )

    tables = cursor.fetchall()
    markdown_table_header = """\n\n\n### %s (%s) \n| 序号 | 字段名称 | 数据类型 | 是否为空 | 字段说明 |\n| :--: |----| ---- | ---- | ---- |\n"""
    markdown_table_row = """| %s | %s | %s | %s | %s |"""

    #   记录到markdown
    with open(database_name + '.' + table_name + '.md', 'w') as f:
        for table in tables:
            if table_name != None:
	            if table[0] != table_name:
                   continue
            cursor.execute(
                "SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'" % (
                    database_name, table[0]
                )
            )

            tmp_table = cursor.fetchall()
            print(tmp_table)
            p = markdown_table_header % (table[0], remove_newline(table[1]))
            for col in tmp_table:
                p += (remove_newline(markdown_table_row % col) + "\n")
            print(p)
            f.writelines(p)

    #   记录到csv
    filename = database_name + '.' + table_name + '.csv'
    # with open(filename, 'w', "a+", newline='', encoding='gb18030', errors='ignore') as file:
    with open(filename, 'w') as file:
        csv_file = csv.writer(file)
        csv_file.writerows([["序号", "字段名称", "数据类型", "是否为空", "字段说明"]])

        for table in tables:
            if table_name != None:
	            if table[0] != table_name:
                   continue
            cursor.execute(
                "SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'" % (
                    database_name, table[0]
                )
            )
            tmp_table = cursor.fetchall()
            # print(tmp_table)
            if tmp_table:
                with open(filename, "a+", newline='', encoding='gb18030', errors='ignore') as file:
                    csv_file = csv.writer(file)
                    csv_file.writerows(tmp_table)


if __name__ == '__main__':
	generate_table('database_name', 'table_name')

参考链接：https://blog.csdn.net/wxb666666/article/details/105202817/