mongodb数据定时增量存mysql

概要

代码统一管理是我加入公司时才逐步规范管理的，在整理是否存在遗漏代码时，检查一个延保api查询服务代码，重点关注ods延保数据表，排除发现kettle同步商城mongodb数据出现问题，特此写了一个同步脚本分享如下

技术细节

简单一点就不写方法了

• 实现脚本，增量方式t-1,连接方式等信息我就去掉了

from pymongo import MongoClient
import pymysql
import datetime


client = MongoClient(host="172.24.xx.xxx", port=29997)
db = client["orderSyns"]
db.authenticate(name='xxxxl', password='xxxxxx')
collection = db["insuranceOrder"]

now = datetime.datetime.now()
yesterday = now - datetime.timedelta(days=1)
# 查询数据
data = collection.find({"opeTime": {"$gt": yesterday.strftime("%Y-%m-%d %H:%M:%S")}})

# 连接MySQL数据库
mydb = pymysql.connect(
    host="172.19.xx.xx",
    user="xxx",
    password="xxx#",
    database="ods"
)

cursor = mydb.cursor()
# 插入数据
for document in data:
    cursor.execute(" DELETE FROM ods_gd_insurancemsg WHERE orderId=%s", (document["orderId"],))
    sql = ("INSERT INTO ods_gd_insurancemsg (insuranceOrderId, orderId, insuranceType, brandNo, brandName, orderTime, "
           "customerName, mobile, mainStartTime, mainEndTime, notMainStartTime, notMainEndTime, modelNo, buyTime, "
           "customerUuid, storeUuid, state, buyPrice, insurancePrice, identityNum, opeTime, createOpeTime, modelType, "
           "fakeFlag) VALUES (%s, %s, %s, %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  %s,  "
           "%s,  %s,  %s,  %s,  %s,  %s)")
    values = (document.get("insuranceOrderId"), document.get("orderId"), document.get("insuranceType"), document.get("brandNo"),
              document.get("brandName"), document.get("orderTime"), document.get("customerName"), document.get("mobile"),
              document.get("mainStartTime"), document.get("mainEndTime"),
              document.get("notMainStartTime"), document.get("notMainEndTime"), document.get("modelNo"), document.get("buyTime"),
              document.get("customerUuid"),
              document.get("storeUuid"), document.get("state"), document.get("buyPrice"), document.get("insurancePrice"),
              document.get("identityNum"), document.get("opeTime"), document.get("createOpeTime"), document.get("modelType"),
              document.get("fakeFlag"))
    cursor.execute(sql, values)

# 提交事务
mydb.commit()

# 打印受影响的行数
print(cursor.rowcount, "records inserted.")

• 设置crontab加入定时任务，45分钟执行一次

crontab -e

*/45 * * * * python3 /data/mongodb2mysql/mongodb2mysql.py

小结

愿百花生长安时达的沃土上，齐放在我们流程IT部里面