读取excel到mysql数据库中_从Excel中读取数据并写入MySQL数据库

一、Excel内容时这样的：

二、最初的代码是这样的：

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

import pymysql

from xlrd import open_workbook

class DB:

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

host = "xx.xx.xx.xxx"

username = "root"

password = "xxxx"

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 = pymysql.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 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()

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

for i in range(0,len(content)):

id = content[i][0]

begin_date = str(content[i][1])

end_date = content[i][2]

in_num = content[i][3]

out_num = content[i][4]

all_num = content[i][5]

create_time = content[i][6]

use_time = content[i][7]

scope_date = content[i][8]

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)" %(id, begin_date, end_date, in_num, out_num, all_num, create_time, use_time, scope_date)

mysqlInfo.executeSQL(sql)

mysqlInfo.closeDB()