使用python将excel数据导入数据库过程详解
因为需要对数据处理,将excel数据导入到数据库,记录一下过程。
使用到的库:xlrd和pymysql(如果需要写到excel可以使用xlwt)
直接丢代码,使用python3,注释比较清楚。
importxlrd importpymysql #importimportlib #importlib.reload(sys)#出现呢reload错误使用 defopen_excel(): try: book=xlrd.open_workbook("XX.xlsx")#文件名,把文件与py文件放在同一目录下 except: print("openexcelfilefailed!") try: sheet=book.sheet_by_name("sheet名称")#execl里面的worksheet1 returnsheet except: print("locateworksheetinexcelfailed!") #连接数据库 try: db=pymysql.connect(host="127.0.0.1",user="root", passwd="XXX", db="XXX", charset='utf8') except: print("couldnotconnecttomysqlserver") defsearch_count(): cursor=db.cursor() select="selectcount(id)fromXXXX"#获取表中xxxxx记录数 cursor.execute(select)#执行sql语句 line_count=cursor.fetchone() print(line_count[0]) definsert_deta(): sheet=open_excel() cursor=db.cursor() foriinrange(1,sheet.nrows):#第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1 name=sheet.cell(i,0).value#取第i行第0列 data=sheet.cell(i,1).value#取第i行第1列,下面依次类推 print(name) print(data) value=(name,data) print(value) sql="INSERTINTOXXX(name,data)VALUES(%s,%s)" cursor.execute(sql,value)#执行sql语句 db.commit() cursor.close()#关闭连接 insert_deta() db.close()#关闭数据 print("ok")
XXX里自行修改自己的名称。
说明:对于不规则的单元格,例如合并过的单元格会取到空值。
优化了一下这个程序
importpymysql importxlrd #连接数据库 try: db=pymysql.connect(host="127.0.0.1",user="root", passwd="XXX", db="XXX", charset='utf8') except: print("couldnotconnecttomysqlserver") defopen_excel(): try: book=xlrd.open_workbook("XXX.xlsx")#文件名,把文件与py文件放在同一目录下 except: print("openexcelfilefailed!") try: sheet=book.sheet_by_name("XXX")#execl里面的worksheet1 returnsheet except: print("locateworksheetinexcelfailed!") definsert_deta(): sheet=open_excel() cursor=db.cursor() row_num=sheet.nrows foriinrange(1,row_num):#第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1 row_data=sheet.row_values(i) value=(row_data[0],row_data[1],row_data[2],row_data[3]) print(i) sql="INSERTINTOdemo_yangben(xxx,xxxx,xxxx,xxxx)VALUES(%s,%s,%s,%s)" cursor.execute(sql,value)#执行sql语句 db.commit() cursor.close()#关闭连接 open_excel() insert_deta()
再改一下,每一万条数据写入到数据库一次
importpymysql importxlrd importsys ''' 连接数据库 args:db_name(数据库名称) returns:db ''' defmysql_link(de_name): try: db=pymysql.connect(host="127.0.0.1",user="xxx", passwd="xxx", db=xxx, charset='utf8') returndb except: print("couldnotconnecttomysqlserver") ''' 读取excel函数 args:excel_file(excel文件,目录在py文件同目录) returns:book ''' defopen_excel(excel_file): try: book=xlrd.open_workbook(excel_file)#文件名,把文件与py文件放在同一目录下 print(sys.getsizeof(book)) returnbook except: print("openexcelfilefailed!") ''' 执行插入操作 args:db_name(数据库名称) table_name(表名称) excel_file(excel文件名,把文件与py文件放在同一目录下) ''' defstore_to(db_name,table_name,excel_file): db=mysql_link(db_name)#打开数据库连接 cursor=db.cursor()#使用cursor()方法创建一个游标对象cursor book=open_excel(excel_file)#打开excel文件 sheets=book.sheet_names()#获取所有sheet表名 forsheetinsheets: sh=book.sheet_by_name(sheet)#打开每一张表 row_num=sh.nrows print(row_num) list=[]#定义列表用来存放数据 num=0#用来控制每次插入的数量 foriinrange(1,row_num):#第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1 row_data=sh.row_values(i)#按行获取excel的值 value=(row_data[0],row_data[1],row_data[2],row_data[3],row_data[4],row_data[5],\ row_data[6],row_data[7],row_data[8],row_data[9],row_data[10],row_data[11],row_data[12], row_data[13],row_data[14]) list.append(value)#将数据暂存在列表 num+=1 if(num>=10000):#每一万条数据执行一次插入 print(sys.getsizeof(list)) sql="INSERTINTO"+table_name+"(time,xingbie,afdd,xzb,yzb,cfbj,jjlbmc,\ bjlbmc,bjlxmc,bjlxxlmc,gxqymc,gxdwmc,afql,afxqxx,cjdwmc)\ VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" cursor.executemany(sql,list)#执行sql语句 num=0#计数归零 list.clear()#清空list print("worksheets:"+sheet+"hasbeeninserted10000datas!") print("worksheets:"+sheet+"hasbeeninserted"+str(row_num)+"datas!") db.commit()#提交 cursor.close()#关闭连接 db.close() if__name__=='__main__': store_to('demo','demo_yangben','xxx.xlsx')
思考,如果数据插入有错误,怎么解决,
其实有很多数据库工具可以直接来解决这个问题,注意字符转换的格式就好。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持毛票票。