我有一台 SQL Server,在上面有一个表,我想使用 Python 去获取这个表,并进行赛选,然后将赛选后的内容写入到 Excel 中
这是我的代码:
import pyodbc
import xlwt
data=xlwt.Workbook()
table1=data.add_sheet('sheet1')
table2=data.add_sheet('sheet2')
DB_Host = "192.168.1.10"
DB_Name = "testdb"
DB_User = "admin"
DB_Password = "P@ssw0rd"
print("Connecting to database using pyodbc...")
db = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=' + DB_Host + '; DATABASE=' + DB_Name + '; UID=' + DB_User + '; PWD=' + DB_Password)
print("Succesfully Connected to database using pyodbc!")
cursor = db.cursor()
cursor.execute('SELECT * FROM dbo.Data_Wash_ALM')
for row in cursor:
try:
# print(row[9])
if "lu" in row[9]: # 这是我要筛选的
print(row) # 筛选后的数据,可以顺利 print 出来
index = 0
for col,item in enumerate(row):
table1.write(index, col, item)
index += 1
except TypeError:
print("Type Error")
data.save("data.xls")
# db.close()
我现在想将 print 的数据写入到 Excel 中,但就是写入不了,不知道是哪里出了问题。
print 的内容类似:
(59, 21054, 9789, 5975, 'Windows 10', IP(192.168.1.95), uptime(1900), '2', None, 'lu', 'lu@test.com', 'Passed', 'Passed')
index = 0
for row in cursor:
try:
# print(row[9])
if "lu" in row[9]: # 这是我要筛选的
print(row) # 筛选后的数据,可以顺利 print 出来
for col,item in enumerate(row):
table1.write(index, col, item)
index += 1
except TypeError:
print("Type Error")
你應該是永遠只寫入最後一行資料吧?
index放錯地方啦。
然後if的地方建議用sql去寫:
cursor.execute('SELECT * FROM dbo.Data_Wash_ALM where ...')
数据太长,xlwt 只支持到 65536,我改用了 openpyxl,代码现在是这样的:
import pyodbc
from openpyxl import Workbook
data = Workbook()
sheet1 = data.active
sheet1.title = "table1"
# sheet2 = data.create_sheet(title="table2")
DB_Host = "192.168.1.10"
DB_Name = "testdb"
DB_User = "admin"
DB_Password = "P@ssw0rd"
print("Connecting to database using pyodbc...")
db = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=' + DB_Host + '; DATABASE=' + DB_Name + '; UID=' + DB_User + '; PWD=' + DB_Password)
print("Succesfully Connected to database using pyodbc!")
cursor = db.cursor()
cursor.execute('SELECT * FROM dbo.Data_Wash_ALM')
for row in cursor:
try:
# print(row[9])
if "lu" in row[9]: # 这是我要筛选的
print(row) # 筛选后的数据,可以顺利 print 出来
sheet1.append(row)
except TypeError:
print("Type Error")
data.save("data.xls")
又出现写入不了 Excel
尝试单独写入一行数据:
sheet.append((59, 21054, 9789, 5975, 'Windows 10', IP(192.168.1.95), uptime(1900), '2', None, 'lu', 'lu@test.com', 'Passed', 'Passed'))
提示加粗的地方,语法错误
sheet.append((59, 21054, 9789, 5975, 'Windows 10', **IP(192.168.1.95)**, uptime(1900), '2', None, 'lu', 'lu@test.com', 'Passed', 'Passed'))
SyntaxError: invalid syntax
as900 要寫入excel的資料都轉字串看看?
已经解决了,我将结果转换成 list ,然后再写入到 Excel