mongodb导出到mysql

# 导出 MongoDB 数据为 JSON 格式
mongoexport --db your_database --collection your_collection --out your_collection.json

# 将 JSON 文件导入 MySQL
mysql -u your_username -p your_database < your_collection.sql

import pymongo
import mysql.connector

# 连接 MongoDB
mongo_client = pymongo.MongoClient("mongodb://localhost:27017/")
mongo_db = mongo_client["your_database"]
mongo_collection = mongo_db["your_collection"]

# 连接 MySQL
mysql_conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
mysql_cursor = mysql_conn.cursor()

# 导出数据
data = mongo_collection.find()
for document in data:
    # 将 MongoDB 文档转换为 MySQL 插入语句
    columns = ', '.join(document.keys())
    values = ', '.join(['%s'] * len(document))
    sql = f"INSERT INTO your_table ({columns}) VALUES ({values})"
    mysql_cursor.execute(sql, tuple(document.values()))

# 提交事务
mysql_conn.commit()

# 关闭连接
mongo_client.close()
mysql_cursor.close()
mysql_conn.close()

document['_id'] = str(document['_id'])  # 将 ObjectId 转换为字符串

try:
    mysql_conn.start_transaction()
    # 执行插入操作
    mysql_conn.commit()
except Exception as e:
    mysql_conn.rollback()
    print(f"Error: {e}")

batch_size = 1000
data = mongo_collection.find().batch_size(batch_size)
for i in range(0, len(data), batch_size):
    batch = list(data)[i:i + batch_size]
    # 执行批量插入操作