python如何读取excle文件再写入数据库

pomiss

要用 python 做一个导入数据库的脚本，请教一下如何用 python 读取xls、xlsx、csv等表格文件并导入数据库。
网上看了很多方法，觉得比较麻烦，比如：

"""
功能：将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!"

这样的行数我觉得太多了，并且不能自动获取到列，如果我的列数非常多，怎么办？如果需要做数据的格式化怎么办？

大神请给予指导

ouzo

这个确实有点长了，可能是为了展示思路吧。我给你提供一个更短的办法。

首先，引入类库：pymysql是 mysql的数据库链接库；xlrd是对表格处理的，time是看执行时间的。至于为什么不使用pandas之类的类库，因为pandas用的也是这个，所以心里感觉会效率低一些。

import pymysql
import xlrd
import time

其次，我们要去数据库里把表跟字段建好，这里不提倡用程序去主动创建表，原因很简单，可能你要导入的数据库本身就有很多数据，并且表的字段都是固定的，导入数据的意义在于迁移或者数据仓库服务，要尽量学会使用python来对数据进行格式化、过滤等处理。

然后，我们打开一个需要导入数据的文件，比如xlsx的，斜杠尽量不要使用反斜杠。如果是多sheet，要指定读取哪个sheet来进行操作。如果是默认就一个，就不用加了。

exFile = xlrd.open_workbook("X:/某某数据文件.xlsx")
sheet = exFile.sheet_by_name('sheet2')

如果你想看看打开速度，在这两行上面加上

start_time = time.time()
# 此处为 要执行的操作代码
print('用时: %.3f 秒' % (time.time() - start_time))

最后，文件打开了可以读取了，我们要执行SQL语句了。SQL的语句用三引号不要用双引号。

sql_go = """INSERT INTO tableName(field1,field2,field3,field4) VALUES (%s, %s, %s, %s,)"""
with pymysql.connect(host=' ', user=' ', password=' ', database='dbName', charset='utf8') as cursor:
# 1, sheet_nrows 跳过首行, 首行是参数名
    for r in range(1, sheet.nrows):
        values = sheet.row_values(r)
        if not values[0]:
            break
        cursor.execute(sql_go, values)

这是简写，里面用到一个语句加一个 with…………as cursor：；其实这个正常写就是：

# 先连接数据库
conn = pymysql.connect(host=' ', user=' ', password=' ', database='dbName', charset='utf8') 
# 然后建个游标
cursor = conn.cursor()
# 然后执行语句
sql_go = """INSERT INTO tableName(field1,field2,field3,field4) VALUES (%s, %s, %s, %s,)"""

这个带来的问题就是，你可能会忘记close一些东西，比如数据库或者游标。使用with会比较好一些。
另外里面的循环，r 循环出来的是sheet里面的列数，定义一个values （值）是列数即可。再者是行数，如果值不是空就循环执行，如果为空就跳出了，很多文件都有几万行，但是有内容的可能也就几千行，为了节省资源，我们肯定到没有数据的时候就停止导入了，所以这部分要加上。

扩展：

如果是做数据清洗工作，单独在另外的地方写函数方法，在这里调用后再进行插入数据库操作即可
如果是需要只插入某些列，到某些数据库字段中，要先获取列数，然后取出来如：【0，2，3，5，8】列为一个数组row_arry，对应到执行SQL语句的字段中即可，比如：

row_arry = itemgetter(0，2，3，5，8)(row)
cursor.execute('insert into tableName(field1,field2,field3,field4,field5) VALUES(%s, %s, %s)', row_arry)

以上就是简单的操作过程，望采纳。

pomiss

[未知用户] 非常满意的回答，原来还可以这么写。感谢社管理的耐心解答

ouzo

[未知用户] 中间如果想要知道插入多少数据，用了多少时间，如下操作：

start_time = time.time()
exFile = xlrd.open_workbook("X:/某某数据文件.xlsx")
sheet = exFile.sheet_by_name('sheet2')
count = 0
sql_go = """INSERT INTO tableName(field1,field2,field3,field4) VALUES (%s, %s, %s, %s,)"""
with pymysql.connect(host=' ', user=' ', password=' ', database='dbName', charset='utf8') as cursor:
    for r in range(1, sheet.nrows):
        count += 1
        values = sheet.row_values(r)
        if not values[0]:
            break
        cursor.execute(sql_go, values)

print('条数:', count)
print('用时: %.3f 秒' % (time.time() - start_time))

pomiss

[未知用户] 感谢大神指导