Excel读取并存入数据库通用处理【原创】

此文为连载内容，Excel操作第二篇，对Excel的读取请参考
Python对Excel读取

二、读取之后存入数据库

建表选择了通用字段，A-Z列（没有具体表明），这样可涵盖26个序列，表头有一个字段来标注，与数据存在一张表里，查询时需要讲标头放在第一行

建表SQL

create table excel_row_data
(
 EXCEL_ROW_DATA_ID int auto_increment
 primary key,
 WORK_BOOK_NAME    varchar(255) null comment '文件名称',
 SHEET_NAME        varchar(255) null comment 'sheet页名称',
 FIRST_ROW_FLAG    varchar(1)   null comment '第一行标识',
 `ROW_NUMBER`      int          null,
 COLUMN_A          varchar(255) null,
 COLUMN_B          varchar(255) null,
 COLUMN_C          varchar(255) null,
 COLUMN_D          varchar(255) null,
 COLUMN_E          varchar(255) null,
 COLUMN_F          varchar(255) null,
 COLUMN_G          varchar(255) null,
 COLUMN_H          varchar(255) null,
 COLUMN_I          varchar(255) null,
 COLUMN_J          varchar(255) null,
 COLUMN_K          varchar(255) null,
 COLUMN_L          varchar(255) null,
 COLUMN_M          varchar(255) null,
 COLUMN_N          varchar(255) null,
 COLUMN_O          varchar(255) null,
 COLUMN_P          varchar(255) null,
 COLUMN_Q          varchar(255) null,
 column_r          varchar(255) null,
 COLUMN_S          varchar(255) null,
 COLUMN_T          varchar(255) null,
 COLUMN_U          varchar(255) null,
 COLUMN_V          varchar(255) null,
 COLUMN_W          varchar(255) null,
 COLUMN_X          varchar(255) null,
 COLUMN_Y          varchar(255) null,
 COLUMN_Z          varchar(255) null
);

读取Excel数据，row数据逐个写入，如果数据量小于26则给与空字符

def read_input_ex():
 workbook = xlrd.open_workbook(WORK_BOOK_NAME)  # (1)取得excel book对象
 sheet = workbook.sheet_by_name(SHEET_NAME)  # (2)取得sheet对象
 rows = sheet.nrows  # (3)获得总行数
​
 for r in range(0, rows):
 edata = []
 edata.append(WORK_BOOK_NAME)
 edata.append(SHEET_NAME)
 if r == 0 :
 edata.append('Y')
 else:
 edata.append('N')
 row = sheet.row_values(r)  # (4)获取行数据
​
 # rows的长度sheet._dimncols
 for t in range(0, sheet._dimncols):
 edata.append(row[t])
 if sheet._dimncols < 26:
 for t in range(0, 26 - sheet._dimncols):
 edata.append("")

插入数据，通用的数据插入SQL，此处只做演示，暂无考虑效率问题

def process_item(item):
​
 # 数据库连接
 con = pymysql.connect(host=host, user=user, passwd=psd, db=db, charset=c, port=port)
 # 数据库游标
 cue = con.cursor()
​
 insert_sql = " INSERT IGNORE INTO EXCEL_ROW_DATA  ( " \
 " WORK_BOOK_NAME,  SHEET_NAME, FIRST_ROW_FLAG, " \
 "COLUMN_A,  COLUMN_B, COLUMN_C, COLUMN_D,  COLUMN_E," \
 "COLUMN_F, COLUMN_G, COLUMN_H, COLUMN_I, COLUMN_J, " \
 "COLUMN_K, COLUMN_L, COLUMN_M, COLUMN_N, COLUMN_O, " \
 "COLUMN_P, COLUMN_Q, COLUMN_R, COLUMN_S,  COLUMN_T, " \
 "COLUMN_U, COLUMN_V, COLUMN_W, COLUMN_X, COLUMN_Y, COLUMN_Z" \
 ") VALUE (" \
 "%s,%s,%s,%s," \
 "%s,%s,%s,%s,%s," \
 "%s,%s,%s,%s,%s," \
 "%s,%s,%s,%s,%s," \
 "%s,%s,%s,%s,%s," \
 "%s,%s,%s,%s,%s )"
 print(insert_sql, item)
​
 try:
 cue.execute(insert_sql,item)
 except Exception as e:
 print('Insert error:', e)
 con.rollback()
 else:
 con.commit()
​
 cue.close()  # 关闭游标

使用时需要手动设置Excel路径和sheet页名字，结果如下

1580305537233.png

2020/01/29 于许昌