Python读取Excel并写入MySQL的实用工具实现与优化
本文详细介绍使用Python将Excel数据导入MySQL数据库的完整流程。通过xlrd和pymysql库实现数据读取与写入，重点解析数据库连接字符集设置、常见安装报错处理及[[wp:a2]]错误排查。此外，对比单条插入与批量写入的性能差异，展示如何通过executemany提升大数据量处理效率，为数据分析提供高效的数据入库方案。
Python在数据分析领域具有显著优势。
开展数据分析工作时，首要步骤是掌握如何读取日常工作中产生的各类Excel报表，并将数据存入数据库，以便进行后续的数据处理与分析。 本文旨在分享利用Python读取Excel文件，并将数据成功存入MySQL数据库的具体方法，供相关领域的读者参考学习。
背景
项目需要将全国高校名录导入数据库，数据来源于教委网站发布的最新Excel格式文件。为此，需要开发一个专用工具进行数据导入，决定尝试使用Python实现，随即开始开发。
依赖库
- xlrd：用于Excel文件的读写操作。
- pymysql：纯Python编写的MySQL数据库驱动库。
- re：Python核心库中的正则表达式模块。
前两个库可通过pip轻松安装。本人在Mac Pro环境下操作顺利，此前在Mac上安装mysqlclient一直失败，导致曾放弃使用Python，但在Linux环境下安装mysqlclient则无此问题。
源代码
这是一个简单的小脚本，仅作纪念。需特别注意数据库连接字符串中必须设定字符编码，否则默认使用latin-1会导致写入错误。
import xlrd
import pymysql
import re
conn = pymysql.connect(host='database connect address', port=1234, user='root',
passwd='****', db='database name', charset='utf8mb4')
p = re.compile(r'\s')
data = xlrd.open_workbook('./W020170616379651135432.xls')
table = data.sheets()[0]
t = table.col_values(1)
nrows = table.nrows
for i in range(nrows):
r1 = table.row_values(i)
if len(r1[2]) == 10:
cur = conn.cursor()
cur.execute('insert into `university` (`id`, `name`, `ministry`, `city`, `level`, `memo`) \
values (%s, %s, %s, %s, %s, %s)',
(r1[2], p.sub('', r1[1]), p.sub('', r1[3]), p.sub('', r1[4]), r1[5], r1[6]))
conn.commit()
cur.close()
conn.close()
心得
习惯了类C语言的开发风格，转向Python编程初期感到不太适应，希望同时掌握两种不同风格的编程语言确实颇具挑战。不过，Python的编程效率确实出色。这是我首次使用Python编写实用小程序，从查询资料、编码到调试，总共耗时约一小时。得益于Python丰富的库资源和文档资料，寻找合适的解决方案并不困难。
数据库写入优化
次日空闲时，通过批量写入方式对程序进行了优化，任务执行瞬间完成，速度比逐条写入快得多，且效果远超预期。这证明没有不好的工具，关键在于如何充分利用其功能。
import xlrd
import pymysql
import re
conn = pymysql.connect(host='database connect address', port=1234, user='root',
passwd='****', db='database name', charset='utf8mb4')
p = re.compile(r'\s')
data = xlrd.open_workbook('./W020170616379651135432.xls')
table = data.sheets()[0]
t = table.col_values(1)
nrows = table.nrows
ops = []
for i in range(nrows):
r1 = table.row_values(i)
if len(r1[2]) == 10:
ops.append((r1[2], p.sub('', r1[1]), p.sub('', r1[3]), p.sub('', r1[4]), r1[5], r1[6]))
cur = conn.cursor()
cur.executemany('insert into `university_copy` (`id`, `name`, `ministry`, `city`, `level`, `memo`) \
values (%s, %s, %s, %s, %s, %s)', ops)
conn.commit()
cur.close()
conn.close()
Python读取Excel文件常见问题
1. 在Mac上安装xlrd模块时，若通过cmd执行pip install xlrd失败，可直接前往官网下载类似xlrd-1.0.0-py3-none-any.whl的文件，切换到下载文件所在路径后，在cmd中执行pip3 install xlrd-1.0.0-py3-none-any.whl即可完成安装。 http://pypi.python.org/pypi/xlrd
2. Python打开Excel时出现xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'username'错误。
可检查要打开的Excel文件在保存时是否已存在兼容性或格式提示。若存在此类问题，建议重新创建一个不存在格式问题的Excel文件。
import xlrd
from os.path import join,abspath,dirname
fname = join(dirname(dirname(abspath(__file__))),'test1.xls')
bk = xlrd.open_workbook(fname, encoding_override="utf-8")
shxrange = range(bk.nsheets)
try:
sh = bk.sheet_by_name("工作表1")
# 获取行数
nrows = sh.nrows
# 获取列数
ncols = sh.ncols
print("nrows %d, ncols %d" % (nrows, ncols))
# 获取第一行第一列数据
cell_value = sh.cell_value(1, 1)
# print cell_value
row_list = []
# 获取各行数据
for i in range(0, nrows):
row_data = sh.row_values(i)
row_list.append(row_data)
except:
print("no sheet in %s named Sheet1" % fname)
总结
以上为本文的全部内容，希望本文的内容对大家的学习或工作具有一定的参考价值。如有疑问，欢迎留言交流，感谢大家对本站的支持。
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
