1.環(huán)境介紹：

• python版本：3.6.4
• pymysql: 0.8.0
• xlrd: 1.1.10

pymysql安裝
pip install pymysql

xlwt安裝
pip install xlrd

2.創(chuàng)建一個新表并導(dǎo)入excel數(shù)據(jù)

代碼示例
注意：telphone為數(shù)據(jù)庫名；yhtest為新創(chuàng)建的表名字

import pymysql
import xlrd

# 讀取excel中內(nèi)容到數(shù)據(jù)庫
workbook = xlrd.open_workbook('./yhxt.xls')
sheet = workbook.sheet_by_index(0)
data_list= []
nrows = sheet.nrows # 行數(shù)
ncols = sheet.ncols # 列數(shù)
fo = []

fo.append(sheet.row_values(0))
for i in range(1,nrows):
    data_list.append(sheet.row_values(i))

conn=pymysql.connect(host='localhost',user='root',passwd='password',db='telphone',charset='utf8')  
cursor=conn.cursor()  
#創(chuàng)建table  
cursor.execute("create table yhtest("+fo[0][0]+" varchar(100));")  
#創(chuàng)建table屬性  
for i in range(1,ncols):  
    cursor.execute("alter table yhtest add "+fo[0][i]+" varchar(100);")  
val=''  
for i in range(0,ncols):  
    val = val+'%s,'  
print(data_list)  
  
cursor.executemany("insert into yhtest values("+val[:-1]+");" ,data_list)  
conn.commit()

導(dǎo)入新建yhtest表

3.導(dǎo)入原有的表中（excel數(shù)據(jù)導(dǎo)入已有的表中，不需要創(chuàng)建新表）

代碼示例
注意：telphone為數(shù)據(jù)庫名；login為原有的表名字

import pymysql
import xlrd

# 讀取excel中內(nèi)容到數(shù)據(jù)庫
workbook = xlrd.open_workbook('./yhxt.xls')
sheet = workbook.sheet_by_index(0)
data_list= []
nrows = sheet.nrows # 行數(shù)
ncols = sheet.ncols # 列數(shù)
fo = []

fo.append(sheet.row_values(0))
for i in range(1,nrows):
    data_list.append(sheet.row_values(i))

conn=pymysql.connect(host='localhost',user='root',passwd='password',db='telphone',charset='utf8')  
cursor=conn.cursor()

# #創(chuàng)建table  
# cursor.execute("create table yhtest("+fo[0][0]+" varchar(100));")

# #創(chuàng)建table屬性  
# for i in range(1,ncols):  
#     cursor.execute("alter table yhtest add "+fo[0][i]+" varchar(100);")  

val=''  
for i in range(0,ncols):  
    val = val+'%s,'  
print(data_list)  
  
cursor.executemany("insert into login values("+val[:-1]+");" ,data_list)  
conn.commit()

導(dǎo)入原有(已有)login表