【Oracle】Python 連線Oracle 資料庫

楊奇龍發表於2011-11-21
OraclePython資料庫
Python 連線Oracle 資料庫，需要使用cx_Oracle 包。
1 下載cx_Oracle
該包的下載地址：
下載的時候，注意選擇與作業系統和oracle版本相對應的cx_Oracle版本。
2 安裝
[root@rac3 python]# rpm -ivh cx_Oracle-5.1.1-11g-py24-1.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:cx_Oracle              ########################################### [100%]

3 測試：   
oracle@rac3:/home/oracle/python>cat test.py 
import cx_Oracle 
username = "yang" 
userpwd = "yang" 
host = "127.0.0.1" 
port = 1523 
dbname = "yangdb" 

dsn=cx_Oracle.makedsn(host, port, dbname) 
connection=cx_Oracle.connect(username, userpwd, dsn) 
cursor = connection.cursor() 
sql = "select * from tab" 
cursor.execute(sql) 
result = cursor.fetchall() 
count = cursor.rowcount 
print "=====================" 
print "Total:", count 
print "=====================" 
for row in result: 
        print row 
cursor.close() 
connection.close()
測試結果：
oracle@rac3:/home/oracle/python>python test.py 
=====================
Total: 9
=====================
('BIG_TABLE', 'TABLE', None)
('BIN$sgD3dAkmWHfgQPoK8Qcq3Q==$0', 'TABLE', None)
('BIND', 'TABLE', None)
('IM_SMS_ADD_FRIEND', 'TABLE', None)
('PARALTAB', 'TABLE', None)
('T1', 'TABLE', None)
('T2', 'TABLE', None)
('T3', 'TABLE', None)
('T4', 'TABLE', None)

遇到的問題：
1 要在oracle 使用者才能執行import cx_Oracle
[root@rac3 ~]# python
Python 2.4.3 (#1, Jan 21 2009, 01:11:33) 
[GCC 4.1.2 20071124 (Red Hat 4.1.2-42)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
Traceback (most recent call last):
  File "", line 1, in ?
ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory
>>>

如果在oracle 也遇到 ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory
要檢視.bash_profile 檔案：和環境變數 LD_LIBRARY_PATH有關，一定要設定為：
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib;export LD_LIBRARY_PATH
2 ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
oracle@rac3:/home/oracle/python>python test.py 
Traceback (most recent call last):
  File "test.py", line 10, in ?
    connection=cx_Oracle.connect(username, userpwd, dsn) 
cx_Oracle.DatabaseError: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/oracle/11.2.0/alifpre/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

yangdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.250.7.241 )(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = yangdb)
    )
  )
listener.ora 檔案中
SID_LIST_LISTENER =
      (SID_LIST =
         (SID_DESC =
           (SID_NAME = PLSExtProc)
             (ORACLE_HOME = /opt/oracle/11.2.0/alifpre)
           (PROGRAM = extproc)
         )
        (SID_DESC =
          (GLOBAL_DBNAME = yang)
           (ORACLE_HOME = /opt/oracle/11.2.0/alifpre)
          (SID_NAME =yangdb)
         )
     )

  (GLOBAL_DBNAME = yang)與tnsnams.ora 檔案中的tns連線名不一致導致，修改為yangdb，重新啟動監聽或者lsnrclt reload 即可

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-711728/，如需轉載，請註明出處，否則將追究法律責任。

相關文章