python中怎么利用cx_Oracle连接oracle数据库

本篇文章为大家展示了python中怎么利用cx_Oracle连接oracle数据库，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

直接使用：
    CheckOracle.py -H [remote_home] -u [oracle_user] -p [oracle_password] -s [oracle_SID] -i [information] [table_name]
   具体使用细节和例子，使用-h获取帮助         
其他python调用：
    1 可以在实例化对象时指定参数
    2 调用的函数为OraInfo()
    3 返回结果集

1. #!/usr/bin/env python3
   

2. #_*_coding:utf-8_*_
   

3. #Auth by raysuen
   

4. #v1
   

5. 
   

6. import cx_Oracle
   

7. import sys,re,datetime
   

8. 
   

9. 
   

10. class OracleConn(object): #封装类
    

11.     #存放连接信息的
    

12.     Info = {
    

13.         "host":"127.0.0.1", #远端oracle的host
    

14.         "port":1521, #远端oracle的port
    

15.         "OraSID":None, #远端oracle的SID
    

16.         "OraUser":None, #远端oracle的username
    

17.         "OraPasswd":None, #远端oracle的username对应的密码
    

18.         # "OraSchemas":None,
    

19.         "OraInfo":None, #想要获取远端数据库的那种信息
    

20.         "OraTable":None      #有关系的oracle的表名
    

21.             }
    

22. 
    

23.     def __init__(self,host=None,port=None,sid=None,orauser=None,orapwd=None,orainfo=None,oratable=None):
    

24.         if host != None:
    

25.             self.Info["host"] = host
    

26.         if sid != None:
    

27.             self.Info["OraSID"] = sid
    

28.         if port != None:
    

29.             self.Info["port"] = port
    

30.         if orauser != None:
    

31.             self.Info["OraUser"] = orauser
    

32.         if orapwd != None:
    

33.             self.Info["OraPasswd"] = orapwd
    

34.         if orainfo != None:
    

35.             self.Info["OraInfo"] = orainfo
    

36.         if oratable != None:
    

37.             self.Info["OraTable"] = oratable
    

38. 
    

39. 
    

40.     def Check_Info(self): #判断Info字典里面的key是否存在必要的值
    

41.         if self.Info["OraUser"] == None:
    

42.             print("You must specify a oracle username for connecting oracle.")
    

43.             print("If you don't know how to specify the parameters.You can -h to get help")
    

44.             exit(3)
    

45.         if self.Info["OraPasswd"] == None:
    

46.             print("You must specify a oracle password for connecting oracle.")
    

47.             print("If you don't know how to specify the parameters.You can -h to get help")
    

48.             exit(3)
    

49.         if self.Info["OraSID"] == None:
    

50.             print("You must specify a oracle SID for connecting oracle.")
    

51.             print("If you don't know how to specify the parameters.You can -h to get help")
    

52.             exit(3)
    

53.         if self.Info["OraInfo"] == None:
    

54.             print("You must specify a Information about oracle")
    

55.             print("If you don't know how to specify the parameters.You can -h to get help")
    

56.             exit(3)
    

57. 
    

58.     def ConnectOracle(self): #封装连接数据库的连接，并返回连接对象
    

59.         try:
    

60.             tnsname = cx_Oracle.makedsn(self.Info["host"], self.Info["port"], self.Info["OraSID"])
    

61.             ora = cx_Oracle.connect(self.Info["OraUser"], self.Info["OraPasswd"], tnsname)
    

62.         except Exception as e:
    

63.             print(e)
    

64.             exit(4)
    

65.         return ora
    

66. 
    

67.     def CloseOracle(self,oraCon): #封装管理数据库连接的函数
    

68.         oraCon.close()
    

69. 
    

70.     def ExecSql(self,SqlStr): #封装数据执行sql的函数
    

71.         try:
    

72.             ora = self.ConnectOracle()
    

73.             cursor = ora.cursor()
    

74.             cursor.execute(SqlStr)
    

75.             res = cursor.fetchall()
    

76.             cursor.close
    

77.             self.CloseOracle(ora)
    

78.         except Exception as e:
    

79.             print(e)
    

80.             exit(5)
    

81.         return res
    

82. 
    

83. 
    

84.     def GetTableSpace(self): #获取tablespace信息的函数
    

