Python如何将数据从dataframe插入到MySQL

pip install pandas mysql-connector-python

import mysql.connector

def create_conn():
    conn = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    return conn

from sqlalchemy import create_engine

def insert_dataframe_to_sqlite(df, db_name, table_name):
    engine = create_engine(f'sqlite:///{db_name}')
    df.to_sql(table_name, con=engine, if_exists='replace', index=False)

def insert_dataframe_to_mysql(conn, df, table_name):
    cursor = conn.cursor()

    # 获取列名
    columns = ', '.join(df.columns)

    # 构建插入数据的SQL语句
    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({', '.join(['%s'] * len(df.columns))})"

    # 将DataFrame数据转换为元组列表
    data = [tuple(x) for x in df.values]

    # 执行批量插入
    cursor.executemany(sql, data)
    conn.commit()

import pandas as pd
import mysql.connector

def create_conn():
    conn = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    return conn

def insert_dataframe_to_mysql(conn, df, table_name):
    cursor = conn.cursor()

    # 获取列名
    columns = ', '.join(df.columns)

    # 构建插入数据的SQL语句
    sql = f"INSERT INTO {table_name} ({columns}) VALUES ({', '.join(['%s'] * len(df.columns))})"

    # 将DataFrame数据转换为元组列表
    data = [tuple(x) for x in df.values]

    # 执行批量插入
    cursor.executemany(sql, data)
    conn.commit()

# 示例数据
data = {
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35]
}
df = pd.DataFrame(data)

# 连接到MySQL数据库
conn = create_conn()

# 将DataFrame数据插入到MySQL表中
insert_dataframe_to_mysql(conn, df, 'your_table_name')

# 关闭连接
conn.close()