Python實現(xiàn)讀取SQLServer數(shù)據(jù)并插入到MongoDB數(shù)據(jù)庫的方法示例
更新時間：2018年06月09日 12:56:36 作者：shaomine
這篇文章主要介紹了Python實現(xiàn)讀取SQLServer數(shù)據(jù)并插入到MongoDB數(shù)據(jù)庫的方法,涉及Python同時進行SQLServer與MongoDB數(shù)據(jù)庫的連接、查詢、讀取、寫入等相關操作實現(xiàn)技巧,需要的朋友可以參考下
本文實例講述了Python實現(xiàn)讀取SQLServer數(shù)據(jù)并插入到MongoDB數(shù)據(jù)庫的方法。分享給大家供大家參考，具體如下：
# -*- coding: utf-8 -*-
import pyodbc
import os
import csv
import pymongo
from pymongo import ASCENDING, DESCENDING
from pymongo import MongoClient
import binascii
'''連接mongoDB數(shù)據(jù)庫'''
client = MongoClient('10.20.4.79', 27017)
#client = MongoClient('10.20.66.106', 27017)
db_name = 'SoftADoutput'
db = client[db_name]
'''連接SqlServer數(shù)據(jù)庫'''
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數(shù)據(jù)插入##########################
'''從SQLServer數(shù)據(jù)庫讀取Channel_CovCode數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入#############################
'''從SQLServer數(shù)據(jù)庫讀取channel_ModeCode數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入########################
'''從SQLServer數(shù)據(jù)庫讀取citynumb數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入############################
'''從SQLServer數(shù)據(jù)庫讀取channel數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入############################
'''從SQLServer數(shù)據(jù)庫讀取CPBZK數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入##########################
'''從SQLServer數(shù)據(jù)庫讀取TVPGMCLASS數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入###########################
'''從SQLServer數(shù)據(jù)庫讀取GGBZK_DESCRIPTION數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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數(shù)據(jù)插入##########################
'''從SQLServer數(shù)據(jù)庫讀取Z201607_027數(shù)據(jù)寫入到mongodb數(shù)據(jù)庫中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)
#先把時間戳轉為字符串，然后再轉為十進制數(shù)
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相關內容感興趣的讀者可查看本站專題：《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python編碼操作技巧總結》、《Python圖片操作技巧總結》、《Python數(shù)據(jù)結構與算法教程》、《Python Socket編程技巧總結》、《Python函數(shù)使用技巧總結》、《Python字符串操作技巧匯總》、《Python入門與進階經典教程》及《Python文件與目錄操作技巧匯總》
希望本文所述對大家Python程序設計有所幫助。
相關文章
單例模式（Singleton Pattern）是一種常用的軟件設計模式，該模式的主要目的是確保 某一個類只有一個實例存在.這篇文章主要介紹了單利模式及python實現(xiàn)方式及Python單例模式的4種實現(xiàn)方法,需要的朋友可以參考下2018-03-03
這篇文章主要介紹了python3 requests 各種發(fā)送方式,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-04-04
今天小編就為大家分享一篇使用pandas對兩個dataframe進行join的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-06-06
這篇文章主要為大家介紹了Python中的五個神仙級函數(shù)，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-01-01
這篇文章主要介紹了解決python運行啟動報錯問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-06-06
python實現(xiàn)圖片，視頻人臉識別(dlib版)
這篇文章主要介紹了python實現(xiàn)圖像，視頻人臉識別(dlib版)的相關資料，幫助大家更好的理解和學習python，感興趣的朋友可以了解下2020-11-11
python3+selenium實現(xiàn)qq郵箱登陸并發(fā)送郵件功能
這篇文章主要為大家詳細介紹了python3+selenium實現(xiàn)qq郵箱登陸，并發(fā)送郵件功能，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-01-01
下面小編就為大家?guī)硪黄鉀QPyCharm中光標變粗的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-08-08
本文內容是樹莓派中python獲取GY-85九軸模塊信息的示例，這里使用Python的curses包開發(fā)cli窗口程序,用來實時刷新傳感器的讀數(shù)，下面看代碼2013-12-12
這篇文章主要為大家介紹了Python采集某度貼吧排行榜實戰(zhàn)示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-04-04