python產生資料庫中所有表的DESC描述

標籤：des   style   blog   io   color   ar   os   sp   for   

 

       在資料庫設計完成之後， 常常需要在 wiki 或其他文檔中儲存一份資料庫中所有表的 desc 描述， 尤其是每個欄位的含義和用途。 手動去產生自然是不可取的。 因此， 我編寫了一個簡單的 python 程式，可以自動產生資料庫中所有表的 desc 描述， 並以可讀格式輸出。

 

# -*- coding: utf-8 -*-# -------------------------------------------------------------------------------# Name:          db_tables_descs.py# Purpose:       generate the tables that describe the meanings of fields in db## Author:       qin.shuq## Created:      2014/11/17# Output:       desc.txt#               recording the tables that describe the meanings of fields in db#-------------------------------------------------------------------------------#!/usr/bin/env pythonimport MySQLdbglobalFieldDescs = (‘Field‘, ‘Type‘, ‘Null‘, ‘Key‘, ‘Default‘, ‘Extra‘)globalDescFile = ‘desc.txt‘conflictedWithMysqlKeywords = set([‘group‘])fieldDescMapping = {    ‘id‘:         ‘唯一標識‘,    ‘is_deleted‘: ‘是否邏輯刪除‘,    ‘status‘:     ‘實體狀態‘,    ‘type‘:       ‘實體類型‘,    ‘priority‘:   ‘優先順序‘,    ‘password‘:   ‘密碼‘,    ‘ip‘:         ‘ip 地址‘,    ‘mac‘:        ‘mac 地址‘,    ‘protocol‘:   ‘訪問協議‘,    ‘user_id‘:    ‘使用者唯一標識‘}class DB(object):    def __init__(self):        self.conn = MySQLdb.connect(db=‘mysql‘,host=‘127.0.0.1‘,user=‘root‘,passwd=‘123456‘)    def obtainDB(self):        return self    def query(self, sql):        cursor = self.conn.cursor()        cursor.execute(sql)        result =  cursor.fetchall()        cursor.close()        return list(result)def formatCols(fieldDesc):    return  "%-16s %-24s %-5s %-8s %-8s %-30s" % fieldDescdef withNewLine(astr):    return astr + ‘\n‘def commonFieldsProcess(fieldDescList):    fieldName = fieldDescList[0]    fieldDesc = fieldDescMapping.get(fieldName)    desclen =   len(fieldDescList)    if fieldDesc is None:        if fieldName.startswith(‘gmt_c‘):            fieldDesc = ‘建立時間‘        elif fieldName.startswith(‘gmt_m‘):            fieldDesc = ‘修改時間‘        else:            fieldDesc = fieldDescList[desclen-1]    fieldDescList[desclen-1] = fieldDescdef formatF(fieldDescTuple):    fieldDescList = list(fieldDescTuple)    fieldLen = len(fieldDescList)    for i in range(fieldLen):        if fieldDescList[i] is None:            fieldDescList[i] = ‘NULL‘        else:            fieldDescList[i] = str(fieldDescList[i])    commonFieldsProcess(fieldDescList)    return formatCols(tuple(fieldDescList))def format(tableDesc):    desc = ‘‘    for fieldDescTuple in tableDesc:        desc += withNewLine(formatF(fieldDescTuple))    return descdef descDb(givenDb):    tablesRet = givenDb.query("show tables;")    print tablesRet    tableNames = [table[0] for table in tablesRet]    desc = ‘‘    for tablename in tableNames:        if tablename in conflictedWithMysqlKeywords:            tablename = ‘`‘ + tablename + ‘`‘        descSql = "desc " + tablename        tableDesc = givenDb.query(descSql)        desc += withNewLine(tablename)        desc += withNewLine(formatCols(globalFieldDescs)).decode(‘utf-8‘)        desc += withNewLine(format(tableDesc)).decode(‘utf-8‘)        desc += withNewLine(‘‘).decode(‘utf-8‘)    return descdef main():    descFile = open(globalDescFile, ‘w‘)    db = DB()    database = db.obtainDB()    desc = descDb(database)    descFile.write(desc.encode(‘utf-8‘))    descFile.close()if __name__ == ‘__main__‘:    main()

 

python產生資料庫中所有表的DESC描述