Python实现读取SQLServer数据并插入到MongoDB数据库的方法示例

# -*- coding: utf-8 -*-import pyodbcimport osimport csvimport pymongofrom pymongo import ASCENDING, DESCENDINGfrom pymongo import MongoClientimport binascii'''连接mongoDB数据库'''client = MongoClient('10.20.4.79', 27017)#client = MongoClient('10.20.66.106', 27017)db_name = 'SoftADoutput'db = client[db_name]'''连接SqlServer数据库'''connStr = 'DRIVER={SQL Server Native Client 11.0};SERVER=DESKTOP-44P34L6;DATABASE=Softput;UID=sa;PWD=sa'conn = pyodbc.connect(connStr)cursor = conn.cursor()#########################################Channel_CovCode数据插入##########################'''从SQLServer数据库读取Channel_CovCode数据写入到mongodb数据库中Channel_CovCode集合中'''def InsertChannel_CovCode(cursor):  cursor.execute("select dm, ms from channel_CovCode")  rows = cursor.fetchall()  i = 1  for row in rows:#gb18030    db.channel_CovCode.insert({'_id':i,'dm':row.dm,'ms':row.ms.decode('gbk').encode('utf-8')})    i = i + 1InsertChannel_CovCode(cursor)######################################################################################################################################channel_ModeCode数据插入#############################'''从SQLServer数据库读取channel_ModeCode数据写入到mongodb数据库中channel_ModeCode集合中'''def InsertChannel_ModeCode(cursor):  cursor.execute("select dm, ms from channel_ModeCode")  rows = cursor.fetchall()  i = 1  for row in rows:#gb18030    db.channel_ModeCode.insert({'_id':i,'dm':row.dm,'ms':row.ms.decode('gbk').encode('utf-8')})    i = i + 1InsertChannel_ModeCode(cursor)######################################################################################################################################citynumb数据插入########################'''从SQLServer数据库读取citynumb数据写入到mongodb数据库中citynumb集合中'''def InsertCitynumb(cursor):  cursor.execute("select t.XZQMC,t.SMC,t.CSMC,t.SSQYDM,t.CITY_E,t.AREA_E,t.PROV_E from citynumb t")  rows = cursor.fetchall()  i = 1  for row in rows:    xzqmc = row.XZQMC    if xzqmc != None:      xzqmc = xzqmc.decode('gbk').encode('utf-8')    smc = row.SMC    if smc != None:      smc = smc.decode('gbk').encode('utf-8')    csmc = row.CSMC    if csmc != None:      csmc = csmc.decode('gbk').encode('utf-8')    db.citynumb.insert({'_id':i,'XZQMC':xzqmc,'SMC':smc,'CSMC':csmc,'SSQYDM':row.SSQYDM,'CITY_E':row.CITY_E,'AREA_E':row.AREA_E,'PROV_E':row.PROV_E})    i = i + 1InsertCitynumb(cursor)###########################################################################################################################################################channel数据插入############################'''从SQLServer数据库读取channel数据写入到mongodb数据库中channel集合中'''def InsertChannel(cursor):  cursor.execute("select pdcmc,pdemc,pdemcj,pdbm1,ssqydm,cov,sdate,mode,startTime,endTime,memo,pdtype,sflag,edate,corporation from channel")  rows = cursor.fetchall()  i = 1  for r in rows:    pdcmc = r.pdcmc    if pdcmc != None:      pdcmc = pdcmc.decode('gbk').encode('utf-8')    memo = r.memo    if memo != None:      memo = memo.decode('gbk').encode('utf-8')    corporation = r.corporation    if corporation != None:      corporation = corporation.decode('gbk').encode('utf-8')    db.channel.insert({'_id':i,'pdcmc':pdcmc,'pdemc':r.pdemc,'pdemcj':r.pdemcj,'pdbm1':r.pdbm1,'ssqydm':r.ssqydm,'cov':r.cov,'sdate':r.sdate,'mode':r.mode,'startTime':r.startTime,'endTime':r.endTime,'memo':memo,'pdtype':r.pdtype,'sflag':r.sflag,'edate':r.edate,'corporation':corporation})    i = i + 1InsertChannel(cursor)######################################################################################################################################CPBZK数据插入############################'''从SQLServer数据库读取CPBZK数据写入到mongodb数据库中CPBZK集合中'''def InsertCPBZK(cursor):  cursor.execute("select ZTC,EZTC,ZTC_CODE,LBDM,B_CODE,QY_CODE,IChange,cla from CPBZK")    rows = cursor.fetchall()    i = 1    for r in rows:#gb18030      ztc = r.ZTC      if ztc != None:        ztc = ztc.decode('gbk').encode('utf-8')      db.CPBZK.insert({'_id':i,'ZTC':ztc,'EZTC':r.EZTC,'ZTC_CODE':r.ZTC_CODE,'LBDM':r.LBDM,'B_CODE':r.B_CODE,'QY_CODE':r.QY_CODE,'IChange':r.IChange,'cla':r.cla})      i = i + 1InsertCPBZK(cursor)######################################################################################################################################TVPGMCLASS数据插入##########################'''从SQLServer数据库读取TVPGMCLASS数据写入到mongodb数据库中TVPGMCLASS集合中'''def InsertTVPGMCLASS(cursor):  cursor.execute("select ClassChDesc,ClassEnDesc,ClassCode,ParentCode,SortNo from TVPGMCLASS")  rows = cursor.fetchall()  i = 1  for r in rows:#gb18030    classChDesc = r.ClassChDesc    if classChDesc != None:      classChDesc = classChDesc.decode('gbk').encode('utf-8')    db.TVPGMCLASS.insert({'_id':i,'ClassChDesc':classChDesc,'ClassEnDesc':r.ClassEnDesc,'ClassCode':r.ClassCode,'ParentCode':r.ParentCode,'SortNo':r.SortNo})    i = i + 1InsertTVPGMCLASS(cursor)######################################################################################################################################GGBZK_DESCRIPTION数据插入###########################'''从SQLServer数据库读取GGBZK_DESCRIPTION数据写入到mongodb数据库中GGBZK_DESCRIPTION集合中'''def InsertGGBZK_DESCRIPTION(cursor):  cursor.execute("select V_code,des_named,des_main,des_background,des_scene,des_words,ModifyFlag,UpdateDate from  GGBZK_DESCRIPTION")  rows = cursor.fetchall()  i = 1  for r in rows:#gb18030    name = r.des_named    if name != None:      name = name.decode('gbk').encode('utf-8')    desmain = r.des_main    if desmain != None:      desmain = desmain.decode('gbk').encode('utf-8')    background = r.des_background    if background != None:      background = background.decode('gbk').encode('utf-8')    scene = r.des_scene    if scene != None:      scene = scene.decode('gbk').encode('utf-8')    words = r.des_words    if words != None:      words = words.decode('gbk').encode('utf-8')    db.GGBZK_DESCRIPTION.insert({'_id':i,'V_code':r.V_code,'des_named':name,'des_main':desmain,'des_background':background,'des_scene':scene,'des_words':words,'ModifyFlag':r.ModifyFlag,'UpdateDate':r.UpdateDate})    i = i + 1InsertGGBZK_DESCRIPTION(cursor)#########################################Z201607_027数据插入##########################'''从SQLServer数据库读取Z201607_027数据写入到mongodb数据库中Z201607_027集合中'''def InsertZ201607_027(cursor):  strSql = "select PD,RQ,SHIJIAN,ENDSHIJIAN,LBDM,ZTC_CODE,V_CODE,B_CODE,QY_CODE,QUANLITY,SPECIAL,LANGUAGE,LENGTH,SLENGTH,QJM1,QJM2,QGG,HJM1,HJM2,HGG,DUAN,OSHIJIAN,JG,SORTNO,LURU,ZFILE,COST,ROWTS,COST1,COST2,COST3 from Z201607_027"  cursor.execute(strSql)  rows = cursor.fetchall()  i = 1  for r in rows:#gb18030    cost = float(r.COST) #COST money类型    cost1 = float(r.COST1)    cost2 = float(r.COST2)    cost3 = float(r.COST3)    #先把时间戳转为字符串，然后再转为十进制数    rowts = int(str(binascii.b2a_hex(r.ROWTS)),16)    luru = r.LURU    if luru != None:      luru = luru.decode('gbk').encode('utf-8')    vCODE = r.V_CODE    if vCODE != None:      vCODE = vCODE.decode('gbk').encode('utf-8')    db.Z201607_027.insert({'_id':i,'PD':r.PD,'RQ':r.RQ,'SHIJIAN':r.SHIJIAN,'ENDSHIJIAN':r.ENDSHIJIAN,'LBDM':r.LBDM,'ZTC_CODE':r.ZTC_CODE,'V_CODE':vCODE,'B_CODE':r.B_CODE,'QY_CODE':r.QY_CODE,'QUANLITY':r.QUANLITY,'SPECIAL':r.SPECIAL,'LANGUAGE':r.LANGUAGE,'LENGTH':r.LENGTH,'SLENGTH':r.SLENGTH,'QJM1':r.QJM1,'QJM2':r.QJM2,'QGG':r.QGG,'HJM1':r.HJM1,'HJM2':r.HJM2,'HGG':r.HGG,'DUAN':r.DUAN,'OSHIJIAN':r.OSHIJIAN,'JG':r.JG,'SORTNO':r.SORTNO,'LURU':luru,'ZFILE':r.ZFILE,'COST':cost,'ROWTS':rowts,'ExpandProperty':'','COST1':cost1,'COST2':cost2,'COST3':cost3})    i = i + 1InsertZ201607_027(cursor)#############################################################################################