使用pymysql将excel表格的数据导入mysql数据库（CRUD）（附代码）_python

通过这个就感觉到写代码的时候逻辑有多么重要

把excel中的数据存到数据库里面 ① 所用资料链接

把excel的数据转换到数据库，使用数据库的一些语句进行 *** 作

pymysql 的使用以及 commit 与roback
PyMySQL
使用pymysql创建数据库
pymysql数据库之建库建表、增删改查
安装mysql
Python中对数组的 *** 作小结
python字符串中插入变量的三种方法
MySQL 插入数据
MySql中insert语句中value和values
Python 使用PyMySQL数据库交互工具，查询MySQL数据表的所有字段(表头)
conda命令
怎么查看mysql数据库服务是否开启？
doker安装mysql

DBeaver
DBeaver安装及使用手册

sys.modules['hs'].__dict__.clear()

Python编程——sys模块的使用详解
python sys.modules模块

② 数据库语言 1）创建表和数据库

"CREATE DATABASE IF NOT EXISTS db_name"
"CREATE TABLE `info` (`id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,”

2）表中插入内容

"INSERT INTO `info`(`gender`) VALUES{values}".format(values = value)
for：
row_data = sheet.row_values(i) # row_data第i行的数据
value = (row_data[0]),row_data[1])

3）查找得到某一列

"select q_2,q_3,q_7,hs_std from mmpidb.hs"
origindata = cursor.fetchall()

4）删除某一行

elif info_l_f_k_std[i][0] > 70.0 or info_l_f_k_std[i][1] > 70.0:
     delete = "delete from info WHERE id = {id}".format(id=info_id[i][0])
     cursor.execute(delete)

代码实例 ①excel导入数据库

创建数据库（CREATE DATABASE）
创建表（CREATE TABLE ( 表头）
把excel的数据读入mysql 使用value暂存数据，数据库语言读入
sheet = open_excel()
sql = “INSERT INTO info(gender）VALUES{values}”.format(values = value)

# -*- coding: UTF-8 -*-
'''
从Excel中读取原表格文件，并生成数据库mmpidb
'''

import xlrd
import pymysql


#连接数据库，创建info表格存储excel中的数据
try:
    # 打开数据库连接
    #conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='yunxi2',db='newmmpidb',charset='utf8mb4')
    
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = conn.cursor()
   
    # 创建数据库的sql(如果数据库存在就不创建，防止异常)
    sql = "CREATE DATABASE IF NOT EXISTS db_name"
    # 执行创建数据库的sql
    cursor.execute(sql)
    # 使用 execute() 方法执行 SQL，如果表存在则删除
    cursor.execute("drop table if EXISTS info")

    # 使用预处理语句创建表(省掉了`birthdate``testdate` 两个字段  excel日期格式导入数据库的问题还未解决)
    # originalanswer` VARCHAR(1000) 需要改动一下 表头顺序  `originalanswer` VARCHAR(1000)  , `originalanswer`
    sql = """CREATE TABLE `info` (
                `id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
                `gender` VARCHAR(20),
                `age`  FLOAT,
                `originalanswer` VARCHAR(2000)                               
                )"""
    cursor.execute(sql)
    # 关闭游标
    cursor.close()
    print("创建成功")
except Exception :
    print("创建失败")


def open_excel():
    file = xlrd.open_workbook("exceldata/firstgroup/female-＞35.xlsx")
    # 读取表
    sheet = file.sheet_by_name("Sheet1")
    return sheet


#向info表格中插入数据
def insert_data():
    sheet = open_excel()
    cursor = conn.cursor()
    row_num = sheet.nrows# 行号
    
    //一行一行插入
    for i in range(1, row_num):  # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
        row_data = sheet.row_values(i) # row_data第i行的数据
        value = (pymysql.escape_string(row_data[0]),row_data[1],row_data[2],row_data[3],row_data[4],))
        print(value)
        sql = "INSERT INTO `info`(`gender`, `age`, `hy`, `hy_std`, `sc`, `sc_std`, `pa`, `pa_std`, `pt`, `pt_std`, `d`, `d_std`, `pd`, `pd_std`, `ma`, `ma_std`, `mf`, `mf_std`, `si`, `si_std`, `hs`, `hs_std`, `originalanswer`) VALUES{values}".format(values = value)
        cursor.execute(sql)  # 执行sql语句
        conn.commit()
    cursor.close()  # 关闭连接


insert_data()

conn.close()  # 关闭数据
print("导入数据库成功")

②从数据库的表中查找数据（select）

直接用select（列名）语句

def obtain_hs_data():
    try:
        # 打开数据库连接
        conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='gyd123', db='mmpidb', charset='utf8')
        cursor = conn.cursor()
        
        sql = "select q_2,q_3,q_7,hs_std from mmpidb.hs"
        cursor.execute(sql)
        origindata = cursor.fetchall()
        return origindata
        
        # 关闭游标
        cursor.close()
        conn.close()
        print("创建成功")
    except Exception:
        print("创建失败")

