mysql example generate mysql dict (snippet)

Python mysql example 'generate mysql dict'

Modules used in program:

• import mysql.connector
• import sys

python generate mysql dict

Python mysql example: generate mysql dict

import sys
import mysql.connector

if len(sys.argv) < 5:
    print("Usage: python3 generate_mysql_dict.py host dbname user password")
    sys.exit()

mydb = mysql.connector.connect(
    host=sys.argv[1],
    database=sys.argv[2],
    user=sys.argv[3],
    passwd=sys.argv[4]
)

database=sys.argv[2]

mycursor = mydb.cursor()
sql = 'show tables' 
mycursor.execute(sql)

myresult = mycursor.fetchall();
sqlList = [] 
for x in myresult:
    tmp = {}
    tmp["TABLE_NAME"] = x[0]
    sqlList.append(tmp)

for k, v in enumerate(sqlList):
    sql = """SELECT TABLE_COMMENT
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_name = '{1}' AND table_schema = '{0}'
""".format(database, v["TABLE_NAME"])

    mycursor.execute(sql)
    tableresult = mycursor.fetchall();
    for r in tableresult:
        sqlList[k]['TABLE_COMMENT'] = r[0]
        sqlList[k]['COLUMN'] = []

    sql = """SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, EXTRA, COLUMN_COMMENT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = '{1}' AND table_schema = '{0}'
""".format(database, v["TABLE_NAME"])

    mycursor.execute(sql)
    tableresult = mycursor.fetchall();
    for r in tableresult:
        sqlList[k]['COLUMN'].append(r)

output = ''; 
for k,v in enumerate(sqlList):
    output += """## 表名：{0} {1}
|字段名|数据类型|默认值|允许非空|自动递增|备注|
| ---- | ---- | ---- | ---- | ---- | ---- |
""".format(v['TABLE_NAME'], v['TABLE_COMMENT'])

    for f in v['COLUMN']:
        output += '|' + f[0]
        output += '|' + f[1]
        output += '|' + str(f[2])
        output += '|' + f[3]
        output += '|' + ('是' if f[4] == 'auto_increment' else ' ')
        output += '|' + f[5] + '|' + "\n";

print("""---
title: {0}
date: {1}
---
{2}
------
总共： {3}个数据表
""".format('数据字典'+database, database, output,str(len(sqlList))))

Python links

• Learn Python: https://pythonbasics.org/
• Python Tutorial: https://pythonprogramminglanguage.com