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

本文实例讲述了python实现读取sqlserver数据并插入到mongodb数据库的方法。分享给大家供大家参考，具体如下：

# -*- 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相关内容感兴趣的读者可查看本站专题：《python常见数据库操作技巧汇总》、《python编码操作技巧总结》、《python图片操作技巧总结》、《python数据结构与算法教程》、《python socket编程技巧总结》、《python函数使用技巧总结》、《python字符串操作技巧汇总》、《python入门与进阶经典教程》及《python文件与目录操作技巧汇总》

希望本文所述对大家python程序设计有所帮助。