Excel表导入mysql

最新推荐文章于 2024-09-15 22:31:42 发布

hazry

最新推荐文章于 2024-09-15 22:31:42 发布

阅读量60

点赞数

本文链接：https://blog.csdn.net/hazry/article/details/117671737

版权

关键词由CSDN通过智能技术生成

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])