Read Excel data insert to MySQL

Because of the work required, so test the use of Python to read Excel data to MySQL, under this record.

#!/usr/bin/env python#coding:utf-8import xlrdimport mysqldbimport datetimexlsfile=r ' c \ Users\xuwu\desktop\data.xlsx ' Book = xlrd.open_workbook (xlsfile) #获取sheet的数量count  = len ( Book.sheets ())   #设置连接数据库database  = mysqldb.connect (host= ' 192.168.1.30 ', user= ' root ', passwd= ' 123456 ', db= ' CRM ') #设置字符集database. Set_character_set (' UTF8 ') cursor = database.cursor () Cursor.execute (' set names utf8; ')  cursor.execute (' set character set utf8; ') Cursor.execute (' set character_set_connection=utf8; ') Starttime = datetime.datetime.now () print  ' Start time:%s '  %  (starttime) #循环sheetfor  i  in range (0,count-1):     sheet = book.sheet_by_index (i)       query =  "" "insert into bill_test  ( member_id, name,  tel, phone, dq_datetime, address, parking)  VALUES  ( %s, %s, %s, %s, %s, %s, %s) "" "     #循环每一行     for r  in range (1, sheet.nrows):         #idseq        = sheet.cell (r,0). value        member_id  = sheet.cell (r,1) .value        name           = sheet.cell (r,2) .value         tel     = sheet.cell (r,3) .value         phone       = sheet.cell (r,4) .value         dq_datetime = sheet.cell (r,5) .value         address        = sheet.cell (r,6) .value        &nbsP;parking       = sheet.cell (r,7) .value          values =  (Member_id, name, tel, phone, dq_datetime,  address, parking)         cursor.execute (query, values)      cursor.close () Database.commit () Database.close () Endtime=datetime.datetime.now () print  ' End time:%s '  %  (endtime) print  ' spents:%s  seconds '  %  (endtime-starttime)

Test table structure:

CREATE TABLE ' bill_test ' (' Idseq ' mediumint () unsigned not null auto_increment, ' member_id ' int (ten) DEFAULT NULL, ' n Ame ' varchar ' default NULL, ' tel ' varchar default NULL, ' phone ' varchar default NULL, ' Dq_datetime ' varchar ( Default NULL, ' address ' varchar ($) default null, ' parking ' varchar ($) default NULL, PRIMARY KEY (' idseq ')) ENGI Ne=myisam DEFAULT Charset=utf8 |

Implementation of the program:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M01/8B/C4/wKioL1hYoXSiNwtuAADXfPyv7M0787.png-wh_500x0-wm_3 -wmp_4-s_245419418.png "title=" execution Program "alt=" Wkiol1hyoxsinwtuaadxfpyv7m0787.png-wh_50 "/>

Query Result:

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8B/C4/wKioL1hYobSwSewvAAAyvakEdsI052.png-wh_500x0-wm_3 -wmp_4-s_568280851.png "title=" Query Data "alt=" Wkiol1hyobswsewvaaayvakedsi052.png-wh_50 "/>

This article is from the "Xu Mingjiang blog" blog, make sure to keep this source http://xumingjiang.blog.51cto.com/703960/1884191

Read Excel data insert to MySQL