mysql csv文件导入

LOAD DATA INFILE 'file.csv'
INTO TABLE table_name
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
';

LOAD DATA INFILE 'file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE LINES WITH LESS THAN 5 OR MORE THAN 5 FIELDS;

import mysql.connector
import csv

# 连接MySQL数据库
cnx = mysql.connector.connect(user='username', password='password',
                              host='hostname', database='database_name')
cursor = cnx.cursor()

# 打开CSV文件
with open('file.csv', 'r', encoding='utf-8-sig') as csvfile:
    csvreader = csv.reader(csvfile)
    header = next(csvreader)  # 跳过表头

    # 构建插入语句
    insert_stmt = "INSERT INTO table_name (field1, field2, field3) VALUES (%s, %s, %s)"

    # 执行插入操作
    for row in csvreader:
        cursor.execute(insert_stmt, row)

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