（自用）pymysql、Excel

 

import pandas as pd
import pymysql

 # 读取xls（绝对路径）
df_dict =pd.read_excel(io=r'E:\Desktop\atext.xlsx', sheet_name=None)
df_dict.keys()

for sheet_name,df in df_dict.items():
    print(f'sheet_name:{sheet_name}')
    print(df.ffill())#列名列表

pd.set_option('display.max_rows', 100)
pd.get_option('display.max_columns',100) 

# 重置
#pd.reset_option('display.max_rows')

def mysql_create(sheet_name,col_names): 
    # 建库和建表,user_id char(20),name char(20)
    con = pymysql.connect(host='localhost', user='root',
                          passwd='123456', charset='utf8')
    # 创建游标
    cur = con.cursor()

    # 创建数据库的sql(如果数据库存在就不创建，防止异常)
    sql = "CREATE DATABASE IF NOT EXISTS func_mate" 
    # 执行创建数据库的sql
    cur.execute(sql)

    # 使用库
    cur.execute("use func_mate;")
    
    all_col_name=""
    for col_name in col_names:
        all_col_name += col_name + " char(20),"
    all_col_name = all_col_name[:-1]
    print("all_col_name:",all_col_name)
    # 建表库名为awesome；建表，表名blogs；共有三列，列名 id，user_id, name
    cur.execute("create table IF NOT EXISTS %s(%s)character set utf8;"%(sheet_name,all_col_name))
#mysql_create("hara",["aa","bb","cc"])
    print("=====","sheet_name","成功创建")

 def mysql_select(): 
    # 查询操作
    # 1.建立连接，用户root 密码mysql123456 dbname：awesome
    db = pymysql.connect(host="localhost",
                         user='root',
                         passwd='123456',
                         database="func_mate")
    # 获取游标
    cur = db.cursor()
    # sql查询语句 表名blogs
    sql = 'select * from 功能清单'#mal_function  hara  功能清单
    try:
        cur.execute(sql)
        result = cur.fetchall()  # 返回所有数据
        #result = cur.fetchone()  # 返回一行数据
        #result = cur.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
        #列名
        col=cur.description
        col_name = []
        for i in range(len(col)):
            col_name.append(col[i][0])
        print(col_name)
        
        print(result)
    except Exception as e:
        db.rollback()
        print(e)
    finally:
        cur.close()
        db.close()
mysql_select()

 

def mysql_insert(sheet_name,col_names,list_data): 
    # 插入操作
    # 1.建立连接，用户root 密码mysql123456 dbname：awesome
    db = pymysql.connect(host="localhost",
                         user='root',
                         passwd='123456',
                         database="func_mate")
    # 获取游标
    cur_insert = db.cursor()

    #列名拼接
    all_col_name=""
    val_num=""
    for col_name in col_names:
        all_col_name += col_name + ","
        val_num += "%s,"
    all_col_name = all_col_name[:-1]#去除末尾逗号
    val_num = val_num[:-1]
    print("all_col_name==",all_col_name)
    print("val_num==",val_num)
    
    # 元组、列表形式传参
    sql_insert = 'insert into %s(%s) values(%s)'%(sheet_name,all_col_name,val_num)
    try:
        #cur_insert.execute(sql_insert, ("test_id",'test_user_id','test_name'))
        cur_insert.executemany(sql_insert, list_data)#批量插入，data为元组列表
        db.commit()
        print('开始数据库插入操作')
    except Exception as e:
        db.rollback()
        print('数据库插入操作错误回滚')
        print(e)
    finally:
        cur_insert.close()
        db.close()
 

 

def mysql_update():
    # 3.更新操作
    # 更新user_id = “test_user_id” 的行，将其 name 更新为 “update_test_name”
    db = pymysql.connect(host="localhost",
                         user='root',
                         passwd='123456',
                         database="func_mate")
    cur_update = db.cursor()

    sql_update = "update hara set name = '%s' where user_id = '%s'"
    try:
        cur_update.execute(sql_update % ("update_test_name", "test_user_id"))
        # 提交
        db.commit()
        print('开始数据库更新操作')
    except Exception as e:
        db.rollback()
        print('数据库更新操作错误回滚')
    finally:
        db.close()
 

 

def mysql_delete():
    # 4.删除操作
    # 删除 name = “update_test_name” 的行
    db = pymysql.connect(host="localhost",
                         user='root',
                         passwd='123456',
                         database="func_mate")
    cur_delete = db.cursor()

    sql_delete = "delete from hara where name = '%s'"

    try:
        cur_delete.execute(sql_delete % ("update_test_name"))  # 像sql语句传递参数
        # 提交
        db.commit()
        print('开始数据库删除操作')
    except Exception as e:
        db.rollback()
        print('数据库删除操作错误回滚')
    finally:
        db.close()

mysql_select()

 

def mysql_delete_all():
    # 5.删除整个库操作
    db = pymysql.connect(host="localhost",
                         user='root',
                         passwd='123456',
                         database="func_mate")
    cur_delete = db.cursor()

    sql_delete = "drop table hara"

    try:
        cur_delete.execute(sql_delete)  # 像sql语句传递参数
        # 提交
        db.commit()
        print('开始数据库删除操作')
    except Exception as e:
        db.rollback()
        print('数据库删除操作错误回滚')
    finally:
        db.close()
        
mysql_delete_all()
mysql_select()

 for sheet_name,df in df_dict.items():
    df=df.ffill()#填充合并单元格的nan
    print(f'sheet_name:{sheet_name}')
    print(df.columns)#列名列表
    #excel每页创建一个数据表与列名
    mysql_create(sheet_name, df.columns)
    
    #数据写入
    #遍历sheet每行
    list_data=[]
    for row in df.index.values :
        #每列生成一个元组
        tuple_data = tuple(df.iloc[row, i] for i in range(len(df.columns)))
        list_data.append(tuple_data)
    mysql_insert(sheet_name,df.columns,list_data)
    print("---------------","\n")