Excel导入MySQL.py

import xlrd
import pymysql
from time import time

initialize = time()
# 打开数据所在的工作簿，以及选择存有数据的工作表
book = xlrd.open_workbook("C:/Users/Administrator/Desktop/protocol.xlsx")
# 选择哪一张表，例如：protocol
excel_protocol_explain = book.sheet_by_name("说明")
excel_protocol_classify_v1 = book.sheet_by_name("分类V1")
excel_protocol_classify_v2 = book.sheet_by_name("分类V2")
excel_protocol_classify_v1v2 = book.sheet_by_name("V1_V2")
excel_protocol_classify_appfeature = book.sheet_by_name("应用特征")
excel_protocol_classify_contenttype = book.sheet_by_name("content_type")
excel_protocol_classify_osinfo = book.sheet_by_name("OSINFO")
excel_protocol_classify_terminaloslanguage = book.sheet_by_name("TERMINAL_OS_LANGUAGE")
excel_protocol_classify_networkprotocol = book.sheet_by_name("network_protocol")
excel_protocol_classify_tcpport = book.sheet_by_name("tcp_port")
excel_protocol_classify_udpport = book.sheet_by_name("udp_port")

# 建立一个MySQL连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='000578',
    db='saike',
    port=3306,
    charset='utf8'
)
# 获得游标
cur = conn.cursor()
# sql语句
sql_excel_protocol_explain = \
    'INSERT INTO excel_protocol_explain ' \
    '(VersionNumber, ModifyReason, ModifyDate, ModifiedBy, AppAdd,AppDel, AppAlterFormer, AppAlterLater, TraitAdd, TraitDel, TraitAlterFormer, TraitAlterLater) value ' \
    '("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'

sql_excel_protocol_classify_v1 = \
    'INSERT INTO excel_protocol_classify_v1 ' \
    '(Family,FamilyName,Remark,Example) value ' \
    '("%s","%s","%s","%s")'

sql_excel_protocol_classify_v2 = \
    'INSERT INTO excel_protocol_classify_v2 ' \
    '(Family,FamilyName,FamilyDefine) value ' \
    '("%s","%s","%s")'

sql_excel_protocol_classify_v1v2 = \
    'INSERT INTO excel_protocol_classify_v1v2 ' \
    '(FamilyV1,FamilyNameV1,FamilyV2,FamilyNameV2,FamilyDefineV2) value ' \
    '("%s","%s","%s","%s","%s")'

sql_excel_protocol_classify_appfeature = \
    'INSERT INTO excel_protocol_classify_appfeature ' \
    '(FeatureId35902,AppName,App,FamilyV1,FamilyV2,MetadataFlag,Feature,ICP,Origin,AppId27875,Label,ModifyLog,Mark,Oversea,TestDate,TestLab) value ' \
    '("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'

sql_excel_protocol_classify_contenttype = \
    'INSERT INTO excel_protocol_classify_contenttype ' \
    '(FeatureContentType,StreamType,FeatureTypeFlag,ContentSurffixName) value ' \
    '("%s","%s","%s","%s")'

sql_excel_protocol_classify_osinfo = \
    'INSERT INTO excel_protocol_classify_osinfo ' \
    '(TerminalType,OsVersion) value ' \
    '("%s","%s")'
sql_excel_protocol_classify_terminaloslanguage = \
    'INSERT INTO excel_protocol_classify_terminaloslanguage ' \
    '(TerminalOsLanguageName,TerminalOsLanguage) value ' \
    '("%s","%s")'

sql_excel_protocol_classify_networkprotocol = \
    'INSERT INTO excel_protocol_classify_networkprotocol ' \
    '(ProtoName,FamliyName,Top) value ' \
    '("%s","%s","%s")'

sql_excel_protocol_classify_tcpport = \
    'INSERT INTO excel_protocol_classify_tcpport ' \
    '(ProtoName,PortoPort,PortFlag) value ' \
    '("%s","%s","%s")'

sql_excel_protocol_classify_udpport = \
    'INSERT INTO excel_protocol_classify_udpport ' \
    '(ProtoName,PortoPort,PortFlag) value ' \
    '("%s","%s","%s")'

print('初始化用时：%s' % (time() - initialize))


