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; -- 如果文件包含标题行，忽略第一行

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)

# 提交更改
db.commit()

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