85.         sqlStr="""
    

86.               select  a.tablespace_name,
    

87.                      round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
    

88.                      round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
    

89.                      round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
    

90.                      round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_Free,
    

91.                      100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_used,
    

92.                      round(maxbytes/1048576,2) Max,
    

93.                      round(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) / round((case maxbytes when 0 then a.bytes_alloc else maxbytes end)/1048576,2) * 100,2) || '%' "USED_MAX%"
    

94.               from  ( select  f.tablespace_name,
    

95.                              sum(f.bytes) bytes_alloc,
    

96.                              sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
    

97.                       from dba_data_files f
    

98.                       group by tablespace_name) a,
    

99.                     ( select  f.tablespace_name,
    

100.                              sum(f.bytes)  bytes_free
     

101.                       from dba_free_space f
     

102.                       group by tablespace_name) b
     

103.               where a.tablespace_name = b.tablespace_name (+)
     

104.               union all
     

105.               select h.tablespace_name,
     

106.                      round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
     

107.                      round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
     

108.                      round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
     

109.                      round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' Pct_Free,
     

110.                      100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' pct_used,
     

111.                      round(sum(f.maxbytes) / 1048576, 2) max,
     

112.                      round(round(sum(nvl(p.bytes_used, 0))/ 1048576, 2)/round(sum(case f.maxbytes when 0 then (h.bytes_free + h.bytes_used) else f.maxbytes end) / 1048576, 2) * 100,2)||'%' "USED_MAX%"
     

113.               from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
     

114.               where  p.file_id(+) = h.file_id
     

115.               and    p.tablespace_name(+) = h.tablespace_name
     

116.               and    f.file_id = h.file_id
     

117.               and    f.tablespace_name = h.tablespace_name
     

118.               group by h.tablespace_name
     

119.               ORDER BY 1
     

120.             """
     

121. 
     

122.         res = self.ExecSql(sqlStr)
     

123.         return res
     

124. 
     

125.     def PrintTablespace(self,res):
     

126.         headStr = """|%s|%s|%s|%s|%s|%s|%s|%s|"""%("tablespace_name".center(30),"megs_alloc".center(15),"megs_free".center(15),"megs_used".center(15),"Pct_Free".center(15),"Pct_used".center(15),"Max_MB".center(15),"USED_MAX_PCT".center(15))
     

127.         print("%s"%"".center(144,"-"))
     

128.         print(headStr)
     

129.         print("%s" % "".center(144, "-"))
     

130.         for t in res:
     

131.             print("|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(30),str(t[1]).center(15),str(t[2]).center(15),str(t[3]).center(15),t[4].center(15),t[5].center(15),str(t[6]).center(15),t[7].center(15)))
     

132.         print("%s" % "".center(144, "-"))
     

133. 
     

134.     def GetAsmDiskGroup(self):
     

135.         sqlStr="""select name,total_mb,free_mb from v$asm_diskgroup"""
     

136.         res = self.ExecSql(sqlStr)
     

137.         return res
     

138. 
     

139.     def PrintAsmDiskGroup(self,res):
     

140.         print("%s" % "".center(50, "-"))
     

141.         print("|%s|%s|%s|"%("GROUP_NAME".center(20),"TOTAL_MB".center(15),"FREE_MB".center(15)))
     

142.         print("%s" % "".center(50, "-"))
     

143.         for t in res:
     

144.             print("|%s|%s|%s|"%(t[0].center(20),str(t[1]).center(15),str(t[2]).center(15)))
     

145.         print("%s" % "".center(50, "-"))
     

146. 
     

147.     def GetRedo(self):
     

148.         sqlStr="""
     

149.             select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#
     

150.             """
     

151.         res = self.ExecSql(sqlStr)
     

152.         return res
     

153. 
     

154.     def PrintRedo(self,res):
     

155.         print("%s" % "".center(148, "-"))
     

156.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("GROUP".center(5),"SIZE_MB".center(7),"MEMBER".center(50),"THREAD".center(6),"SEQUENCE".center(8),"MEMBERS".center(7),"ARCHIVED".center(8),"STATUS".center(8),"FIRST_TIME".center(19),"NEXT_TIME".center(19)))
     

157.         print("%s" % "".center(148, "-"))
     

158.         for t in res:
     

159.             if t[9] == None:
     

