使用Python将Excel中的数据导入到MySQL

工具

• Python 3.x
• xlrd
• pymysql

安装

• Python

对于不同的系统安装方式不同，Windows平台有exe安装包，Ubuntu自带。使用前请使用下面的命令确保是3.x版本：

python --version

• xlrd :

这是一个用于读取excel文件的Python包，可以使用pip包管理工具安装：pip install xlrd

• pymysql

为MySQL 的Python驱动接口包，可以使用pip安装。

实现数据转移

功能很简单，直接在代码中注释了

"""功能：将Excel数据导入到MySQL数据库"""import xlrdimport pymysql

# Open the workbook and define the worksheetbook = xlrd.open_workbook("pytest.xls")sheet = book.sheet_by_name("source")#建立一个MySQL连接database = pymysql.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython",use_unicode=True, charset="utf8")# 获得游标对象, 用于逐行遍历数据库数据cursor = database.cursor()

# cursor.execute('SET NAMES utf8;utf8')# cursor.execute('SET CHARACTER SET utf8;')

cursor.execute('SET character_set_connection=utf8;')# 创建插入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,0).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("Done! ")columns = str(sheet.ncols)rows = str(sheet.nrows)print("我刚导入了 {} 列 and {} 行数据到MySQL!".format(columns,rows))