基于Python实现SQL SEVER与Excel间数据的批量导出
10.16638/jki.1671-7988.2021.07.057
基于Python实现SQL SEVER与Excel
间数据的批量导出
李娜，胡晓峰，徐国强
（中国汽车技术研究中心有限公司中汽数据（天津）有限公司，天津300300）
摘要：在SQL sever数据库管理过程中，将SQL sever中数据导出到Excel数据表提供给用户，更有利于用户使用和处理数据，同时也能更好地保障数据安全。文章首先对Python操作数据库进行了简单的介绍，并进一步分析了利用SQL sever和Excel自带的导出导入功能进行数据转换时存在的问题，为此，提出利用Python将SQL sever的数据批量导出到Excel表格的方法，并使用Python模块XlsxWriter设置Excel文件格式，来提升数据批量转换处理效率。
关键词：Python；SQL SEVER；Excel；数据导出
中图分类号：U461.99 文献标识码：A 文章编号：1671-7988(2021)07-181-05
The batch export of data between sql sever and excel is realized based on python
Li Na, Hu Xiaofeng, Xu Guoqiang
( China Automotive Technology & Research Center Co., Ltd. Automotive Data
Of China (TianJin) Co., Ltd, Tianjin 300300 )
Abstract: In the management of SQL Sever , exporting the data from SQL Sever to Excel has become an effective way for users to use and process the data. This paper has carried on the simple to the Python database operation is introduced, and further analyzes the use of SQL sever and Excel's built-in export import function to convert the data problems, therefore, this paper puts forward using Python to SQL sever data batch export to Excel method, and use the Python module XlsxWriter set Excel file format, to improve the data batch conversion and processing efficiency.
Keywords: Python; SQL SEVER; Excel; Export data
CLC NO.: U461.99 Document Code: A Article ID: 1671-7988(2021)07-181-05
前言
随着信息化管理系统的快速发展，数据库系统也日益成熟。SQL sever作为一种分布式的关系型数据库管理系统，具有操作便捷、功能强大以及管理灵活等特点，成为备受欢迎的数据库系统。Excel作为一种可以简单进行数据管理的工具，具有便于日常数据处理、统计分析计算以及制作报表等优势，备受数据使用者的青睐，因此，SQL sever与Excel之间的数据转换操作需求日益增加。将SQL sever数据库中数据导出到Excel的方法有很多，其中SQL sever自带的导出功能和网格结果查询功能就已经为行数和字段较少的数据转换导出工作提供了有力的支撑，但是对于所需导出行数和字段较多的大量数据而言，SQL sever自带的导出功能和网格结果查询功能导出速度极慢，且无法进行批量操作，导致工作效率较低[1]。
作者简介：李娜，硕士学位，助理工程师，就职于中国汽车技术研
究中心有限公司中汽数据（天津）有限公司，研究方向：数据库。打开excel很慢
181
汽车实用技术
182本文以Python为开发语言，anaconda为操作平台，实现
了SQL sever数据库向Excel文件的批量导出，提升数据管理员的工作效率。
1 Python操作数据库技术简介
Python是一个结合了解释性、互动性、编译性和面向对象的高层次脚本语言。Python相对于其他经常使用英文关键字以及标点符号的计算机语言而言，具有更强可读性的设计，且语法结构更具有特，同时，Python具有易于学习、可扩展、易于阅读、兼容性好、易于维护、可移植、可嵌入等特点。
Python能提供所有主流数据库的接口，Python 标准数据库接口为Python DB-API，Python DB-API为开发人员提供了简单的、标准化的数据库应用编程接口。Python 数据库接口支持Microsoft SQL Server、Oracle、MySQL、mSQL、PostgreSQL、GadFly、Informix、Interbase、Sybase等多种数据库，可以根据自己所使用的数据库来应用接口，DB-API 是一个规范，不同的数据库需要下载不同的DB-API模块。DB-API为便于为各大底层数据库系统和不同数据库接口程序提供可以一致的访问接口，定义了一系列必需的对象和数据库存取方式。Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各大底层数据库。本文将研究Python操作SQL sever数据库，将数据库中数据导出到Excel数据表中[2]。
2 传统数据导出功能存在的问题
2.1 SQL Sever自带导出功能
图1 SQL Sever数据库连接界面
在SQL Sever中自带数据导出功能，打开如图1所示的SQL Sever导出界面，数据源选择SQL Sever Native Client 11.0选项，输入服务器名称，在用户名和密码框分别填入SQL Sever账号和密码后执行下一步，进入如图2所示界面，在目标框选择Microsoft Excel，单击浏览选择所要导出的Excel 文件，点击下一步进入如图3所示指定表复制或查询界面，如果将库中表或视图数据全部导出到Excel，则选择“复制一个或多个表或视图的数据”按钮，如果需要利用SQL语句查询出数据结果后导出到Excel文件，则选择“编写查询以指定要传输的数据”按钮，点击下一步输入SQL语句，执行后续操作即可完成SQL Sever中数据批量导出到Excel数据表的操作[3]。
图2 Excel文件导出界面
图3 选择导出数据方式界面
经验证此方法只适用于行数较少的数据导出，当数据量较大时，导出到Excel数据表的过程中运行速度会很慢，且会出现中断导致导出数据缺失。
2.2 SQL Sever网格结果查询功能
通过SQL Sever中自带查询设置将查询结果以文本格式、以网格显示、保存到csv或txt文本这三种形式进行显示。在SQL Sever数据查询过程中为方便展示结果以及方便操作，通常以网格显示数据结果，再将网格显示数据复制到Excel表格中[4]。
此种方法对于行数较少的数据查询及导出非常方便，但是对于数据量较大的查询，将结果粘贴到Excel的过程中会出现如图4所示错误，因此不适用于数据量较大的数据导出。
图4 SQL Sever网格结果数据复制异常
李娜 等：基于Python 实现SQL SEVER 与Excel 间数据的批量导出
183
2.3 Excel 导入SQL Sever 数据源功能
当数据量稍大时，可以通过Excel 的自其他来源功能，如图5所示，连接SQL sever 数据库，如图6所示，选定SQL sever 中所需要导出的数据表，将表中全部数据导出到Excel 表格中，如果需要导出表中部分数据，可以先利用SQL sever 语句将数据导出至临时表，再将临时表中数据导出到Excel 表格中。此种方法不适合超出Excel 行数上限1048576行的数据导出，会失败或导致数据缺失，且效率极慢。
图5  Excel 连接SQL Sever 数据库
图6  选择所需导入Excel 的表
由上可知，在不借助任何工具，利用SQL Sever 或者Excel 自带的导出功能，将SQL Sever 中数据导出到Excel 的方式有很多种，但是，这些方法只适用于数据量较小的数据导出，且每导出到一个Excel 表格就需要进行一次手动操作，无法进行一次性批量导出。因此，当需要进行数据量较大的数据转换或将数据批量导出到多个Excel 表格等操作时，利用SQL Sever 或者Excel 自带的导出功能是效率极低的，且容易造成数据缺失。
3 利用Python 将SQL Sever 中数据导出到Excel Python 是一种高级的计算机程序设计语言，能够连接S
QL sever 数据库，并直接把数据表加载到Python ，通过Python 的pandas 导出到本地Excel ，可以有效解决直接通过数据库导出大量数据到Excel 过程中运行速度极慢、中断以及不成功等问题，且可以利用Python 模块XlsxWriter 批量设置用户所需要的Excel 数据表格式
[5,6]
。
3.1 Python 脚本代码
//导入包
import pandas as pd
from sqlalchemy import create_engine //定义数据库链接 username = 'abc' password = 'abc123'
cons=create_engine('mssql+pyodbc://%s:%*********.5.91:8182/test?driver=ODBC Driver 17 for SQL Server' %(username, password))
//定义SQL 参数，调用SQL 脚本1 sql_1 = '''
SELECT [字段1]，[字段2]，[字段3]，[字段4]，[字段5]，count(*) 数量
FROM table
where 条件
group by [字段1]，[字段2]，[字段3]，[字段4]，[字段5]
'''
//包装数据导出到Excel 的函数 def getdata(a):
data_1 = pd.read_sql(sql_1 %a,con=cons)
writer_=pd.ExcelWriter('文件名.xlsx' %a, engine='xlsxwri ter')
_Excel(writer_1,sheet_name='Sheet1',startrow=1, header=False, index=False)
workbook_1  = writer_1.book worksheet_1 = writer_1.sheets['Sheet1']
for col_num, value in enumerate(lumns.values): worksheet_1.write(0, col_num, value) writer_1.save() //调用包装好的函数 getdata()
3.2 利用XlsxWriter 设置导出Excel 的数据格式
XlsxWriter 是一个用来写Excel 数据表格式的Python 模块。它可以用来写文本、公式、数字并支持单元格格式化、图片、图表、文档配置、自动过滤等特性，将所需导出的数
据设置为用户所需要的Excel 格式。
3.3 实例应用
将SQL sever 数据库中2017-2020年部分汽车数据导出到Excel 表格中，由于行数较多，导出到一个Excel 中超出最大行数限制，因此根据需求，将导出数据分为每半年提供一个Excel 数据表，因此共分成8个Excel 数据表。
步骤一：编写核心代码 import pandas as pd
汽车实用技术
184from sqlalchemy import create_engine
username = 'lina'
password = '***********'
cons=create_engine('mssql+pyodbc://%s:%*********.*.
**:****/test?driver=ODBC Driver 17 for SQL Server' %(userna me, password))
date_list = ['2017','2018','2019','2020']
sql_1 = '''
SELECT cast（[年] as int）as 年，cast（[月] as int）as 月，[省份] as省，[大区]，[厂商（中文）] as厂商_CN，[厂商（英文）] as厂商，[厂商品牌] as厂商品牌，[品牌（中文）] as品牌_CN，[品牌（英文）] as品牌，[车系（中文）] as车型_CN，[车系（英文）] as车型，[车身形式]，[国别]，[来源]，[细分市场分组]，[细分市场] as大细分市场，[子细分市场] as细分市场，[燃料类型]as燃料种类，count(*) as[量]，[城市]，[层级]，[城市补充]，[层级补充]
FROM [test].[dbo].[V_ENTERPRISE_CYC_NEW]
where 年=%s and 月<=6
group by cast（[年] as int），cast([月] as int)，[省份]，[大区]，[厂商（中文）]，[厂商（英文）]，[厂商品牌]，[品牌（中文）]，[品牌（英文）]，[车系（中文）]，[车系（英文）]，[车身形式]，[国别]，[来源]，[细分市场分组]，[细分市场]，[子细分市场]，[燃料类型]，[城市]，[层级]，[城市补充]，[层级补充]
'''
sql_2 = '''
SELECT cast([年] as int) as 年，cast([月] as int) as 月，[省份] as 省，[大区]，[厂商（中文）] as 厂商_CN，[厂商（英文）] as 厂商，[厂商品牌] as 厂商品牌，[品牌（中文）] as 品牌_CN，[品牌（英文）] as 品牌，[车系（中文）] as 车型_CN，[车系（英文）] as 车型，[车身形式]，[国别]，[来源]，[细分市场分组]，[细分市场] as 大细分市场，[子细分市场] as 细分市场，[燃料类型] as 燃料种类，count(*) as [量]，[城市]，[层级]，[城市补充]，[层级补充]
FROM [test].[dbo].[V_ENTERPRISE_CYC_NEW]
where 年=%s and 月>=7
group by cast([年] as int)，cast([月] as int)，[省份]，[大区]，[厂商（中文）]，[厂商（英文）]，[厂商品牌]，[品牌（中文）]，[品牌（英文）]，[车系（中文）]，[车系（英文）]，[车身形式]，[国别]，[来源]，[
细分市场分组]，[细分市场]，[子细分市场]，[燃料类型]，[城市]，[层级]，[城市补充]，[层级补充] '''
def getdata(a):
data_1= pd.read_sql(sql_1 %a,con=cons)
writer_1= pd.ExcelWriter('%s01-06零售数据（城市）.xlsx' %a, engine='xlsxwriter')
_Excel(writer_1, sheet_name='Sheet1', startrow=1, header=False, index=False)
workbook_1 = writer_1.book
worksheet_1 = writer_1.sheets['Sheet1']
for col_num, value in enumerate(lumns.values):
worksheet_1.write(0, col_num, value)
writer_1.save()
data_2= pd.read_sql(sql_2 %a,con=cons)
writer_2= pd.ExcelWriter('%s07-12零售数据（城市）.xlsx' %a, engine='xlsxwriter')
_Excel(writer_2, sheet_name='Sheet1', startrow=1, header=False, index=False)
workbook_2= writer_2.book
worksheet_2= writer_2.sheets['Sheet1']
for col_num, value in enumerate(lumns.values):
worksheet_2.write(0, col_num, value)
writer_2.save()
for d in date_list:
getdata(d)
步骤二：将代码存储到数据.py文件中
步骤三：利用anaconda操作平台调用数据.py文件，如图7所示，首先输入activate excel激活Excel文件，
再输入cd数据.py所在文件夹位置来打开文件夹，输入Python 数据.py，回车调用数据.py文件中脚本代码，运行程序。当程序运行完成，即可一次性得出如图8所示的以每半年为一个Excel数据表的共8个数据表，极大地节省了数据库管理员的时间成本，并可以直接设置好用户所需的Excel数据表导出格式，实现数据导出的半自动化处理。
图7 anaconda操作界面
图8 Python批量导出到Excel的结果文件
4 结论
本文设计的Python程序实现了SQL sever向Excel表格进行数据的批量导出，利用此方法不仅可以批量的导出大量数据，还可以直接通过Python的XlsxWriter模块设置导出到
李娜 等：基于Python 实现SQL SEVER 与Excel 间数据的批量导出
185
Excel 表格中的数据格式，实现跨数据库传递和数据编辑的半自动化处理，解决了利用SQL Sever 或Excel 自带功能进行数据传递和编辑无法批量处理以及传递效率低等问题，极大地提高了数据库管理员和用户的工作效率。
参考文献
[1] 杨晓峰，张茁.使用VB 将SQL Server 数据导出生成Excel 表格[J].
硅谷,2009(10):58-88.
[2] 王国辉，李磊，冯春龙.Python 从入门到项目实践[M].长春：吉林大
学出版社,2018.
[3] 杨学全.SQL SERVER 实例教程[M].北京：电子工业出版社,2019. [4] 赵喜来，崔程.SQL sever 2005从入门到精通[M].北京：电子工业出
版社,2007.
[5] 李菁.基于Python 的Excel 文档处理程序的设计[J].科技经济导
刊,2020,28(22):14-69.
[6] 周延熙.基于Python 的Excel 文档处理程序的设计与实现[J].信息
与电脑(理论版),2019,31(23):85-87.
（上接第168页）
通过实车检查SB10保险后，发现SB10保险已熔断，更换保险后故障消失，发动机可正常起动。
4 结束语
众所周知，汽车是一个集机电液于一体的交通运输工具，作为一名合格的汽车维修人员，在日常汽车故障诊断中，应遵循以下四个诊断原则：（1）先简后繁、先易后难的原则；
（2）先思后行、先熟后生的原则；（3）先上后下、先外后里的原则；（4）先备后用、代码优先诊断的原则。只有遵循上述四个诊断原则，方能在实际工作中达到事半功倍的效果。
参考文献
[1] 弋国鹏.汽车检测与维修竞赛案例集[M].北京：机械工业出版社.
2018.3.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。