Python reads SQL Server data into the MongoDB database

#-*-Coding:utf-8-*-
Import Pyodbc
Import OS
Import CSV
Import Pymongo
From Pymongo import Ascending, descending
From Pymongo import mongoclient
Import Binascii

"Connect to MongoDB database"
Client = mongoclient (' 10.20.4.79 ', 27017)
#client = mongoclient (' 10.20.66.106 ', 27017)
db_name = ' Softadoutput '
db = Client[db_name]

' connection to SQL Server database ' '
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 data is inserted ##########################
"reads Channel_covcode data from the SQL Server database and writes to the MongoDB database 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 + 1

Insertchannel_covcode (cursor)
#################### #########################################################################

######################################## #channel_ModeCode数据插入 #############################
"" Reads Channel_modecode data from the SQL Server database to the MongoDB database in the Channel_modecode collection '
def insertchannel_modecode (cursor):
Cursor.execute ("Select DM, Ms from Channel_modecode")
rows = Cursor.fetchall ()
i = 1
for row in R OWS: #gb18030
Db.channel_ModeCode.insert ({' _id ': I, ' DM ': row.dm, ' Ms ': Row.ms.decode (' GBK '). Encode (' Utf-8 ')})
i = i + 1

Insertchannel_modecode (cursor)
########################################################### ##################################

######################################## #citynumb数据插入 ########################
"' reads citynumb data from the SQL Server database into the Citynumb collection in the MongoDB database"
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 + 1

Insertcitynumb (cursor)
############################################################################################################### ###

######################################## #channel数据插入 ############################
"' reads channel data from SQL Server database to the channel set in the MongoDB database"
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 ':p DCMC, ' 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 + 1

Insertchannel (cursor)
#############################################################################################

######################################## #CPBZK数据插入 ############################
"" Reads CPBZK data from the SQL Server database into the Cpbzk collection in the MongoDB database "
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 + 1

Insertcpbzk (cursor)
########################### ##################################################################

######################################## #TVPGMCLASS数据插入 ##########################
"' reads Tvpgmclass data from the SQL Server database into the Tvpgmclass collection in the MongoDB database"
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 + 1

Inserttvpgmclass (cursor)
#############################################################################################

######################################## #GGBZK_DESCRIPTION数据插入 ###########################
"' reads ggbzk_description data from the SQL Server database into the Ggbzk_description collection in the MongoDB database"
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 ':d esmain, ' des_background ': Background

' Des_scene ': Scene, ' des_words ': words, ' modifyflag ': R.modifyflag, ' updatedate ': r.updatedate})
i = i + 1

Insertggbzk_description (cursor)

######################################## #Z201607_027数据插入 ##########################
"' reads z201607_027 data from the SQL Server database into the z201607_027 collection in the MongoDB database"
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 type of money
Cost1 = float (r.cost1)
Cost2 = float (r.cost2)
Cost3 = float (r.cost3)
#先把时间戳转为字符串, and then decimal number
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 + 1

insertz201607_027 (cursor)
#############################################################################################

Python reads SQL Server data into the MongoDB database