将Oracle服务名称与SQLAlchemy结合使用 [英] Using Oracle Service Names with SQLAlchemy

问题描述

我遇到了一个令人讨厌的小问题，即使用服务名称通过SQLAlchemy连接到Oracle模式.这是我的脚本代码. (出于安全原因，尖括号之间的项目是实值的占位符)

I've run into a nasty little problem connecting to an Oracle schema via SQLAlchemy using a service name. Here is my code as a script. (items between angle brackets are place holders for real values for security reasons)

from sqlalchemy import create_engine

if __name__ == "__main__":                                                                                                                                                        
    engine = create_engine("oracle+cx_oracle://<username>:<password>@<host>/devdb")                                                                                                                                                   
    result = engine.execute("create table test_table (id NUMBER(6), name VARCHAR2(15) not NULL)")
    result = engine.execute("drop table test_table")

其中"devdb"是服务名称而不是SID.运行此脚本的结果是堆栈跟踪.

Where 'devdb' is a service name and not an SID. The result of running this script is the stack trace.

(oracle-test)[1]jgoodell@jgoodell-MBP:python$ python example.py 
Traceback (most recent call last):
  File "example.py", line 8, in <module>
    result = engine.execute("create table test_table (id NUMBER(6), name VARCHAR2(15) not NULL)")
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1621, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 1669, in contextual_connect
    self.pool.connect(),
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 777, in _do_get
    con = self._create_connection()
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 318, in __init__
    self.connection = self.__connect()
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/pool.py", line 368, in __connect
    connection = self.__pool._creator()
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/engine/strategies.py", line 80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/Users/jgoodell/.virtualenvs/oracle-test/lib/python2.6/site-packages/sqlalchemy/engine/default.py", line 279, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
 None None

如果'devdb'是SID而不是服务名称，则此示例可以正常工作，我一直在尝试对连接字符串进行不同的排列，但没有找到任何可行的方法. SQLAlchemy文档中似乎也没有任何内容可以明确说明如何处理Oracle连接的SID诗句服务名称.

If 'devdb' were an SID and not a service name this example would work just fine, I've been trying different permutations of the connection string but haven't found anything that works. There also does not appear to be anything in the SQLAlchemy documentation that explicitly explains how to handle SID's verses service names for Oracle connections.

推荐答案

我找到了答案，您必须在'@之后的连接字符串中使用tnsnames.ora文件中使用的相同连接字符串.像这样

I've found the answer you have to use the same connection string that would be used in a tnsnames.ora file in the connection string after the '@" like so

from sqlalchemy import create_engine

if __name__ == "__main__":                                                                                                                                                        
    engine = create_engine("oracle+cx_oracle://<username>:<password>@(DESCRIPTION = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)))")                                                                                                                                                   
    result = engine.execute("create table test_table (id NUMBER(6), name VARCHAR2(15) not NULL)")
    result = engine.execute("drop table test_table")

此示例运行得很好，您可以注释掉drop语句并检查数据库以查看表已创建.

This example runs just fine, and you can comment out the drop statement and check the DB to see that the table was created.

这篇关于将Oracle服务名称与SQLAlchemy结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！