excel文件数据上传到MySQL数据库

方法1：使用MySQL的LOAD DATA语句

如果Excel文件已经保存为CSV格式，可以使用MySQL的LOAD DATA INFILE语句将数据导入表中。例如：

LOAD DATA LOCAL INFILE 'path/to/your/file.csv'  
INTO TABLE your_table_name  
FIELDS TERMINATED BY ','   
ENCLOSED BY '"'   
LINES TERMINATED BY '\n'  
IGNORE 1 ROWS;  -- 如果你的CSV文件包含标题行，使用此选项忽略它

方法2：使用pymysql

 先复习一下基础语句：

--CREATE TABLE: 这是一个SQL命令，用于创建一个新的表。
--IF NOT EXISTS: 这是一个可选的子句，用于在尝试创建表之前检查该表是否已经存在。

CREATE TABLE IF NOT EXISTS your_table_name (    
    id INT AUTO_INCREMENT PRIMARY KEY,    
    column1 VARCHAR(255),    
    column2 VARCHAR(255),      
);

在括号()内，定义了表的列和数据类型：
id INT AUTO_INCREMENT PRIMARY KEY:
--id: 列的名称。
--INT: 这指定了列的数据类型，即整数。
--AUTO_INCREMENT: 这是一个属性，用于指示数据库自动为新记录分配一个唯一的递增值。
  这通常用于主键-列，以确保每个记录都有一个唯一的标识符。
--PRIMARY KEY: 这指定了该列作为表的主键。
  主键是唯一标识表中每条记录的列或列组合。主键列中的值必须是唯一的，并且不能为NULL。

column1 VARCHAR(255):
--column1: 列的名称。
--VARCHAR(255): 这指定了列的数据类型和最大长度。
  VARCHAR是一种可变长度的字符数据类型，括号中的数字255指定了该列可以存储的最大字符数。

column2 VARCHAR(255): 与column1类似，定义了另一个可变长度的字符列，最大长度为255个字符。

步骤： 

1. 读取Excel文件并将其加载到Pandas DataFrame中。
2. 使用pymysql连接到MySQL数据库。
3. 将DataFrame中的数据写入MySQL表。
4. 执行SQL查询以清洗数据库中的数据。

import pandas as pd  
import pymysql  
  
# 读取Excel文件  
excel_file = 'path/to/your/excel_file.xlsx'  
df = pd.read_excel(excel_file)  
  
# MySQL数据库连接信息  
host = 'localhost'  
user = 'your_username'  
password = 'your_password'  
db = 'your_database'  
charset = 'utf8mb4'  
  
# 使用pymysql连接到MySQL数据库  
connection = pymysql.connect(host=host,  
                             user=user,  
                             password=password,  
                             db=db,  
                             charset=charset,  
                             cursorclass=pymysql.cursors.DictCursor # 指定游标类型为DictCursor  
                             )  
  
try:  
    with connection.cursor() as cursor:  # 创建了一个游标对象，可以用来执行SQL语句
        # 根据数据创建表，如果表已存在则跳过此步
        # 确保create_table_query中的列名和数据类型与表结构匹配
        # sql插入语句中的列名与DataFrame中的列名匹配
        create_table_query = """  
        CREATE TABLE IF NOT EXISTS your_table_name (  
            id INT AUTO_INCREMENT PRIMARY KEY,  
            column1 VARCHAR(255),  
            column2 VARCHAR(255),  
            ...  
        )  
        """  
        cursor.execute(create_table_query)  
  
        # 插入数据到MySQL表  
        sql = "INSERT INTO your_table_name (column1, column2, ...) VALUES (%s, %s, ...)"  
        val = [(row['column1'], row['column2'], ...) for index, row in df.iterrows()] 
        # 列表推导式。该列表的每个元素都是一个元组，包含了从DataFrame的相应行中提取的列值。
        # df.iterrows(): 返回DataFrame中的索引和每一行数据。每次迭代都会返回一对值：索引和该行数据的Series对象。 
        # for index, row in df.iterrows(): 遍历DataFrame的每一行。index变量存储了当前行的索引，而row变量存储了当前行的数据（作为一个Series对象）。
        # row['column1'], row['column2'], ...: 这部分代码从当前行（即row）中提取指定列的值。
   
        cursor.executemany(sql, val)  
      
    # 提交，确保插入操作生效
    connection.commit()  
  
    # 清洗数据（此处以删除column1为空的行示例）  
    with connection.cursor() as cursor:  
        clean_query = "DELETE FROM your_table_name WHERE column1 IS NULL"  
        cursor.execute(clean_query)  
      
    # 再次提交  
    connection.commit()  
  
finally:  
    # 关闭数据库连接  
    connection.close()

ps：

pymysql批量插入数据-一个更简单的示例

import pymysql  
  
config = {  
    'host': 'localhost',  
    'user': 'your_username',  
    'password': 'your_password',  
    'db': 'your_database',  
    'charset': 'utf8mb4',  
    'cursorclass': pymysql.cursors.DictCursor  
}  
  
try:  
    # 建立连接  
    connection = pymysql.connect(**config)  
    # **是一个语法糖，用于将一个字典解包为关键字参数。
    # 当你函数调用中使用**前缀跟着一个字典变量时，Python会自动将该字典中的每一对键-值解包为单独的命名参数，并将它们传递给函数。
  
    with connection.cursor() as cursor:  
        # 准备要插入的数据  
        sql = "INSERT INTO users (email,password) VALUES (%s, %s)"  
        data = [  
            ('user1@example.com', 'password1'),  
            ('user2@example.com', 'password2'),  
            ('user3@example.com', 'password3'),  
            ...  
        ]  
          
        # 批量插入  
        cursor.executemany(sql, data)  
      
    # 提交事务  
    connection.commit()  
finally:  
    # 关闭连接  
    connection.close()