Python crawls Excel table data to MySQL table

#encoding =utf-8import xlrdimport mysqldbdata=xlrd.open_workbook (' test.xlsx ') table=data.sheets () [0]nrows= Table.nrowsncols=table.ncolstabledict={}for i in range (nrows): Tabledict[i]=table.row_values (i) print tabledict[2] Print tuple (tabledict[2]) #读取数据try: conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' 1234 ', db= ' test ',    port=3306,charset= ' UTF8 ') cur=conn.cursor () cur.execute (' Select Name, sex from classmate ') Result_set=cur.fetchall () For row in Result_set:print row print "Number of rows returned:%d"%cur.rowcount cur.close () Conn.close () ex Cept mysqldb.error,e:print "MySQL Error%d:%s"% (E.args[0],e.args[1]) #插入数据try: conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' 1234 ', db= ' test ', port=3306,charset= ' UTF8 ') cur=conn.cursor () sql1= "DROP table IF EXISTS   exceltable; "   Cur.execute (SQL1) print "Drop success!" sql2= "CREATE table IF not EXISTS exceltable (col1 varchar () primary key, col2 varchar (), col3 Int ())" Cur.execute (s   QL2)Print "sucess to create a new table!"   #列表转元组, Tabledict[i] #通过添加数据到列表中, and then to tuples, because tuples are immutable. sql3= "INSERT into exceltable (COL1,COL2,COL3) VALUES (%s,%s,%s)" param01=[] for I in range (nrows): Param01.append ( Tuple (Tabledict[i])) param02=tuple (param01) print param02 #多行数据 try:cur.executemany (SQL3,PARAM02) CO   Nn.commit () print "Success Insert many records!" Except Exception,e:conn.rollback () Print e cur.close () conn.close () except Mysqldb.error,e:print "mysq  L Error%d:%s "% (E.args[0],e.args[1])

For specific instructions and details, see the comments in the source code.

Python crawls Excel table data to MySQL table