def mongo_to_mysql(self):
# 将数据从mongo到mysql
dbu = DBUtil()
dbu.create_session_factory(
con_str='mysql+pymysql://root:[email protected]:3306/ncov?charset=utf8mb4?connect_timeout=30')
dbs = DBSession(dbu.get_new_session())
n = 0
self.client = MongoClient("mongodb://{}:{}/".format(self.host, self.port))
self.database = self.client["ncov"]
while 1:
self.collection = self.database["DXYArea"]
query = {}
cursor = self.collection.find(query)
sql = 'insert into dxyarea (_id,comment,confirmedCount,country,createTime,curedCount,deadCount,modifyTime,operator,provinceName,provinceShortName,suspectedCount,updateTime) values(:_id,:comment,:confirmedCount,:country,:createTime,:curedCount,:deadCount,:modifyTime,:operator,:provinceName,:provinceShortName,:suspectedCount,:updateTime)'
for doc in cursor:
doc['_id'] = str(doc['_id'])
doc['updateTime'] = str(doc['updateTime'])
try:
doc['modifyTime'] = str(doc['modifyTime'])
except:
doc['modifyTime'] = None
try:
doc['createTime'] = str(doc['createTime'])
except:
doc['createTime'] = None
try:
doc['operator'] = str(doc['operator'])
except:
doc['operator'] = None
try:
if doc['cities']!='':
if isinstance(doc['cities'],list) is False:
doc['cities'] = json.loads(doc['cities'])
for r in doc['cities']:
sql2 = 'insert into dxyarea_city (dxyarea_id,cityName,deadCount,curedCount,suspectedCount,confirmedCount) values(:dxyarea_id,:cityName,:deadCount,:curedCount,:suspectedCount,:confirmedCount)'
r['dxyarea_id']=doc['_id']
try:
dbs.exec_sql(sql2, r)
except Exception as e:
emsg = (repr(e))
if 'Duplicate entry' in emsg:
pass
else:
logger.exception('遇到一个问题')
dbs.commit()
# else:
# print(doc['cities'])
except:
logger.exception('')
# print(doc['cities'])
pass
try:
dbs.exec_sql(sql, doc)
except Exception as e:
emsg = (repr(e))
if 'Duplicate entry' in emsg:
pass
else:
logger.exception('遇到一个问题')
dbs.commit()
self.collection = self.database["DXYOverall"]
query = {}
cursor = self.collection.find(query)
sql = 'insert into dxyoverall (_id,abroadRemark,confirmedCount,countRemark,curedCount,dailyPic,deadCount,generalRemark,infectSource,passWay,remark1,remark2,remark3,remark4,remark5,summary,suspectedCount,updateTime,virus) values(:_id,:abroadRemark,:confirmedCount,:countRemark,:curedCount,:dailyPic,:deadCount,:generalRemark,:infectSource,:passWay,:remark1,:remark2,:remark3,:remark4,:remark5,:summary,:suspectedCount,:updateTime,:virus)'
for doc in cursor:
doc['_id'] = str(doc['_id'])
doc['updateTime'] = str(doc['updateTime'])
try:
doc['abroadRemark'] = str(doc['abroadRemark'])
except:
doc['abroadRemark'] = None
try:
doc['generalRemark'] = str(doc['generalRemark'])
except:
doc['generalRemark'] = None
try:
dbs.exec_sql(sql, doc)
except Exception as e:
emsg = (repr(e))
if 'Duplicate entry' in emsg:
pass
else:
logger.exception('遇到一个问题')
dbs.commit()
logger.debug('转换数据成功')
sleep(30)
