excel中数据写入mysql_从Excel中读取数据并批量写入MySQL数据库（基于MySQLdb）

一、Excel内容如下，现在需要将Excel中的数据全部写入的MySQL数据库中：

二、连接MySQL的第三方库使用的是“MySQLdb”,代码如下：

# -*- coding:utf-8 -*-

import MySQLdb

from xlrd import open_workbook

class DB:

global host,username,password,port,database,config

host = "10.10.xx.xx"

username = "root"

password = "xxxxx"

port = 3306

database = "pythondb"

config = {

'host': str(host),

'user': username,

'passwd': password,

'port': int(port),

'db': database

}

def __init__(self):

self.db = None

self.cursor = None

def connectDB(self):

try:

self.db = MySQLdb.connect(**config)

self.cursor = self.db.cursor()

print "Connect DB successfully!"

except:

print "Connect DB failed!"

def executeSQL(self,sql):

self.connectDB()

self.cursor.execute(sql)

self.db.commit()

return self.cursor

def executeMany(self,sql,params):

self.connectDB()

self.cursor.executemany(sql,params)

self.db.commit()

return self.cursor

def getAll(self,cursor):

value = cursor.fetchall()

return value

def getOne(self,cursor):

value = cursor.fetchone()

return value

def closeDB(self):

self.db.close()

print "Database closed!"

def get_xls(self,xls_name, sheet_name):

"""

get interface data from xls file

:return:

"""

cls = []

# open xls file

file = open_workbook(xls_name)

# get sheet by name

sheet = file.sheet_by_name(sheet_name)

# get one sheet's rows

nrows = sheet.nrows

for i in range(1,nrows):

cls.append(sheet.row_values(i))

return tuple(cls)

if __name__ == "__main__":

mysqlInfo = DB()

params = mysqlInfo.get_xls("DataSource.xls","Sheet1")

sql = "INSERT INTO `day`( ID, BEGIN_DATE, END_DATE, IN_NUM, OUT_NUM, ALL_NUM, CREATE_TIME, USE_TIME, SCOPE_DATE ) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"

mysqlInfo.executeMany(sql,params)

mysqlInfo.closeDB()