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

本文实例讲述了Python实现读取SQLServer数据并插入到MongoDB数据库的方法。分享给大家供大家参考，具体如下：
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
									# -*- coding: utf-8 -*-

									import pyodbc

									import os

									import csv

									import pymongo

									from pymongo import ASCENDING, DESCENDING

									from pymongo import MongoClient

									import 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 + 1

									InsertChannel_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 + 1

									InsertChannel_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 + 1

									InsertCitynumb(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 + 1

									InsertChannel(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 + 1

									InsertCPBZK(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 + 1

									InsertTVPGMCLASS(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 + 1

									InsertGGBZK_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 + 1

									InsertZ201607_027(cursor)

									#############################################################################################
希望本文所述对大家Python程序设计有所帮助。
原文链接：http://www.cnblogs.com/shaosks/p/5729166.html
Python实现读取SQLServer数据并插入到MongoDB数据库的方法示例

相关文章

热门资讯
