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)建一個(gè)新表并導(dǎo)入excel數(shù)據(jù)
代碼示例
注意：telphone為數(shù)據(jù)庫(kù)名揭厚；yhtest為新創(chuàng)建的表名字
import pymysql
import xlrd
# 讀取excel中內(nèi)容到數(shù)據(jù)庫(kù)
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)入已有的表中，不需要?jiǎng)?chuàng)建新表）
代碼示例
注意：telphone為數(shù)據(jù)庫(kù)名；login為原有的表名字
import pymysql
import xlrd
# 讀取excel中內(nèi)容到數(shù)據(jù)庫(kù)
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表
