mysql 异构数据同步

import pandas as pd

# 示例：将CSV文件转换为MySQL表
def csv_to_mysql(csv_file, table_name):
    df = pd.read_csv(csv_file)
    # 数据清洗和格式转换
    df = df.dropna()
    # 连接MySQL数据库
    conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
    cursor = conn.cursor()
    # 创建表
    create_table_sql = f"CREATE TABLE {table_name} (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)"
    cursor.execute(create_table_sql)
    # 插入数据
    for index, row in df.iterrows():
        insert_sql = f"INSERT INTO {table_name} (name, age) VALUES ('{row['name']}', {row['age']})"
        cursor.execute(insert_sql)
    conn.commit()
    cursor.close()
    conn.close()

csv_to_mysql('data.csv', 'users')

-- 示例：根据更新时间合并数据
INSERT INTO users (id, name, age, update_time)
SELECT id, name, age, update_time
FROM source_table
ON DUPLICATE KEY UPDATE
name = IF(source_table.update_time > users.update_time, source_table.name, users.name),
age = IF(source_table.update_time > users.update_time, source_table.age, users.age);

# 示例：批量插入数据
def batch_insert(data, table_name):
    conn = mysql.connector.connect(user='user', password='password', host='host', database='database')
    cursor = conn.cursor()
    insert_sql = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"
    cursor.executemany(insert_sql, data)
    conn.commit()
    cursor.close()
    conn.close()

data = [('Alice', 30), ('Bob', 25), ('Charlie', 35)]
batch_insert(data, 'users')