160.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(5),str(t[1]).center(7),t[2].center(50),str(t[3]).center(6),str(t[4]).center(8),str(t[5]).center(7),t[6].center(8),t[7].center(8),datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S"),"None".center(19))
     

161.             else:
     

162.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|" % (
     

163.                 str(t[0]).center(5), str(t[1]).center(7), t[2].center(50), str(t[3]).center(6), str(t[4]).center(8),
     

164.                 str(t[5]).center(7), t[6].center(8), t[7].center(8),
     

165.                 datetime.datetime.strftime(t[8], "%Y-%m-%d %H:%M:%S"),
     

166.                 datetime.datetime.strftime(t[9], "%Y-%m-%d %H:%M:%S"))
     

167.             print(tStr)
     

168.         print("%s" % "".center(148, "-"))
     

169. 
     

170.     def GetRedoShift(self):
     

171.         sqlStr = """
     

172.                   SELECT
     

173.                   to_char(first_time,'YYYY-MM-DD') day,
     

174.                   to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
     

175.                   to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
     

176.                   to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
     

177.                   to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
     

178.                   to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
     

179.                   to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
     

180.                   to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
     

181.                   to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
     

182.                   to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
     

183.                   to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
     

184.                   to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
     

185.                   to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
     

186.                   to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
     

187.                   to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
     

188.                   to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
     

189.                   to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
     

190.                   to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
     

191.                   to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
     

192.                   to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
     

193.                   to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
     

194.                   to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
     

195.                   to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
     

196.                   to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
     

197.                   to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
     

198.                   from
     

199.                   v$log_history
     

200.                   GROUP by
     

201.                   to_char(first_time,'YYYY-MM-DD') order by day desc
     

202.                  """
     

203.         res = self.ExecSql(sqlStr)
     

204.         return res
     

205. 
     

206.     def PrintRedoShift(self,res):
     

207.         print("%s" % "".center(132, "-"))
     

208.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("DAY".center(10),"00".center(4),"01".center(4),"02".center(4),"03".center(4),"04".center(4),"05".center(4),"06".center(4),"07".center(4),"08".center(4),"09".center(4),"10".center(4),"11".center(4),"12".center(4),"13".center(4),"14".center(4),"15".center(4),"16".center(4),"17".center(4),"18".center(4),"19".center(4),"20".center(4),"21".center(4),"22".center(4),"23".center(4)))
     

209.         print("%s" % "".center(132, "-"))
     

210.         for t in res:
     

211.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(10),t[1].center(4),t[2].center(4),t[3].center(4),t[4].center(4),t[5].center(4),t[6].center(4),t[7].center(4),t[8].center(4),t[9].center(4),t[10].center(4),t[11].center(4),t[12].center(4),t[12].center(4),t[13].center(4),t[14].center(4),t[15].center(4),t[16].center(4),t[17].center(4),t[18].center(4),t[19].center(4),t[20].center(4),t[21].center(4),t[22].center(4),t[23].center(4)))
     

212.         print("%s" % "".center(132, "-"))
     

213. 
     

214. 
     

215.     def GetExecNow(self):
     

216.         sqlStr = """
     

217.                 select distinct b.SID,b.SERIAL#,p.SPID,b.LAST_CALL_ET,a.sql_id, a.sql_text,b.status,b.event,b.MODULE, b.OSUSER,b.MACHINE from v$sql a,v$session b,v$process p where a.SQL_ID=b.SQL_ID and b.PADDR=p.ADDR and b.STATUS='ACTIVE' order by B.LAST_CALL_ET desc
     

218.                 """
     

219.         res = self.ExecSql(sqlStr)
     

220.         return res
     

221. 
     

222.     def PrintExecNow(self,res):
     

223.         print("%s" % "".center(188, "-"))
     

224.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("SID".center(6),"SERIAL".center(6),"SPID".center(5),"LAST_ET".center(7),"SQL_ID".center(15),"SQL_TEXT".center(60),"STATUS".center(7),"EVENT".center(30),"MODULE".center(15),"OSUSER".center(10),"MACHINE".center(15)))
     

225.         print("%s" % "".center(188, "-"))
     

226.         for t in res:
     

227.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(6),str(t[1]).center(6),t[2].center(5),str(t[3]).center(7),t[4].center(15),t[5].strip()[0:60].center(60),t[6].center(7),t[7][0:30].center(30),t[8][0:15].center(15),t[9].center(10),t[10][0:15].center(15)))
     

