Python exports SqlServerl data dictionary as excel

Define three methods

1. Define a getData() method to get data
2. Define a method to export excel table exportSqlServer()
3. Define a method to get type of (), which is used to identify the queried data

Here's the code

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


def getData():
    connect= pymssql.connect(host, 'sa', Password, Database name);
    cur = connect.cursor();
    query = '''
 SELECT
     tableName       =  D.name  , # I merge cells according to the table here. If I use the case wheel end structure, I cannot merge cells. There will be errors
     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'    --If only the specified table is queried,Add this condition
 Order By
     A.id,A.colorder'''


    cur.execute(query)
    data = cur.fetchall()  # Tuple type
    return data

def exportExcel(name):
    data = getData()
    myExcel = xlwt.Workbook('encoding=utf-8')
    # Define the width of the table
    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

    # Set center
    a1 = xlwt.Alignment()
    a1.horz = 0x02
    a1.vert = 0x01
    style = xlwt.XFStyle()  # Assign style as XFStyle as initialization style
    style.alignment = a1

    today = datetime.today()  # Get the current date and get a datetime object, such as: (2019, 7, 2, 23, 12, 23, 424000)
    today_date = datetime.date(today)  # Only the date of the acquired datetime object is taken as: 2019-7-2
    items = ['Data sheet', 'Table name', 'Field ordinal number', 'field', 'type', 'Bytes occupied', 'length', 'Decimal point', 'Is it empty?', 'Primary key or not', 'Default value','Notes']
    for col in range(len(items)):
        sheet1.write(0, col, items[col])
    # Merge the name of the second column, get the data of the first column from content, [("Choleen","xxx"), ()]
    first_col = []
    for i in range(len(data)):
        first_col.append(data[i][0])
    print("first_col:", first_col)
    # Remove duplicate column data and keep the order unchanged
    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)  # Calculate the number of duplicate elements
        mergeRow = row + count - 1  # The number of uplink after consolidation,
        sheet1.write_merge(row, mergeRow, 0, 0, i, style)  # First column
        sheet1.write_merge(row, mergeRow, 1, 1, i, style)
        row = mergeRow + 1  # Write from next line

    # Get the second element in data[i], and write it circularly
    for row in range(len(data)):
        for col in range(1, len(data[row])):
            result = data[row][col]
            str = typeof(result) # Acquisition type
            if str == None: # Unrecognized type, conversion required
                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("This is sqlServer Exported data dictionary");
    # response = chardet.detect(b'\xe7\x94\xa8\xe6\x88\xb7\xe8\xa1\xa8')
    # print(response)
    exportExcel("user surface")
In the process of writing code, there is a Chinese disorder. Python 3 will automatically convert unicode. Let's take a look at the conversion process:
     UTF-8/GBK --> decode Decode --> Unicode
  Unicode --> encode Code --> GBK / UTF-8 

The code here is unicode. To convert it to plaintext, you need the decode method. It can only be in unicode format. If it is int or str, an error will be reported

Plaintext -- encode -- "Unicode --" gbk, utf-8
 Plaintext -- decode -- Unicode -- gbk, utf-8

so, that's it. Complete the operation.

Tags: Python Excel Database encoding

Posted on Wed, 18 Mar 2020 07:59:55 -0700 by stallingjohn