欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Python导出SqlServerl数据字典为excel

程序员文章站 2022-06-27 21:44:19
定义三个方法 1.定义一个获取数据的getData()方法2.定义一个导出excel表的方法exportSqlServer()3.定义一个获取类型typeof()的方法,用作查询出来的数据被识别 下面直接展示代码 from datetime import datetime import os imp ......

定义三个方法

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,这样就可以了,完成操作。