python实现mongodb数据导入到mysql

最新推荐文章于 2024-07-30 14:29:27 发布

柯之梦

最新推荐文章于 2024-07-30 14:29:27 发布

阅读量2.3k

点赞数

分类专栏： python 文章标签： python

本文链接：https://blog.csdn.net/yin4302008/article/details/89606203

版权

python 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

在实际项目中需要把mongodb数据库的数据迁移到mysql数据库，由于该功能只使用一次，因此使用python实现，代码量比较小，特此奉上自己的代码，供大家参考，谢谢！如有问题，请在下方留言，我会及时的回复。

from pymongo import MongoClient
import pymysql
import time
#连接mysql数据库  后面加上 charset="utf8mb4" 这样方便传输汉字，解决字符集不匹配的问题
client1 = pymysql.connect("localhost","数据库账号","数据库密码","数据库名",charset="utf8mb4")
#定义mysql数据库的游标
cursor = client1.cursor()
#连接Momgo数据库
client = MongoClient('localhost',27017)
#连接相应的collection
db = client.organization.orgProductsScope
sql = "INSERT INTO orgProductsScope (orgId,isSharedByCity,districtName,cityId,provinceId,seriesId,isSharedByProvince,districtId,cityName,isSharedBydistrict,brandId,disabled,provinceName,isShared,ctime,mtime) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
#遍历mongo数据库，加入batch_size(30)方法解决解决 MongoDB 的 cursor id is not valid at server 问题
for i in db.find().batch_size(30):
    orgId = int(i["orgId"])
    relationOrgProductsScope = i["relationOrgProductsScope"]
    for j in relationOrgProductsScope:
        print(j)
        isSharedByCity = 0
        if 'isSharedByCity' in j:
            if j["isSharedByCity"] != "":
                isSharedByCity = int(j["isSharedByCity"])

        districtName = ""
        if 'districtName' in j:
            districtName = j["districtName"]

        cityId = ""
        if 'cityId' in j:
            cityId = j["cityId"]

        provinceId = ""
        if 'provinceId' in j:
            provinceId = j["provinceId"]

        seriesId = ""
        if 'seriesId' in j:
            seriesId = j["seriesId"]

        isSharedByProvince = "0"
        if 'isSharedByProvince' in j:
            isSharedByProvince = j["isSharedByProvince"]

        districtId = ""
        if 'districtId' in j:
            districtId = j["districtId"]

        cityName = ""
        if 'cityName' in j:
            cityName = j["cityName"]

        isSharedByCity = "0"
        if 'isSharedByCity' in j:
            isSharedBydistrict = j["isSharedByCity"]

        brandId = "0"
        if 'brandId' in j:
            brandId = j["brandId"]

        disabled = 0
        if 'disabled' in j:
            if j["disabled"] != "":
                disabled = int(j["disabled"])

        provinceName = ""
        if 'provinceName' in j:
            provinceName = j["provinceName"]

        isShared = 0
        if 'isShared' in j:
            if j["isShared"] != "":
                isShared = int(j["isShared"])
        # 向表orgProductsScope插入相应的数据
        par = (
        orgId, isSharedByCity, districtName, cityId, provinceId, seriesId, isSharedByProvince, districtId, cityName,
        isSharedBydistrict, brandId, disabled, provinceName, isShared,time.localtime(),time.localtime())
        try:
            cursor.execute(sql, par)
            client1.commit()
        except Exception as e:
            client1.rollback()
            print(e)
cursor.close()
client.close()