python导入excel到mysql


import xlrd import pymysql import sys,os

import importlib
importlib.reload(sys) #出现呢reload错误使用


def open_excel():
    try:
        book = xlrd.open_workbook("service_list.xls")  # 文件名,把文件与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="10.0.32.150", user="",
                         passwd="",
                         db="vue",
                         charset='utf8')
except:
    print("could not connect to mysql server")


def search_count():
    cursor = db.cursor()
    select = "select count(id) from hello_equipment"  # 获取表中xxxxx记录数
    cursor.execute(select)  # 执行sql语句
    line_count = cursor.fetchone()
    print(line_count[0])


def insert_deta():
    sheet = open_excel()
    cursor = db.cursor()
    for i in range(1, sheet.nrows):  # 第一行是标题名,对应表中的字段名所以应该从第二行开始,计算机以0开始计数,所以值是1
        int_ip = sheet.cell(i, 0).value  # 取第i行第0列
        int_code = sheet.cell(i, 1).value  # 取第i行第1列,下面依次类推
        serial_num = sheet.cell(i, 2).value  # 取第i行第1列,下面依次类推
        remote_ctrl = sheet.cell(i, 3).value  # 取第i行第1列,下面依次类推
        model = sheet.cell(i, 4).value  # 取第i行第1列,下面依次类推
        area = sheet.cell(i, 5).value  # 取第i行第1列,下面依次类推
        cabinet = sheet.cell(i, 6).value  # 取第i行第1列,下面依次类推
        number = sheet.cell(i, 7).value  # 取第i行第1列,下面依次类推
        state = sheet.cell(i, 8).value
        # print(name)
        # print(data)
        value = (int_ip, int_code, serial_num, remote_ctrl, model, area, cabinet, number, state)
        print(value)
        sql = "INSERT INTO hello_equipment(int_ip, int_code, serial_num, remote_ctrl, model, area, cabinet, number, state)VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        cursor.execute(sql, value)  # 执行sql语句
        db.commit()
    cursor.close()  # 关闭连接


insert_deta()
db.close()  # 关闭数据
print("ok ")