使用Python将Excel中的数据导入到MySQL
使用Python将Excel中的数据导入到MySQL
工具
- Python 2.7
- xlrd
- MySQLdb
安装
- Python
对于不同的系统安装方式不同,Windows平台有exe安装包,Ubuntu自带。使用前请使用下面的命令确保是2.7.x版本:
python --version
- xlrd :
这是一个扩Python包,可以使用pip包管理工具安装:pip install xlrd
- MySQLdb
为MySQL 的Python驱动接口包,可以到http://sourceforge.net/projects/mysql-python/下载安装。在Ubuntu值哦你可以使用sudo apt-get install python-mysql
安装
实现数据转移
功能很简单,直接在代码中注释了
"""
功能:将Excel数据导入到MySQL数据库
"""
import xlrd
import MySQLdb
# Open the workbook and define the worksheet
book = xlrd.open_workbook("pytest.xls")
sheet = book.sheet_by_name("source")
#建立一个MySQL连接
database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython")
# 获得游标对象, 用于逐行遍历数据库数据
cursor = database.cursor()
# 创建插入SQL语句
query = """INSERT INTO orders (product, customer_type, rep, date, actual, expected, open_opportunities, closed_opportunities, city, state, zip, population, region) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题
for r in range(1, sheet.nrows):
product = sheet.cell(r,).value
customer = sheet.cell(r,1).value
rep = sheet.cell(r,2).value
date = sheet.cell(r,3).value
actual = sheet.cell(r,4).value
expected = sheet.cell(r,5).value
open = sheet.cell(r,6).value
closed = sheet.cell(r,7).value
city = sheet.cell(r,8).value
state = sheet.cell(r,9).value
zip = sheet.cell(r,10).value
pop = sheet.cell(r,11).value
region = sheet.cell(r,12).value
values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region)
# 执行sql语句
cursor.execute(query, values)
# 关闭游标
cursor.close()
# 提交
database.commit()
# 关闭数据库连接
database.close()
# 打印结果
print ""
print "Done! "
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "我刚导入了 " %2B columns %2B " 列 and " %2B rows %2B " 行数据到MySQL!"
还真有人点开啊🤣随意随意😂