mysql导入excel数据

-- 创建表
CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

-- 导入数据
LOAD DATA INFILE 'path/to/your/file.csv'
INTO TABLE example
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

pip install pandas mysql-connector-python

import pandas as pd
import mysql.connector

# 读取Excel文件
df = pd.read_excel('path/to/your/file.xlsx')

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

# 创建游标
cursor = conn.cursor()

# 创建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS example (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
)
""")

# 插入数据
for index, row in df.iterrows():
    cursor.execute("""
    INSERT INTO example (id, name, age) VALUES (%s, %s, %s)
    """, (row['id'], row['name'], row['age']))

# 提交事务
conn.commit()

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

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