Python 导入 Excel 到数据库

Python 导入 Excel 到数据库

Python作为简单易学的脚本语言，在处理系统运维工作时，也有很大帮助。要知道CentOS的包管理工具就是通过Python编写的。

在实际工作过程中，用户提供的基础数据往往是基于Excel整理的，将Excel数据导入数据库是一个十分常见的需求。本文将介绍如何基于Python3实现读取Excel数据，并导入到数据库。

本程序中会应用到两个包，可以通过pip安装

pip install openpyxl pyodbc

程序结构如下图所示

入口模块通过Run来执行。它依赖SqlHelper以及ExcelHelper。SqlHelper负责数据库读取的操作，ExcelHelper负责Excel读取操作。ExcelHelper通过读取Excel文件，解析为一个Model类型的数组。SqlHelper通过读取这个Model数组，来进行数据库操作。

run.py 入口模块代码如下：

#通过from ... import引入其他模块 from excelHelper import ExcelHelper from sqlHelper import SqlHelper def run(): importxls(\'xls/xxx.xlsx\') def importxls(path): # 读取excel文件，获取model列表 excelHelper = ExcelHelper(path,\'Sheet1\') modellist = excelHelper.readExcelForModels() # 通过sqlHelper传入读取到的model列表插入数据库 sqlHelper = SqlHelper() sqlHelper.insertModels(modellist) #执行 run()

model.py Model是我们读取excel数据后需要得到的数据模型定义。定义自己需要的字段就行：

class Model: def __init__(self,code,name): self.code = code self.name = name #通过定义str，帮助在调试时，通过print()函数打印数据 def __str__(self): str = "code:%s name:%s tenantId:%s targetRemark1:%s" % (self.code, self.name) return str

excelHelper.py 读取excel帮助类：

#引入openpyxl以读取excel文件 import openpyxl #引入模型定义 from model import Model class ExcelHelper: def __init__(self, path, sheetname): #列数定义，Index从1开始计算 self.codeIndex = 2 self.nameIndex = 3 #读取workbook。data_only=True，以避免Excel公式值读取的问题 wb = openpyxl.load_workbook(path, data_only=True) #读取标签 self.sheet = wb[sheetname] #读取excel中的行数 self.max_row = self.sheet.max_row def readExcelForModels(self): list = [] #循环读取excel，行数从1开始计算 for rowIndex in range(2, self.max_row): cellCode = self.sheet.cell(row=rowIndex, column=self.codeIndex).value if(cellCode == None): continue #读取所需数据 code = self.sheet.cell(row=rowIndex, column=self.codeIndex).value name = self.sheet.cell(row=rowIndex, column=self.nameIndex).value #设置model data = Model(code, name) #插入数组 list.append(data) return list

appsettings.json 定义数据库odbc连接串

{ "ConnectionStrings": { "Default": "DRIVER={SQL SERVER};Server=xx; Database=xx; UID=xx;PWD=xx;" } }

configReader.py 读取数据库连接串：

import json def getConnectString(name): config = open("appsettings.json") setting = json.load(config) connectstring = setting[\'ConnectionStrings\'][name] return connectstring

sqlHelper.py 数据库操作帮助类：

#sqlHelper.py #pyodbc通过odbc读取数据库 import pyodbc #configReader来读取appsettings.json import configReader #textwrap用于处理sql字符串 import textwrap class SqlHelper: def __init__(self): #获取数据库连接字符串 conn_info = configReader.getConnectString(\'Default\') #连接数据库 self.mssql_conn = pyodbc.connect(conn_info) #获取游标 self.mssql_cur = self.mssql_conn.cursor() #校验字段是否为空 def isEmpty(self, v): if(v == None or v.isspace()): return True else: return False #插入数据 def insertModels(self, modelList): for model in modelList: if(self.isEmpty(item.code) or self.isEmpty(item.name)): continue #通过textwrap构建sql字符串，?代表预留参数位 sql = textwrap.dedent(""" INSERT INTO [dbo].[Model] ([Code] ,[Name] ) VALUES (? ,? ) """) #循环插入数据 self.mssql_cur.execute(sql, model.code, model.name) #数据库事务提交 self.mssql_conn.commit() #展示如何进行数据库查询，假设存在一个可能为null的字段tennantId def getIdByCode(self, code, tennantId): #由于tennantId可能为null，因此查询语句会有is null的差别 if(tennantId == None): #通过fetchval()来读取数据 return self.mssql_cur.execute("select Id from Model where TenantId is null and Code = ?", code).fetchval() else: return self.mssql_cur.execute("select Id from Model where TenantId = ? and Code = ?", tennantId, code).fetchval()

Python进行excel导入数据库操作，总体而言还是很简单的。尤其是pyodbc对于数据库的操作有了较好的封装，对于修改操作，默认提供了事务提交。并支持通过参数替换的方式来输入参数。python不愧是一个易于使用的语言，其他语言做类似的事，编程效率要弱不少。