228.         print("%s" % "".center(188, "-"))
     

229. 
     

230. 
     

231.     def GetIndexInfo(self):
     

232.         if self.Info["OraTable"] == None:
     

233.             print("You must specify a oracle table to get indexs information of table.")
     

234.             print("If you don't know how to specify the parameters.You can -h to get help")
     

235.             exit(6)
     

236.         sqlStr = """
     

237.                 select
     

238.                     table_name,
     

239.                     TABLE_TYPE,
     

240.                     INDEX_NAME,
     

241.                     INDEX_TYPE,
     

242.                     TABLE_OWNER,
     

243.                     max(columns) columns
     

244.                 from
     

245.                 (SELECT
     

246.                     ui.table_name,
     

247.                     ui.TABLE_TYPE,
     

248.                     ui.INDEX_NAME,
     

249.                     ui.INDEX_TYPE,
     

250.                     uic.TABLE_OWNER,
     

251.                     to_char(wm_concat (uic.COLUMN_NAME)
     

252.                         over(partition by ui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNER order by uic.COLUMN_POSITION)) columns
     

253.                 FROM
     

254.                     dba_indexes ui,
     

255.                     dba_IND_COLUMNS uic
     

256.                 WHERE
     

257.                     ui.INDEX_NAME (+) = uic.INDEX_NAME
     

258.                 AND ui.TABLE_NAME = UPPER ('%s'))
     

259.                 GROUP BY
     

260.                     table_name,
     

261.                     TABLE_TYPE,
     

262.                     INDEX_NAME,
     

263.                     INDEX_TYPE,
     

264.                     TABLE_OWNER
     

265.                 """%self.Info["OraTable"]
     

266.         res = self.ExecSql(sqlStr)
     

267.         return res
     

268. 
     

269. 
     

270.     def PrintIndexInfo(self,res):
     

271.         print("%s" % "".center(142, "-"))
     

272.         print("|%s|%s|%s|%s|%s|%s|"%("TABLE_NAME".center(20),"TABLE_TYPE".center(20),"INDEX_NAME".center(25),"INDEX_TYPE".center(15),"TABLE_OWNER".center(15),"COLUMNS".center(40)))
     

273.         print("%s" % "".center(142, "-"))
     

274.         for t in res:
     

275.             print("|%s|%s|%s|%s|%s|%s|" % (t[0].center(20), t[1].center(20), t[2].center(25), t[3].center(15), t[4].center(15), t[5].center(40)))
     

276. 
     

277.         print("%s" % "".center(142, "-"))
     

278. 
     

279. 
     

280.     def GetPartInfo(self):
     

281.         if self.Info["OraTable"] == None:
     

282.             print("You must specify a oracle table to get indexs information of table.")
     

283.             print("If you don't know how to specify the parameters.You can -h to get help")
     

284.             exit(6)
     

285.         sqlStr = """
     

286.                 SELECT
     

287.                     a.TABLE_OWNER,
     

288.                     a.table_name,
     

289.                     c. M,
     

290.                     a.PARTITION_NAME,
     

291.                     a.HIGH_VALUE,
     

292.                     a.NUM_ROWS,
     

293.                     a.TABLESPACE_NAME,
     

294.                     b.COLUMN_NAME,
     

295.                     A .LAST_ANALYZED
     

296.                 FROM
     

297.                     dba_TAB_PARTITIONS A,
     

298.                     DBA_PART_KEY_COLUMNS b,
     

299.                     (
     

300.                         SELECT
     

301.                             SUM (bytes / 1024 / 1024) M,
     

302.                             segment_name,
     

303.                             partition_name
     

304.                         FROM
     

305.                             dba_segments
     

306.                         WHERE segment_type LIKE '%%TABLE%%'
     

307.                         AND partition_name IS NOT NULL
     

308.                         and segment_name = upper('%s')
     

309.                         GROUP BY
     

310.                             segment_name,
     

311.                             partition_name
     

312.                         ORDER BY
     

313.                             segment_name,
     

314.                             partition_name DESC
     

315.                     ) c
     

316.                 WHERE
     

317.                     A .TABLE_NAME = b. NAME (+)
     

318.                 AND A .table_name = c.SEGMENT_NAME
     

319.                 AND A .partition_name = c.PARTITION_NAME
     

320.                 AND A .table_name = upper('%s')
     

321.                 ORDER BY
     

322.                     A .TABLE_NAME,
     

323.                     partition_name DESC
     

324.                 """%(self.Info["OraTable"],self.Info["OraTable"])
     

