python,读excel数据，批量存入MySQL数据库

之前有个需求，把excel数据批量存入数据库，空数据存入为null，写了一个小脚本，可改进的地方，望大神不吝赐教

本文用到的两个库，xlrd，pymysql

第一种方法

• 读取excel文件，拼接SQL语句，批量插入数据库
  sql = insert into table_name values(),(),()......
  execute(sql)

import xlrd
import pymysql

# 连接数据库
coon = pymysql.connect(
    host="XXX.XXX.XXX.XXX",
    db="test_zwl",
    user="root",
    password="123456",
    port=3306
)
cur = coon.cursor()

# 打开excel文件，返回实例对象-<xlrd.book.Book object at 0x000001ED41180898>
excel = xlrd.open_workbook(r"./excel/2017年人员电子档案.xlsx") #r-->保持原始字符串，不转义
# 获取某一个sheet对象
sheet_index = excel.sheet_by_index(0)  # 根据索引
# 获取总行数和总列数
row_num = sheet_index.nrows
col_num = sheet_index.ncols

# 构造sql语句，批量插入数据库 values(),(),(),没有选择一条一条的插入
sql = "insert into file values"
for i in range(1,row_num): # 控制每一行
    for j in range(0,col_num): # 控制列
        item = sheet_index.cell_value(i, j) # 获取指定单元格数值
        # TODO 数据库中的空值两种形式，一种空字符串--数据库显示空白，另一种是null,且不能用引号包裹起来--数据库显示为null
        if item == "":
            item = "Null"
            value = str(item)
        else:
            value = '"' + str(item) + '"'
        if i != row_num-1:
            if j == 0 :
                sql += "(" + str(i) + ","+ value + ","  # TODO 插入的item 要用 ”“包起来，不然报错 1064，但是null不可以包
            elif j == col_num-1:
                sql += value + "),"
            else:
                sql += value + ","
        else:
            if j == 0 :
                sql += "(" + str(i) + ","+ value + ","
            elif j == col_num-1:
                sql += value + ")"
            else:
                sql += value + ","
    # break
# print(sql)

# 插入数据
try:
    cur.execute(sql)
    coon.commit()  # TODO 不要忘记提交啊
except:
    coon.rollback()

cur.close()
coon.close()

第二种方法

• 使用executemany(sql,params)
  sql = insert into table_name (字段名称，字段名称，字段名称)values(%s,%s,%s)
  params = [(),(),(),...........]
  executemany(sql,params)

value_list = []
for i in range(1,row_num):
    row_v = sheet_index.row_values(i)
    row_v = [None if row == "" else row for row in row_v ] # None在数据库显示为Null
    value_list.append(row_v)
sql_many = "insert into file (name,area,department,job_state,phone,in_date,out_date)values(%s,%s,%s,%s,%s,%s,%s)"

try:
    cur.executemany(sql_many,value_list)
    coon.commit()  # TODO 不要忘记提交啊
except Exception as e:
    print(e)
    coon.rollback()

cur.close()
coon.close()

注意：

• 拼接sql的方法，空值使用Null，且不能被包裹
• executemany方法，空值使用None