使用Python将Excel文件中的数据导入MySQL

一、准备工作：安装两个Package——xlrd与pymysql；

• xlrd：用于将.xls与.xlsx等格式文件中的数据导出；
• pymysql：用于Python与MySQL的连接；

安装指令(在cmd中)：

pip install xlrd
pip install pymysql

二、示例介绍：

# -*- coding: utf-8 -*-
"""
Created on Thu Apr  2 15:01:56 2020

@author: JiangMingHan
"""


import xlrd
import pymysql


# Excel文件的路径
filename = 'D:\Code\Python\MIT6.0001\Student.xls'
    

# 获得一个xls对象
file = xlrd.open_workbook(filename)   

# 根据sheet页的排序选取sheet，参数为0返回sheet1，若参数为1则返回sheet2
sheet_1 = file.sheet_by_index(0) 

# row_valuesz()方法用于获取指定行的数据，参数为行数，以0为始
row_content = sheet_1.row_values(2) 
# cell()方法用于获取表中某一格的数据，参数分别为行数与列数
cell_content = sheet_1.cell(45,0).value

# nrows字段记录的是有效数据的最大行数
row_number = sheet_1.nrows 
# nrows字段记录的是有效数据的最大列数
col_number = sheet_1.ncols


# 下面开始对数据库进行操作 


# 建立mysql连接,返回database对象
db = pymysql.connect(
    host = 'localhost',
    user='root',
    passwd='jmh181949',
    db='database4se',
    port=3306,
    charset='utf8'
        )

# 创建游标对象
cursor = db.cursor()

# 问题：Excel中有46行数据，我使用了print输出了读取到的所有的数据，数据并未丢失，但是在数据库中却少了两个记录；

# 按行遍历表格
for row in range(1,row_number):
    # 将一行中每个字段的值都使用一个变量存放
    idd = sheet_1.cell(row,0).value
    name = sheet_1.cell(row,1).value
    sex = sheet_1.cell(row,2).value
    birthday = sheet_1.cell(row,3).value
    race = sheet_1.cell(row,4).value
    address = sheet_1.cell(row,5).value
    
    # 将每行的数据输出到屏幕，方便了解数据提取的情况
    print(idd,name,sex,birthday,race,address)
    
    # 将单行数据打包成一个元组
    value = (idd, name, sex, birthday, race, address)
    # 创建插入SQ语句，注意VALUES后面的%s需要使用单引号括起来以表示其为字符串
    sql = "INSERT INTO STUDENT(ID, NAME, SEX, BirthDay, RACE, ADDRESS) VALUES('%s', '%s','%s','%s','%s','%s')"%value
    
    try:
        # 执行sql
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 发生错误时回滚
        db.rollback()    

print("Done!")

# 关闭游标
cursor.close()
# 关闭数据库
db.close()


# =============================================================================
# # MySQL读取操作
# sql1= "SELECT * FROM STUDENT"
# 
# # 执行sql语句
# cursor.execute(sql1)
# 
# # result是一个二维元组，第一维是数据库列表的行，第二位是各字段值
# result = cursor.fetchall()
# print(result)
# =============================================================================

三、问题：

在上述示例中我使用的Excel文件中有46行数据，在读取数据时，通过print在屏幕上打印出了所有数据，但是在数据库中却少了两个记录，原因暂不明；