python3学习笔记之六——从excel表读取数据，利用字符串格式化方式，将数据写入mysql数据库

预备知识1：从excel表中读取数据，通过xlrd实现。

#打开工作表

ex = xlrd.open_workbook('d:\student.xlsx')

#操作工作表的sheet页,第一页

sheet = ex.sheet_by_index(0)

#获取行数和列数

rows = sheet.nrows

cols = sheet.ncols

#获取一行的数据

data = sheet.row_values('行号')

#获取单元格的数据

data = sheet.cell('行号','列号').value

预备知识2：mysql数据库操作

#数据库连接 ，通过pymysql模块

db = pymysql.connect(host='localhost',user='',password='',db='',port='')

#设置游标，靠它进行数据库操作

cursor = db.cursor()

#创建数据库表格

create table  表名（字段，类型）;

#向数据表中插入列

alter table 表名 add column 字段 类型；

#向表格中插入数据

insert into 表名（字段） values(数据)

准备工作完成后，就可以进行读取数据，插入数据库表格了。完整代码如下：

import pymysql
import xlrd

def excel_to_sql():
    excelf = xlrd.open_workbook(r'd:\student.xlsx')
    sheet1 =excelf.sheet_by_index(0)
    #取第一行表头数据作为插入创建表的字段
    firstrow = sheet1.row_values(0)
    sqlcol=','.join(firstrow)  #用于后面插入数据
    #取第二行的数据，为插入字段的数据类型
    secondrow = sheet1.row_values(1)
    #取第第一列的第一行，第二行数据作为创建表的初始值
    firstdata = sheet1.cell(0,0).value  #字段
    firsttype = sheet1.cell(1,0).value  #类型
    #将excel表中存储的类型对应到数据之中，string对应字符型 
    if firsttype == 'string':
        firsttype = 'char(10)'
    else:
        firsttype = firsttype  #int类型
    cols = sheet1.ncols  #总列数
    rows = sheet1.nrows  #总行数

    #连接数据库
    db = pymysql.connect(host='localhost',user='test01',password='123456',db='testdb',port=3306)
    #设置游标，数据的操作就靠它了
    cursor = db.cursor() 

    #创建STUDENT表，其中数据的字段和类型通过字符串格式化插入
    sql = 'DROP TABLE IF EXISTS STUDENT;'
    cursor.execute(sql)
    sql = "CREATE TABLE STUDENT(%s %s);" % (firstdata,firsttype)
    cursor.execute(sql)
    
    #向STUDENT表中插入列，通过for循环从excel表中取出列的字段和类型
    for i in range(1,cols):
        colname = firstrow[i] #剩余的字段
        coltype = secondrow[i] #剩余的字段类型
        if coltype == 'string':
            coltype = 'char(10)'
        else:
            coltype = coltype
        sql = "ALTER TABLE STUDENT\
                ADD COLUMN %s %s;" % \
                (colname,coltype)
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()

    #将数据插入已经创建好的表格中
    for i in range(2,rows): #数据是从第三行开始的
        student = sheet1.row_values(i)
        for j in range(0,len(student)):
            if isinstance(student[j],float):
                student[j] = '%d' % student[j] #这里携程”‘%d’“这样也可以，mysql里面‘121’也能插入整型字段中
            else:
                student[j] = "'%s'" % student[j]  #这里一定要加一个引号，不然字符型不带引号就不能插入数据库             
        student1 = ','.join(student)#一次性取一行数据                     
        sql = "INSERT INTO STUDENT(%s)\
                values(%s);" %\
                (sqlcol,student1)    #这里有一点值得注意的是mysql中int型的字段中，既可以插入12，也可以插入‘12’
        try:
            cursor.execute(sql)
            db.commit()
        except:
            db.rollback()

    db.close()
                

excel_to_sql()

执行结果：

#student.xlsx