【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 "", 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 即可