mysql批量导入csv

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 忽略 CSV 文件的第一行（通常是标题行）

SET NAMES 'utf8mb4';
LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

import mysql.connector
import csv

# 连接到 MySQL 数据库
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# 打开 CSV 文件
with open('file.csv', newline='') as csvfile:
    reader = csv.reader(csvfile)
    next(reader)  # 忽略标题行
    for row in reader:
        # 构建插入语句
        sql = "INSERT INTO your_table (column1, column2, column3) VALUES (%s, %s, %s)"
        cursor.execute(sql, row)

# 提交事务
db.commit()

# 关闭连接
cursor.close()
db.close()