悬臂货架,惹火小魔仙,红安房价
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，这样就可以了，完成操作。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
新手学习Python2和Python3中print不同的用法
Python基于os.environ从windows获取环境变量
网友评论