python读取excel文件并自动在mysql中建表导数据

107 阅读 0 评论 71 点赞

我是靠谱客的博主腼腆牛排，这篇文章主要介绍python读取excel文件并自动在mysql中建表导数据，现在分享给大家，希望可以做个参考。

复制代码

"""
根据excel在mysql中建表(表名为文件名,字段为csv中的header,默认所有字段为varchar,如需更改,在数据库中更改即可),并插入数据
"""
import xlrd
import pymysql
from datetime import date, datetime
# 建表并生成插入语句
from xlrd import xldate_as_tuple
def create_table(file_name, header):
table_name = file_name.split(".")[0]
header = [str(i).replace("－", "_").replace("-", "_").replace("(", "_").replace(")", "") for i in header]
create_sql = "CREATE TABLE `%s` (`id` int(11) NOT NULL AUTO_INCREMENT," % table_name
for i in header:
create_sql += "`%s` varchar(255) DEFAULT NULL," % i
create_sql += "PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
cursor.execute(create_sql)
db.commit()
insert_sql = "insert into %s(%s)values(%s)" % (table_name, ",".join(header), ",".join(['%s'] * len(header)))
print(insert_sql)
return insert_sql
# 每一千条插入数据库一次
def insert_data(file_name):
excel_path = "%s%s" % (dir_path, file_name)
wb = xlrd.open_workbook(filename=excel_path)
# 通过sheet索引
worksheet = wb.sheet_by_index(0)
# 通过sheet名
# worksheet = wb.sheet_by_name("人员")
cols_count = worksheet.ncols
insert_datas = []
for i in range(worksheet.nrows):
if i == 0:
row_data = worksheet.row_values(0)
insert_sql = create_table(file_name, row_data)
continue
row_data = []
for j in range(cols_count):
cell_type = worksheet.cell(i, j).ctype
cell_value = worksheet.cell_value(i, j)
if cell_type == 3:
# 转成datetime对象
date_tool = datetime(*xldate_as_tuple(cell_value, 0))
cell_value = date_tool.strftime('%Y-%d-%m %H:%M:%S')
elif cell_type == 4:
cell_value = True if cell_value == 1 else False
row_data.append(cell_value)
sql_data = tuple(row_data)
print(sql_data)
insert_datas.append(sql_data)
if i + 1 % 1000 == 0:
cursor.executemany(insert_sql, insert_datas)
db.commit()
insert_datas = []
if len(insert_datas) != 0:
cursor.executemany(insert_sql, insert_datas)
db.commit()
db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='test')
cursor = db.cursor()
dir_path = "F:/"
insert_data("name.xlsx")
db.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
"""
根据excel在mysql中建表(表名为文件名,字段为csv中的header,默认所有字段为varchar,如需更改,在数据库中更改即可),并插入数据
"""
import xlrd
import pymysql
from datetime import date, datetime
# 建表并生成插入语句
from xlrd import xldate_as_tuple
def create_table(file_name, header):
table_name = file_name.split(".")[0]
header = [str(i).replace("－", "_").replace("-", "_").replace("(", "_").replace(")", "") for i in header]
create_sql = "CREATE TABLE `%s` (`id` int(11) NOT NULL AUTO_INCREMENT," % table_name
for i in header:
create_sql += "`%s` varchar(255) DEFAULT NULL," % i
create_sql += "PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
cursor.execute(create_sql)
db.commit()
insert_sql = "insert into %s(%s)values(%s)" % (table_name, ",".join(header), ",".join(['%s'] * len(header)))
print(insert_sql)
return insert_sql
# 每一千条插入数据库一次
def insert_data(file_name):
excel_path = "%s%s" % (dir_path, file_name)
wb = xlrd.open_workbook(filename=excel_path)
# 通过sheet索引
worksheet = wb.sheet_by_index(0)
# 通过sheet名
# worksheet = wb.sheet_by_name("人员")
cols_count = worksheet.ncols
insert_datas = []
for i in range(worksheet.nrows):
if i == 0:
row_data = worksheet.row_values(0)
insert_sql = create_table(file_name, row_data)
continue
row_data = []
for j in range(cols_count):
cell_type = worksheet.cell(i, j).ctype
cell_value = worksheet.cell_value(i, j)
if cell_type == 3:
# 转成datetime对象
date_tool = datetime(*xldate_as_tuple(cell_value, 0))
cell_value = date_tool.strftime('%Y-%d-%m %H:%M:%S')
elif cell_type == 4:
cell_value = True if cell_value == 1 else False
row_data.append(cell_value)
sql_data = tuple(row_data)
print(sql_data)
insert_datas.append(sql_data)
if i + 1 % 1000 == 0:
cursor.executemany(insert_sql, insert_datas)
db.commit()
insert_datas = []
if len(insert_datas) != 0:
cursor.executemany(insert_sql, insert_datas)
db.commit()
db = pymysql.connect(host='localhost', port=3306, user='root', passwd="123zxcv", db='test')
cursor = db.cursor()
dir_path = "F:/"
insert_data("name.xlsx")
db.close()