python怎么导入表格数据库_4.python实现Excel导入Mysql数据库

1.环境介绍：

python版本：3.6.4

pymysql: 0.8.0

xlrd: 1.1.10

pymysql安装

pip install pymysql

xlwt安装

pip install xlrd

2.创建一个新表并导入excel数据

代码示例

注意：telphone为数据库名；yhtest为新创建的表名字

import pymysql

import xlrd

# 读取excel中内容到数据库

workbook = xlrd.open_workbook('./yhxt.xls')

sheet = workbook.sheet_by_index(0)

data_list= []

nrows = sheet.nrows # 行数

ncols = sheet.ncols # 列数

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()

#创建table

cursor.execute("create table yhtest("+fo[0][0]+" varchar(100));")

#创建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()

导入新建yhtest表

3.导入原有的表中(excel数据导入已有的表中，不需要创建新表)

代码示例

注意：telphone为数据库名；login为原有的表名字

import pymysql

import xlrd

# 读取excel中内容到数据库

workbook = xlrd.open_workbook('./yhxt.xls')

sheet = workbook.sheet_by_index(0)

data_list= []

nrows = sheet.nrows # 行数

ncols = sheet.ncols # 列数

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()

# #创建table

# cursor.execute("create table yhtest("+fo[0][0]+" varchar(100));")

# #创建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()

导入原有(已有)login表