def def_excel_protocol_explain():
    row = excel_protocol_explain.nrows
    col = excel_protocol_explain.ncols
    starttime = time()
    # 创建一个for循环迭代读取xls文件每行数据的, 从第一行开始
    for r in range(3, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_explain.cell(r, c).value
            rowls.append(' '.join(str(cell).split('\n')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_explain % (rowls[0], rowls[1], rowls[2], rowls[3],
                                                      rowls[4], rowls[5], rowls[6], rowls[7],
                                                      rowls[8], rowls[9], rowls[10], rowls[11]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_explain 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_v1():
    row = excel_protocol_classify_v1.nrows
    col = excel_protocol_classify_v1.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_v1.cell(r, c).value
            rowls.append(' '.join(str(cell).split('\n')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_v1 % (rowls[0], rowls[1], rowls[2], rowls[3]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_v1 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_v2():
    row = excel_protocol_classify_v2.nrows
    col = excel_protocol_classify_v2.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_v2.cell(r, c).value
            rowls.append(' '.join(str(cell).split('\n')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_v2 % (rowls[0], rowls[1], rowls[2]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_v2 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_v1v2():
    row = excel_protocol_classify_v1v2.nrows
    col = excel_protocol_classify_v1v2.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_v1v2.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_v1v2 % (rowls[0], rowls[1], rowls[2], rowls[3], rowls[4]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_v1v2 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_appfeature():
    row = excel_protocol_classify_appfeature.nrows
    col = excel_protocol_classify_appfeature.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_appfeature.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        # print(sql_excel_protocol_classify_appfeature % (rowls[0], rowls[1], rowls[2], rowls[3], rowls[4]))
        try:
            cur.execute(sql_excel_protocol_classify_appfeature % (rowls[0], rowls[1], rowls[2], rowls[3],
                                                                  rowls[4], rowls[5], rowls[6], rowls[7],
                                                                  rowls[8], rowls[9], rowls[10], rowls[11],
                                                                  rowls[12], rowls[13], rowls[14], rowls[15]
                                                                  ))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_appfeature 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_contenttype():
    row = excel_protocol_classify_contenttype.nrows
    col = excel_protocol_classify_contenttype.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_contenttype.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_contenttype % (rowls[0], rowls[1], rowls[2], rowls[3]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_contenttype 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_osinfo():
    row = excel_protocol_classify_osinfo.nrows
    col = excel_protocol_classify_osinfo.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_osinfo.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_osinfo % (rowls[0], rowls[1]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_osinfo 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_terminaloslanguage():
    row = excel_protocol_classify_terminaloslanguage.nrows
    col = excel_protocol_classify_terminaloslanguage.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_terminaloslanguage.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_terminaloslanguage % (rowls[0], rowls[1]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_terminaloslanguage 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_networkprotocol():
    row = excel_protocol_classify_networkprotocol.nrows
    col = excel_protocol_classify_networkprotocol.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_networkprotocol.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_networkprotocol % (rowls[0], rowls[1], rowls[2]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_networkprotocol 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_tcpport():
    row = excel_protocol_classify_tcpport.nrows
    col = excel_protocol_classify_tcpport.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_tcpport.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_tcpport % (rowls[0], rowls[1], rowls[2]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_tcpport 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


def def_excel_protocol_classify_udpport():
    row = excel_protocol_classify_udpport.nrows
    col = excel_protocol_classify_udpport.ncols
    starttime = time()
    for r in range(1, row):
        rowls = []
        for c in range(0, col):
            cell = excel_protocol_classify_udpport.cell(r, c).value
            rowls.append(','.join(str(cell).split('\n' and '\"')))
            # 执行sql语句
        try:
            cur.execute(sql_excel_protocol_classify_udpport % (rowls[0], rowls[1], rowls[2]))
        except EOFError as e:
            print(e)
    conn.commit()
    alltime = time() - starttime
    print("def_excel_protocol_classify_udpport 导入 %s 列 %s 行数据到MySQL数据库! 用时 %s 秒" % (col, row, alltime))


if __name__ == '__main__':
    starttime = time()
    def_excel_protocol_explain()  # 说明
    def_excel_protocol_classify_v1()  # 分类V1
    def_excel_protocol_classify_v2()  # 分类V2
    def_excel_protocol_classify_v1v2()  # V1_V2
    def_excel_protocol_classify_appfeature()
    def_excel_protocol_classify_contenttype()
    def_excel_protocol_classify_osinfo()
    def_excel_protocol_classify_terminaloslanguage()
    def_excel_protocol_classify_networkprotocol()
    def_excel_protocol_classify_tcpport()
    def_excel_protocol_classify_udpport()

    alltime = time() - starttime
    print('处理完毕，共用时%s' % (alltime))