定義三個方法
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 = https://www.cnblogs.com/Choleen/p/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，這樣就可以了，完成操作，
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/175006.html
標籤：Python
上一篇：Django 模型筆記