【Oracle】Python 连接Oracle 数据库

From: http://space.itpub.net/22664653/viewspace-711728

Python 连接Oracle 数据库，需要使用cx_Oracle 包。

1 下载cx_Oracle

该包的下载地址：http://cx-oracle.sourceforge.net/

下载的时候，注意选择与操作系统和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 "<stdin>", 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 即可