本文实例讲述了Python实现读取SQLServer数据并插入到MongoDB数据库的方法。分享给大家供大家参考，具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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 pyodbcimport osimport csvimport pymongofrom pymongo import ASCENDING, DESCENDINGfrom pymongo import MongoClientimport 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 + 1InsertChannel_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 + 1InsertChannel_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 + 1InsertCitynumb(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 + 1InsertChannel(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 + 1InsertCPBZK(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 + 1InsertTVPGMCLASS(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 + 1InsertGGBZK_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 + 1InsertZ201607_027(cursor)############################################################################################# |
希望本文所述对大家Python程序设计有所帮助。
原文链接：http://www.cnblogs.com/shaosks/p/5729166.html
