Excel表导入mysql

最新推荐文章于 2025-12-18 08:31:44 发布

原创最新推荐文章于 2025-12-18 08:31:44 发布 · 99 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python #mysql

该代码示例展示了如何使用xlrd库读取Excel文件中的数据，并利用pymysql将数据批量插入到MySQL数据库中。首先，遍历Excel文件的指定行，创建Question类的对象并填充属性，然后将这些对象存储到列表中。最后，通过连接数据库，执行SQL插入语句，将数据逐条写入Text01表。

import xlrd
import xlwt
import pymysql

data = xlrd.open_workbook("data.xls")
sheet1 = data.sheet_by_index(0)
questionList = []
class Question:
    pass

for i in range(sheet1.nrows):
    if i > 4:
        obj = Question() #构建对象
        obj.FNumber = sheet1.cell(i,0).value
        obj.FParentID = sheet1.cell(i,1).value
        obj.FIsBizUnit = sheet1.cell(i,2).value
        obj.FLeader = sheet1.cell(i,3).value
        obj.FCostCenterType = sheet1.cell(i,4).value
        obj.FCreatorNumber = sheet1.cell(i,5).value
        obj.FCreateTime = sheet1.cell(i,6).value
        print(obj)
        print("++++++==================================")
        questionList.append(obj)
print(questionList)

db = pymysql.Connect(host='localhost', port=3306, user='root', passwd='root',db='mysql',charset='utf8')
cursor = db.cursor()
# 插入语句
def insertSql(s1,s2,s3,s4,s5,s6,s7):
    sql = "insert into Text01(FNumber,FParentID,FIsBizUnit,FLeader,FCostCenterType,FCreatorNumber,FCreateTime) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')"
    sql=sql.format(s1,s2,s3,s4,s5,s6,s7)
    print(sql)
    cursor.execute(sql)
    db.commit()
val = []
for item in questionList:
    val.append((item.FNumber,item.FParentID,item.FIsBizUnit,item.FLeader,item.FCostCenterType,item.FCreatorNumber,item.FCreateTime))
for ch in val:
    num = 0
    while num < len(ch):
        print(ch[num])
        num=num+1
    insertSql(ch[0],ch[1],ch[2],ch[3],ch[4],ch[5],ch[6])