325.         res = self.ExecSql(sqlStr)
     

326.         return res
     

327. 
     

328.     def PrintPartInfo(self,res):
     

329.         print("%s" % "".center(184, "-"))
     

330.         print("|%s|" % "TABLE_OWNER".center(15), end='')
     

331.         print("%s|" % "TABLE_NAME".center(25), end='')
     

332.         print("%s|" % "MB".center(5), end='')
     

333.         print("%s|" % "PARTITION_NAME".center(20), end='')
     

334.         print("%s|" % "HIGH_VALUE".center(40), end='')
     

335.         print("%s|" % "NUM_ROWS".center(10), end='')
     

336.         print("%s|" % "TABLESPACE".center(20), end='')
     

337.         print("%s|" % "CULUMN_NAME".center(20), end='')
     

338.         print("%s|" % "LAST_ANALYZED".center(19))
     

339.         print("%s" % "".center(184, "-"))
     

340. 
     

341.         for t in res:
     

342.             print("|%s|"%t[0].center(15),end='')
     

343.             print("%s|"%t[1].center(25), end='')
     

344.             print("%s|"%str(t[2]).center(5), end='')
     

345.             print("%s|"%t[3].center(20), end='')
     

346.             print("%s|"%t[4][0:40].center(40), end='')
     

347.             print("%s|"%str(t[5]).center(10), end='')
     

348.             print("%s|"%t[6].center(20), end='')
     

349.             if t[7] == None:
     

350.                 print("%s|"%"None".center(20))
     

351.             else:
     

352.                 print("%s|"%t[7].center(20), end='')
     

