通过python读取excel写入mysql

import pymysql
import xlrd
import sys
from xlrd import xldate_as_datetime

# excel文件的路径
excel_path=sys.argv[1]
host=sys.argv[2]
user=sys.argv[3]
password=sys.argv[4]
db=sys.argv[5]
table=sys.argv[6]


# 读取excel文件
excel_data = xlrd.open_workbook(excel_path)
# 获取第一个sheet页
sheet = excel_data.sheet_by_index(0)
# 总行数
rows = sheet.nrows
# 总列数
cols = sheet.ncols

# 因为在mysql中添加了一列主键为自增id，excel中没有，所以要提取出除主键外的其他字段，才可以与excel匹配
# 获取表的字段名
def get_col_values():
    global col_result
    try:
        con = pymysql.connect(host, user, password, db)
        cur = con.cursor()  # 获取操作游标，也就是开始操作
        sql_select = 'select * from ' + table + ' limit 1;'  # 查询命令
        cur.execute(sql_select)  # 执行查询语句

        col_result = cur.description  # 获取查询结果的字段描述
        con.close()
    except Exception as e:
        print(e)
        print("!!!!!!!!!!!!!!请检查数据库连接信息!!!!!!!!!!!!!!")
        exit(-1)

    # 将字段名存入数组，第一个字段为主键，不存入
    columns = []
    for i in range(1, len(col_result)):
        columns.append(col_result[i][0])  # 获取字段名，以列表形式保存
    print(columns)

    sql = "insert into " + table + '('
    for i in columns:
        sql = sql + i + ','

    # 去掉最后一个逗号
    sql = sql[:-1]
    sql += ') values(' + '%s, ' * len(columns) + ')'

    sql = sql[:-3]
    sql += ')'
    print(sql)
    return sql


# 清空表
def truncate_table():
    try:
        con = pymysql.connect(host, user, password, db)
        cur = con.cursor()

        cur.execute('truncate table ' + table)
        con.close()
    except Exception as e:
        print(e)
        print("!!!!!!!!!!!!!!请检查数据库连接信息!!!!!!!!!!!!!!")
        exit(-1)


# 写入mysql
def write_to_mysql(sql):
    # 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题
    for i in range(1, rows):
        # item = sheet.row_values(i)[:len(cols)]
        data = []
        # 组装参数化sql，通过拼接%s得到占位符
        for j in range(0, cols):
            data.append(sheet.cell(i,j).value）
       
        try:
            con1 = pymysql.connect(host, user, password, db)
            cur1 = con1.cursor()

            cur1.execute(sql,data)
            con1.commit()
            con1.close()
        except Exception as e:
            print(e)
            print("!!!!!!!!!!!!!!请检查数据库连接信息!!!!!!!!!!!!!!")
            exit(-1)


if __name__ == "__main__":
    truncate_table()
    sql=get_col_values()
    write_to_mysql(sql)