SqlServer同步mongo模块

2021年11月26日 阅读数:7
这篇文章主要向大家介绍SqlServer同步mongo模块,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

基于业务需求,须要同步业务表中的数据字典到mongo中作数据对照表,找了相关资料,最终整理以下的模块,用于SqlServer数据同步到mongo中
1. 先将SqlServer中的表结构读取出来,并以keys的可迭代对象存储【注:注意看一下字段和值的对应关系,以防对应有误,致使数据错误】python

2. 再循环遍历数据即刻,用dict(zip(iter,iter))进行字典构造数据便可sql

# #!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time : 2021-09-27 14:10
# @Author : BruceLong
# @FileName: realChannel_map_sqlserver_to_mongo.py
# @Email   : 18656170559@163.com
# @Software: PyCharm
# @Blog :http://www.cnblogs.com/yunlongaimeng/
import pymssql
import time

import pymongo


class SqlServerToMongo:
    def __init__(self):
        self.cd_mongo = pymongo.MongoClient(
            "mongodb://localhost:27017/")
        self.coll = self.cd_mongo['CDDict']['ChannelRealChannelDict']
        self.conn = pymssql.connect('127.0.0.1', 'admin', 'admin', 'Dict')
        self.cur = self.conn.cursor()
        self.count = 0
        self.limit = 1000
        self.table = 'Channel_mapping'

    def query_sqlserver(self):
        self.cur.execute(f"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{self.table}'")
        ctime = time.strftime('%Y-%m-%d', time.localtime(time.time() - 60 * 60 * 24 * 3))
        print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
        keys = [row[3] for row in self.cur.fetchall()]
        self.cur.execute(f"SELECT * FROM [dbo].[{self.table}] WHERE CreateTime>N'{ctime}'")
        for row in self.cur.fetchall():
            data = dict(zip(keys, row))
            data['CreateTime'] = data['CreateTime'].strftime('%Y-%m-%d %H:%M:%S')
            data['gne_sign'] = 0
            item = {
                'Channel': data.get('Channel'),
                'realChannel': data.get('realChannel'),
                'SpiderName': data.get('SpiderName'),
                'owner': data.get('UNAME'),
                'ctime': data.get('CreateTime'),
                'gne_sign': 0,

            }
            # print(item)
            self.coll.update_one({'realChannel': item['realChannel']}, {"$set": item}, upsert=True)
            # return

        pass

    def run(self):
        self.query_sqlserver()
        pass


if __name__ == '__main__':
    app = SqlServerToMongo()
    app.run()