方法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个字符。
步骤:
- 读取Excel文件并将其加载到Pandas DataFrame中。
- 使用
pymysql
连接到MySQL数据库。 - 将DataFrame中的数据写入MySQL表。
- 执行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()