python3.4.1导excel进mysql数据库

# coding=gbk
import pymysql
##读取excel使用(支持03)
import xlrd
from datetime import datetime
from xlrd import xldate_as_tuple
from builtins import int

##打开数据库
#conn=pymysql.connect(host='192.168.0.200',user='root',passwd='123456',db='db_casino',port=3310,charset='utf8')
conn=pymysql.connect(host='localhost',user='root',passwd='root',db='db_casino',port=3306,charset='utf8')
##打开游标
cur=conn.cursor()

##将excel文件导入mysql中
def importExcelToMysql(path):
  workbook=xlrd.open_workbook(path)
  sheets=workbook.sheet_names();
  worksheet=workbook.sheet_by_name(sheets[0])
  ##遍历行
  for i in range(1,worksheet.nrows):
    row=worksheet.row(i)
    
   ##初始化数组
    sqlstr=[]
    ##遍历列
    for j in range(0,worksheet.ncols):
      ##构造数组
      sqlstr.append(worksheet.cell_value(i,j))
      
    ##打印信息 
    #print(int(sqlstr[3]),"\t",int(sqlstr[4]),"\t",int(sqlstr[8]),"\t",end="")
      
    ##插入数据库
    ##tb_game_type表  
    #valuestr=[int(sqlstr[0]),str(sqlstr[1]),str(sqlstr[2]),int(sqlstr[3]),int(sqlstr[4]),str(sqlstr[5]),str(sqlstr[6]),str(sqlstr[7]),int(sqlstr[8]),'admin','1432178411236','admin','1432178411236','']

    ##tb_play_type表
    valuestr=[int(sqlstr[0]),str(sqlstr[1]),int(sqlstr[2]),int(sqlstr[3]),int(sqlstr[4]),'admin','1432178411236','admin','1432178411236']
   
    ##tb_bet_loss表
    #valuestr=[int(sqlstr[0]),str(sqlstr[1]),str(sqlstr[2]),float(sqlstr[3]),int(sqlstr[4]),float(sqlstr[5]),int(sqlstr[6]),int(sqlstr[7]),int(sqlstr[8]),'admin','1432178411236','admin','1432178411236']

   ##tb_bet_type表
    #valuestr=[int(sqlstr[0]),str(sqlstr[1]),int(sqlstr[2]),str(sqlstr[3]),int(sqlstr[4]),str(sqlstr[5]),int(sqlstr[6]),int(sqlstr[7]),str(sqlstr[8]),str(sqlstr[9]),int(sqlstr[10]),str(sqlstr[11]),str(sqlstr[12]),int(sqlstr[13]),'admin','1432178411236','admin','1432178411236']

    #print(valuestr,"\t",end="")
   
    ##插入数据库
    ##tb_game_type表
    #cur.execute("insert into tb_game_type(id,NAME,MAX_WIN_MONEY,ENABLED,DEEP,CLOSE_BEGIN_DATE,CLOSE_END_DATE,BONUS_DIFF,GAME_TYPE,CREATED_BY,CREATED_DATE,LAST_MODIFIED_BY,LAST_MODIFIED_DATE,CODE) "+
    #           "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",valuestr)

    ##tb_play_type表
    cur.execute("insert into tb_play_type(id,NAME,GAME_TYPE,DEEP,ENABLED,CREATED_BY,CREATED_DATE,LAST_MODIFIED_BY,LAST_MODIFIED_DATE) "+
               "values(%s,%s,%s,%s,%s,%s,%s,%s,%s)",valuestr)
   
    ##tb_bet_loss表
    #cur.execute("insert into tb_bet_loss(ID,NAME,BALANCER,MILTIPLE,BASE_LINE,BASE_SCALE,BET_COUNT_LIMIT,MAX_WIN_MONEY,ENABLED,CREATED_BY,CREATED_DATE,LAST_MODIFIED_BY,LAST_MODIFIED_DATE) " +
    #            "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",valuestr)

   ##tb_bet_type表
    #cur.execute("insert into tb_bet_type(ID,NAME,PLAY_TYPE,BET_TYPE_GROUP_NAME,BET_TYPE_GROUP,PLAY_TYPE_NAME,BALANCERID,DEEP,DESCRIPTION,HELP,"+
    #            "MAX_PRIZE_NUMS,EXAMPLE,STYLE,ENABLED,CREATED_BY,CREATED_DATE,LAST_MODIFIED_BY,LAST_MODIFIED_DATE) " +
    #            "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",valuestr)

  cur.close()
  conn.commit()
  conn.close()
  #打印信息 
  print("恭喜，数据导入成功！")

##tb_game_type表
#read03path=r"F:\caizhong\gametype.xls";
##tb_play_type表
read03path=r"F:\caizhong\playtype.xls";
##tb_bet_loss表
#read03path=r"F:\caizhong\betloss.xls";
##tb_bet_loss表
#read03path=r"F:\caizhong\bettype.xls";

##调用函数
importExcelToMysql(read03path);