如何在 Python 中将 Sql Server 结果导出到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18977387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Export Sql Server Result to Excel in Python
提问by user2768851
I'm working with our own framework
我正在使用我们自己的框架
I want to export sql server result set (queried one) to excel using ion python.
我想使用ion python将sql server结果集（查询一个）导出到excel。
Inside the python file I can call that sql query result.
在 python 文件中，我可以调用该 sql 查询结果。
Then I want to export that query result in to excel with headers.
然后我想将该查询结果导出到带有标题的 excel 中。
That means that once I call the python file, that query result should be saved as an excel spreadsheet.
这意味着一旦我调用 python 文件，该查询结果应保存为 excel 电子表格。
Please any one can help me to make this correctly?
请任何人都可以帮助我正确地做到这一点？
回答by Talvalin
Take a look at the following modules:
看看以下模块：
If you're still having issues after installing those modules and reading through the documentation, then you are welcome to request further assistance by posting your script in a new question, along with any error messages seen. :)
如果您在安装这些模块并通读文档后仍然遇到问题，那么欢迎您通过在新问题中发布您的脚本以及看到的任何错误消息来请求进一步帮助。:)
回答by Oli
I've had great success with openpyxlbefore and their tutorialis simple but comprehensive enough to get going without tripping over what gotchas there are.
回答by moarCoffee
I would do this with pyodbc(for interacting with the db) and pandas(for manipulating data and exporting as spreadsheet).
我会用pyodbc（用于与数据库交互）和pandas（用于操作数据和导出为电子表格）来做到这一点。
Here's a simple example:
这是一个简单的例子：
import pyodbc
import pandas as pd
cnxn = pyodbc.connect(< db details here >)
cursor = cnxn.cursor()
script = """
SELECT * FROM my_table
"""
cursor.execute(script)
columns = [desc[0] for desc in cursor.description]
data = cursor.fetchall()
df = pd.DataFrame(list(data), columns=columns)
writer = pd.ExcelWriter('foo.xlsx')
df.to_excel(writer, sheet_name='bar')
writer.save()
Read through the docs and you'll be fine.
通读文档，你会没事的。
== UPDATE ==
== 更新 ==
For more recent versions of pandas this is a better way to process the SQL query:
对于更新版本的 Pandas，这是处理 SQL 查询的更好方法：
import pyodbc
import pandas as pd
cnxn = pyodbc.connect(< db details here >)
script = """
SELECT * FROM my_table
"""
df = pd.read_sql_query(script, cnxn)
回答by Manish Chaudhary
import pandas as pd
import xlsxwriter
import pyodbc
conn = pyodbc.connect('Driver={SQL Server}; Server=ServerIP; uid=UID; pwd=Password; Trusted_Connection=No;')
with pd.ExcelWriter("Output.xlsx", engine="xlsxwriter", options = {'strings_to_numbers': True, 'strings_to_formulas': False}) as writer:
try:
df = pd.read_sql("Select * from Orders", conn)
df.to_excel(writer, sheet_name = "Sheet1", header = True, index = False)
print("File saved successfully!")
except:
print("There is an error")