③ 删除数据库中的数据

删除LKF> 70的数据
一定要记住数据库的数据的格式是m*n(行，列)

遍历所有行（select id）
提取某一列（k）的数据
①select requestionnum from mmpidb.info m*1 info_requestionnum[i][0]
② select l_std,f_std,k_std from m*3
info_l_f_k_std[i][0] [i][1] [i][2]
for循环 — 不满足的删除
delete from info WHERE id = {id}".format(id=info_id[i][0])

def clearn():
    try:
        # 获取数据库连接的游标
        cursor = conn.cursor()

        #获取数据库info表中的重复题目不一致requestionnum数据
        sql = "select requestionnum from mmpidb.info"
        cursor.execute(sql)
        info_requestionnum = cursor.fetchall()

        # 获取数据库info表中的不能确定的题目notsurenum数据
        sql1 = "select notsurenum from mmpidb.info"
        cursor.execute(sql1)
        info_notsurenum = cursor.fetchall()

        # 获取数据库info表中的id数据
        sql2 = "select id from mmpidb.info"
        cursor.execute(sql2)
        info_id = cursor.fetchall()

        # 清理 l_std>70, f_std>70, k_std>70 的无效问卷
        sql3 = "select l_std,f_std,k_std from mmpidb.info"
        cursor.execute(sql3)
        info_l_f_k_std = cursor.fetchall()

        print(info_l_f_k_std)
        print(info_l_f_k_std[1][0])
        print(info_l_f_k_std[1][1])
        print(info_l_f_k_std[1][2])

		//一行一行检查 是否删除
        for i in range(len(info_id)):
            if info_requestionnum[i][0] + info_notsurenum[i][0] > 30.0:          #判断两者数量大于30则通过id进行删除数据
                delete = "delete from info WHERE id = {id}".format(id = info_id[i][0])
                cursor.execute(delete)
            elif info_l_f_k_std[i][0] > 70.0 or info_l_f_k_std[i][1] > 70.0 or info_l_f_k_std[i][2] > 70.0:
                delete = "delete from info WHERE id = {id}".format(id=info_id[i][0])
                cursor.execute(delete)
            conn.commit()

        cursor.close()  #关闭游标

        print("连接成功，清理无效数据成功")
    except Exception:
        print("失败")

④ 从数据库的表中提取数据形成另一个表（select）

把数据库中的一列数据（A,B,A…）拆成多列

建表
获得数据
插入数据

获得这一列的数据（select 列名）
for 循环（行数）

	sql = """CREATE TABLE `mf` (
                        `id` INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
                        `u_id` INT,
                        `mf` FLOAT,
                        `q_1` VARCHAR(20), `q_4` VARCHAR(20),

        
        #获取info origindata原始问卷数据
        sql1 = "select originalanswer from mmpidb.info"

        #获取info id数据
        sql2 = "select id from mmpidb.info"
		
		//一行一行插入的
        for i in range(len(info_originalanswer)):
            s = info_originalanswer[i][0]    #获取原始数据元组
            s = s.replace(",", "")    #去除,
            u_id = info_id[i][0]   #获取用户id
            mf = info_mf[i][0]

            #分割获取原始数据关于MF量表相关的选项
            v = (u_id, g, mf, mf_std, s[0], s[3], s[18], s[24], s[25], s[27], s[68], s[69],

            print(v)
            sql = "INSERT INTO `mf`( `u_id`, `gender`, `mf`, `mf_std`, `q_1`, `q_4`, `q_19`, `q_25`,"  \
                  "VALUES{values}".format(values=v)
            cursor.execute(sql)  # 执行sql语句
            conn.commit()

        cursor.close()
        print("MF量表成功")
    except Exception:
        print("MF量表失败")