excel 写入mysql 实例

import xlrd
import time
import pymysql.cursors


connect = pymysql.Connect(
    host='127.0.0.1',
    user='zabbix',
    passwd='123456',
    db='cc',
    use_unicode=True,
    charset="utf8"
)
cursor = connect.cursor()

def de(x,y):
    a=table.cell(x,y).value
    return a

def add_id(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p):
    sql = "INSERT INTO ZXLB (" \
          "sheet_name," \
          "Date_start," \
          "ZX_ID," \
          "OSS_ID," \
          "system_ID," \
          "system_CPSL,"\
          "customer_name," \
          "a_side_dev," \
          "a_side_port," \
          "c_vlan," \
          "c_connect_ip," \
          "c_user_ip," \
          "c_gateway," \
          "boundary," \
          "c_bandwidth," \
          "c_type" \
          ") VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
    data=(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p)
    cursor.execute(sql % data)
    connect.commit()

time_start=time.time()
data=xlrd.open_workbook('副本总线路表-office 2013版.xlsx',encoding_override='utf-8')
table = data.sheet_by_name(u'09年后新网及专线资源')


count=1
for i in range(100):
    add_id('09sheet',str(count),de(i,6),de(i,8),de(i,9),de(i,10),de(i,11),de(i,19),de(i,21),de(i,32),de(i,33),de(i,34),de(i,35
),de(i,37),de(i,39),de(i,40))

    #print(1,'09sheet')
    #print(str(count))
    #print(de(i,6))
    #print(de(i,8))
    #print(de(1,9))


time_end=time.time()
print(int(time_end-time_start))

转载于:https://www.cnblogs.com/abyssce/p/7492935.html