求教下，python把execl表导入到mysql为什么这么慢？
3000多条数据要花半分钟，机子差一点要花1，2分钟，怎么解决？def excelTosql():
global photo_repository
if (len(photo_repository) == 0):
tk.messagebox.showinfo("警告", "先选择照片所在的文件夹！")
return
file=tkinter.filedialog.askopenfilename()
if file[-4:]!='.xls':
tkinter.messagebox.showerror('错误','您选择的不是excel文件')
return
temp=file.split('/')
global datatable_name
tablename=temp[-1]
datatable_name=tablename
conn = pymysql.connect(server_name, user_name, user_password, database_name, charset="utf8")
cursor=conn.cursor()
sql="show tables like '"+tablename+"'"
cursor.execute(sql)
result=cursor.fetchall()
if len(result) ==0:
sql="create table "+tablename+"( "\
"student_number varchar(13) not null,"\
"BH varchar(2),"\
"exam_taker_number varchar(20),"\
"name varchar(11),"\
"gender varchar(8),"\
"birthday varchar(8),"\
"faculty varchar(23),"\
"discipline varchar(47),"\
"length_of_academic_system varchar(2),"\
"checked_time varchar(255),"\
"photo varchar(255), "\
"primary key(student_number)) charset=utf8"
cursor.execute(sql)
conn.commit()
xlsfile = xlrd.open_workbook(file)
sheet = xlsfile.sheet_by_index(0)
nrows = sheet.nrows
print(nrows)
index = 1
while index < nrows:
print(index)
value =
for i in range(9):
value = sheet.cell(index, i).value
value = ""
value = ""
sql="insert into "+tablename+\
" values('"+value+"','"+value+"','"+value+"','"+value+"','"+value+"',"\
"'"+value+"','"+value+"','"+value+"','"+value+"','"+value+"',"\
"'"+value+"')"
cursor.execute(sql)
conn.commit()
index += 1
tkinter.messagebox.showinfo('成功', '数据表导入成功')
else:
pass
sql="select * from "+tablename+" limit 1"
cursor.execute(sql)
student = Student(cursor.fetchone())# 尝试把查询结果绑定到Student对象
sql = "select count(*) from " + datatable_name
cursor.execute(sql)
student.sum = cursor.fetchone()
student.rank=1
conn.close()
showinfo(student) 有人吗？ 有人吗？
本帖最后由 jkluoling1992 于 2020-5-8 20:30 编辑
把while内的conn. commit写到while循环外 jkluoling1992 发表于 2020-5-8 20:08
把while内的conn. commit写到while循环外
感谢大佬，解决了！
页:
[1]