mysql 从txt导入数据库中

id,name,age
1,Alice,25
2,Bob,30
3,Charlie,35

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

import mysql.connector
import csv

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

cursor = db.cursor()

# 打开TXT文件
with open('/path/to/data.csv', 'r') as file:
    reader = csv.reader(file)
    next(reader)  # 跳过标题行
    for row in reader:
        cursor.execute("INSERT INTO your_table_name (id, name, age) VALUES (%s, %s, %s)", row)

# 提交事务
db.commit()

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

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table_name
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;