Python 将excel数据导入数据库

2021-11-16

import xlrd
import pymysql


# 打开excel文件
def open_excel():
    try:
        book = xlrd.open_workbook("123.xlsx")  # 文件名，把文件与py文件放在同一目录下
    except:
        print("open excel file failed!")

    try:
        sheet = book.sheet_by_name("sheet1")  # execl里面的worksheet1
        return sheet
    except:
        print("locate worksheet in excel failed!")


# 连接数据库
try:
    db = pymysql.connect(host="127.0.0.1", user="root",
                         passwd="3105501510",
                         db="xiaoyang",
                         charset='utf8')
except:
    print("could not connect to mysql server")


def search_count():
    cursor = db.cursor()
    select = "select count(*) from tiku"  # 获取表中记录数
    cursor.execute(select)  # 执行sql语句
    line_count = cursor.fetchone()
    print("当前数据库中一共" + str(line_count[0]) + "条记录")


def insert_deta():
    sheet = open_excel()
    cursor = db.cursor()
    for i in range(1, sheet.nrows):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1

        id = sheet.cell(i, 0).value  # 取第i行第0列
        _type = sheet.cell(i, 1).value  # 取第i行第1列，下面依次类推
        if _type == "单选":
            _type = 1
        elif _type == "多选":
            _type = 2
        elif _type == "判断":
            _type = 3
        else:
            _type = 0

        title = sheet.cell(i, 2).value
        option_a = sheet.cell(i, 3).value
        option_b = sheet.cell(i, 4).value
        option_c = sheet.cell(i, 5).value
        option_d = sheet.cell(i, 6).value
        answer = sheet.cell(i, 7).value
        page = sheet.cell(i, 8).value
        source = sheet.cell(i, 9).value

        value = (id, _type, title, option_a, option_b, option_c, option_d, answer, page, source)
        print(value)
        try:
            sql = "INSERT INTO tiku (id,type,title,option_a,option_b,option_c,option_d,answer,page,source)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql, value)  # 执行sql语句
            db.rollback()
        except:
            print("SQL执行错误！")
            break
        
        db.commit()
    cursor.close()  # 关闭连接


# insert_deta()

print("ok ")
if __name__ == '__main__':
    search_count()
    insert_deta()
    db.close()  # 关闭数据