欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-13 14:27:33
本文实例讲述了python实现读取sqlserver数据并插入到mongodb数据库的方法。分享给大家供大家参考,具体如下: # -*- coding: utf-...

本文实例讲述了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程序设计有所帮助。