如何将csv导入mysql数据库

CREATE TABLE my_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    email VARCHAR(255)
);

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- 如果CSV文件有标题行，忽略第一行

pip install pandas mysql-connector-python

import pandas as pd
from mysql.connector import connect

# 读取CSV文件
df = pd.read_csv('path_to_your_file.csv')

# 连接到MySQL数据库
conn = connect(host='localhost', user='your_username', password='your_password', database='your_database')
cursor = conn.cursor()

# 将DataFrame数据插入到MySQL表中
for index, row in df.iterrows():
    query = "INSERT INTO my_table (name, age, email) VALUES (%s, %s, %s)"
    values = (row['name'], row['age'], row['email'])
    cursor.execute(query, values)

# 提交事务并关闭连接
conn.commit()
cursor.close()
conn.close()

SET NAMES utf8mb4;

try:
    cursor.execute(query, values)
except Exception as e:
    print(f"Error inserting row {index}: {e}")

# 批量插入示例
batch_size = 1000
for i in range(0, len(df), batch_size):
    batch = df[i:i+batch_size]
    data = [tuple(x) for x in batch.values]
    cursor.executemany(query, data)