基于业务需求,需要同步业务表中的数据字典到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 : [email protected]
# @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()