基于业务需求,需要同步业务表中的数据字典到mongo中做数据对照表,找了相关资料,最终整理如下的模块,用于SqlServer数据同步到mongo中
1. 先将SqlServer中的表结构读取出来,并以keys的可迭代对象存储【注:注意看一下字段和值的对应关系,以防对应有误,导致数据错误】
2. 再循环遍历数据即刻,用dict(zip(iter,iter))进行字典构造数据即可
# #!/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()