从Excel中读取数据并批量写入MySQL数据库(基于MySQLdb)

从Excel中读取数据并批量写入MySQL数据库(基于MySQLdb)
强烈推介IDEA2021.1.3破解激活,IntelliJ IDEA 注册码,2021.1.3IDEA 激活码 

大家好,我是架构君,一个会写代码吟诗的架构师。今天说一说从Excel中读取数据并批量写入MySQL数据库(基于MySQLdb),希望能够帮助大家进步!!!

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

从Excel中读取数据并批量写入MySQL数据库(基于MySQLdb)

二、连接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()

转载于:https://www.cnblogs.com/python-kp/p/11151535.html

本文来源weixin_30595035,由架构君转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/29697

发表评论