353.             print("%s|"%(datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S")))
     

354.         print("%s" % "".center(184, "-"))
     

355. 
     

356.     def OraInfo(self): #这个函数不格式化打印返回结果，只返回结果集
     

357.         self.Check_Info()
     

358.         if self.Info["OraInfo"].upper() == "TABLESPACE":
     

359.             self.GetTableSpace()
     

360.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
     

361.             self.GetAsmDiskGroup()
     

362.         elif self.Info["OraInfo"].upper() == "REDO":
     

363.             self.GetRedo()
     

364.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
     

365.             self.GetRedoShift()
     

366.         elif self.Info["OraInfo"].upper() == "EXECNOW":
     

367.             self.GetExecNow()
     

368.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
     

369.             self.GetIndexInfo()
     

370.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
     

371.             self.GetPartInfo()
     

372.         else:
     

373.             print("Please enter valid value for -i")
     

374.             exit(6)
     

375. 
     

376. 
     

377.     def PrintOraInfo(self):
     

378.         self.Check_Info()
     

379.         if self.Info["OraInfo"].upper() == "TABLESPACE":
     

380.             self.PrintTablespace(self.GetTableSpace())
     

381.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
     

382.             self.PrintAsmDiskGroup(self.GetAsmDiskGroup())
     

383.         elif self.Info["OraInfo"].upper() == "REDO":
     

384.             self.PrintRedo(self.GetRedo())
     

385.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
     

386.             self.PrintRedoShift(self.GetRedoShift())
     

387.         elif self.Info["OraInfo"].upper() == "EXECNOW":
     

388.             self.PrintExecNow(self.GetExecNow())
     

389.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
     

390.             self.PrintIndexInfo(self.GetIndexInfo())
     

391.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
     

392.             self.PrintPartInfo(self.GetPartInfo())
     

393.         else:
     

394.             print("Please enter valid value for -i")
     

395.             exit(6)
     

396. 
     

397. def fun_help():
     

398.     helpStr = """
     

399.     Name
     

400.         CheckOracle.py      display information which you want to know
     

401.     
     

402.     Synopsis
     

403.         CheckOracle.py -H [romote_host] -u [oracle user] -p [oracle password] -s [oracle SID] -i [information] [table_name]
     

404.     
     

405.     Description
     

406.         -H Specify a remote host,defaul 127.0.0.1.
     

407.         -u Specify a oracle user
     

408.         -p Specify a oracle password
     

409.         -s Specify a oracle SID
     

410.         -i Specify a action information what you want to know
     

411.             Value:
     

412.                 tablespace
     

413.                 asmdiskgroup
     

414.                 redo
     

415.                 redoshift
     

416.                 execnow
     

417.                 indexinfo
     

418.                     Table_name must be specified after indexinfo.
     

419.                 partitioninfo
     

420.                     Table_name must be specified after partitioninfo.
     

421.     
     

422.     Example
     

423.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i execnow
     

424.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i indexinfo dept
     

425.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i partitioninfo dept
     

426.     """
     

427.     print(helpStr)
     

428. 
     

429. if __name__=="__main__":
     

430.     # print(sys.argv)
     

431.     # print(len(sys.argv))
     

432. 
     

433.     if len(sys.argv) > 1: #判断是否传递了参数
     

434.         for i in range(1,len(sys.argv)): #循环参数下标
     

435.             # print(sys.argv[i])
     

436.             if sys.argv[i] == "-H": #判断参数
     

437.                 if re.match("^-",sys.argv[i+1]) == None: #判断当先的下一个参数是否为-开头，-开头不是正取的value值
     

438.                     i += 1   #下标加1
     

439.                     OracleConn.Info["host"] = sys.argv[i] #获取参数值
     

440.                 else:
     

441.                     print("-H.You use this parameter,but no value are specified") #当前参数的下一个参数，不是正取的value值，报错退出
     

442.                     exit(2)
     

443.             elif sys.argv[i] == "-u":
     

444.                 if re.match("^-", sys.argv[i+1]) == None:
     

445.                     i += 1
     

446.                     OracleConn.Info["OraUser"] = sys.argv[i]
     

447.                 else:
     

448.                     print("-u.You use this parameter,but no value are specified")
     

449.                     exit(2)
     

450.             elif sys.argv[i] == "-p":
     

451.                 if re.match("^-", sys.argv[i+1]) == None:
     

452.                     i += 1
     

453.                     OracleConn.Info["OraPasswd"] = sys.argv[i]
     

454.                 else:
     

455.                     print("-p.You use this parameter,but no value are specified")
     

456.                     exit(2)
     

457.             elif sys.argv[i] == "-P":
     

458.                 if re.match("^-", sys.argv[i+1]) == None:
     

459.                     i += 1
     

460.                     OracleConn.Info["port"] = sys.argv[i]
     

461.                 else:
     

462.                     print("-P.You use this parameter,but no value are specified")
     

463.                     exit(2)
     

464.             elif sys.argv[i] == "-S":
     

465.                 if re.match("^-", sys.argv[i+1]) == None:
     

466.                     i += 1
     

467.                     OracleConn.Info["OraSchemas"] = sys.argv[i].upper()
     

468.                 else:
     

469.                     print("-S.You use this parameter,but no value are specified")
     

470.                     exit(2)
     

471.             elif sys.argv[i] == "-s":
     

472.                 if re.match("^-", sys.argv[i+1]) == None:
     

473.                     i += 1
     

474.                     OracleConn.Info["OraSID"] = sys.argv[i]
     

475.                 else:
     

476.                     print("-S.You use this parameter,but no value are specified")
     

477.                     exit(2)
     

478.             elif sys.argv[i] == "-i":
     

479.                 if re.match("^-", sys.argv[i+1]) == None:
     

480.                     i += 1
     

481.                     OracleConn.Info["OraInfo"] = sys.argv[i]
     

482.                     if i+1 < len(sys.argv) and re.match("^-", sys.argv[i+1]) == None:
     

483.                         i += 1
     

484.                         OracleConn.Info["OraTable"] = sys.argv[i]
     

485.                 else:
     

486.                     print("-s.You use this parameter,but no value are specified")
     

487.                     exit(2)
     

488.             elif sys.argv[i] == "-h":
     

489.                 fun_help()
     

490.                 exit(0)
     

491.     else:
     

492.         print("Please enter right parameters")
     

493.         exit(1)
     

494.     oc = OracleConn() #实例化类对象
     

495.     oc.PrintOraInfo() #调用类的接口函数,打印结果

上述内容就是python中怎么利用cx_Oracle连接oracle数据库，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。