Python導出SqlServerl數據字典為excel

定義三個方法

1.定義一個獲取數據的getData()方法
2.定義一個導出excel表的方法exportSqlServer()
3.定義一個獲取類型typeof()的方法，用作查詢出來的數據被識別

下麵直接展示代碼

from datetime import datetime
import os
import pymssql as pymssql
import xlwt


def getData():
    connect= pymssql.connect(host, 'sa', 密碼, 資料庫名);
    cur = connect.cursor();
    query = '''
 SELECT
     tableName       =  D.name  , # 我合併單元格是按照這裡的表的重覆合併的，若用case whern end 結構，則不能合併，會出錯
     tableIntroduce     =  isnull(F.value,''),
     sort   = A.colorder,
     fieldName     = A.name,
     catogary       = B.name,
     bytes = A.Length,
     lengths       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
     scales   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
     isOrNotNull     = Case When A.isnullable=1 Then '√'Else '' End,
		   primarays       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
     defauts     = isnull(E.Text,''),
		  annotations   = isnull(G.[value],'')
 FROM
     syscolumns A
 Left Join
     systypes B
 On
     A.xusertype=B.xusertype
 Inner Join
     sysobjects D
 On
     A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'
 Left Join
     syscomments E
 on
     A.cdefault=E.id
 Left Join
 sys.extended_properties  G
 on
     A.id=G.major_id and A.colid=G.minor_id
 Left Join

 sys.extended_properties F
 On
     D.id=F.major_id and F.minor_id=0
     --where d.name='OrderInfo'    --如果只查詢指定表,加上此條件
 Order By
     A.id,A.colorder'''


    cur.execute(query)
    data = cur.fetchall()  # 元組類型
    return data

def exportExcel(name):
    data = getData()
    myExcel = xlwt.Workbook('encoding=utf-8')
    # 定義表的寬
    sheet1 = myExcel.add_sheet(name, cell_overwrite_ok=True)
    sheet1.col(0).width = 300 * 20
    sheet1.col(1).width = 400 * 20
    sheet1.col(2).width = 100 * 20
    sheet1.col(3).width = 300 * 20
    sheet1.col(4).width = 256 * 20
    sheet1.col(5).width = 180 * 20
    sheet1.col(6).width = 180 * 20
    sheet1.col(7).width = 100 * 20
    sheet1.col(8).width = 100 * 20
    sheet1.col(9).width = 100 * 20
    sheet1.col(10).width = 180 * 20
    sheet1.col(11).width = 800 * 20

    # 設置居中
    a1 = xlwt.Alignment()
    a1.horz = 0x02
    a1.vert = 0x01
    style = xlwt.XFStyle()  # 賦值style為XFStyle為初始化樣式
    style.alignment = a1

    today = datetime.today()  # 獲取當前日期，得到一個datetime對象如：(2019, 7, 2, 23, 12, 23, 424000)
    today_date = datetime.date(today)  # 將獲取到的datetime對象僅取日期如：2019-7-2
    items = ['數據表', '表名', '欄位序號', '欄位', '類型', '占用位元組數', '長度', '小數點', '是否為空', '是否為主鍵', '預設值','註釋']
    for col in range(len(items)):
        sheet1.write(0, col, items[col])
    # 合併第二列的name,從content獲取第一列數據，[("Choleen","xxx"),()]
    first_col = []
    for i in range(len(data)):
        first_col.append(data[i][0])
    print("first_col:", first_col)
    # 去掉重覆的列數據，並順序不變
    nFirst_col = list(set(first_col))
    nFirst_col.sort(key=first_col.index)
    print("nFirst_col:", nFirst_col)
    row = 1
    for i in nFirst_col:
        count = first_col.count(i)  # 計算重覆的元素個數
        mergeRow = row + count - 1  # 合併後的上行數，
        sheet1.write_merge(row, mergeRow, 0, 0, i, style)  # 第一列
        sheet1.write_merge(row, mergeRow, 1, 1, i, style)
        row = mergeRow + 1  # 從下一行開始寫入

    # 獲取data[i]中的第二個元素，迴圈寫入
    for row in range(len(data)):
        for col in range(1, len(data[row])):
            result = data[row][col]
            str = typeof(result) # 獲取類型
            if str == None: # 不能識別的類型，需要轉換
                result = result.decode('utf-8')
            sheet1.write(row + 1, col, result, style)

    fileName = name + '.xls'
    rootPath = os.path.dirname(os.path.abspath('ExportSqlServer.py')) + '\\'
    print(rootPath)
    flag = os.path.exists(rootPath + fileName)
    if flag:
        os.remove(rootPath + fileName)
        myExcel.save(fileName)
    else:
        myExcel.save(fileName) 


def typeof(variate):
    type = None
    if isinstance(variate, int):
        type = "int"

    elif isinstance(variate, str):
        type = "str"
    elif isinstance(variate, float):
        type = "float"
    elif isinstance(variate, list):
        type = "list"
    elif isinstance(variate, tuple):
        type = "tuple"
    elif isinstance(variate, dict):
        type = "dict"
    elif isinstance(variate, set):
        type = "set"
    return type

if __name__ == '__main__':
    print("這是sqlServer導出的數據字典");
    # response = chardet.detect(b'\xe7\x94\xa8\xe6\x88\xb7\xe8\xa1\xa8')
    # print(response)
    exportExcel("user表")

在編寫代碼過程中出現了，中文亂碼。python3會自動轉換未unicode，我們來看下轉換過程：

     UTF-8/GBK --》 decode 解碼 --》 Unicode
　　Unicode --》 encode 編碼 --》 GBK / UTF-8 

　這裡的代碼是Unicode,要轉換成明文，就需要decode方法，只能是unicode的格式才能，若是int，str類型則會報錯 

明文 -- encode --》Unicode--》gbk，utf-8
明文 《-- decode -- Unicode 《-- gbk，utf-8

so，這樣就可以了，完成操作。