mysql 从txt导入

LOAD DATA INFILE 'path/to/your/file.txt'
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' -- 分隔符，根据实际情况修改
ENCLOSED BY '"' -- 如果字段值包含分隔符，可以使用引号包围
LINES TERMINATED BY '\n' -- 行分隔符，根据实际情况修改
IGNORE 1 LINES; -- 如果TXT文件第一行是标题行，可以忽略

import mysql.connector

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 打开TXT文件
with open('path/to/your/file.txt', 'r') as file:
    for line in file:
        # 根据实际情况分割数据
        data = line.strip().split(',')
        # 插入数据到数据库
        query = "INSERT INTO your_table_name (column1, column2, column3) VALUES (%s, %s, %s)"
        cursor.execute(query, (data[0], data[1], data[2]))
        db.commit()

cursor.close()
db.close()

GRANT FILE ON *.* TO 'yourusername'@'localhost';