在 SQLAlchemy 中使用 Oracle 服务名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14140902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Oracle Service Names with SQLAlchemy
提问by snarkyname77
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)
我在使用服务名称通过 SQLAlchemy 连接到 Oracle 模式时遇到了一个令人讨厌的小问题。这是我作为脚本的代码。（出于安全原因，尖括号之间的项目是实际值的占位符）
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")
Where 'devdb' is a service name and not an SID. The result of running this script is the stack trace.
其中“devdb”是服务名称而不是 SID。运行此脚本的结果是堆栈跟踪。
(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
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.
如果 'devdb' 是 SID 而不是服务名称，这个例子就可以正常工作，我一直在尝试连接字符串的不同排列，但没有找到任何有效的方法。SQLAlchemy 文档中似乎也没有任何内容明确说明如何处理 Oracle 连接的 SID 和服务名称。
回答by snarkyname77
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
我找到了答案，您必须使用与 tnsnames.ora 文件中“@”之后的连接字符串相同的连接字符串，如下所示
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")
This example runs just fine, and you can comment out the drop statement and check the DB to see that the table was created.
这个例子运行得很好，你可以注释掉 drop 语句并检查数据库以查看表是否已创建。
回答by Matthew Moisen
import cx_Oracle
dsnStr = cx_Oracle.makedsn('myhost','port','MYSERVICENAME')
connect_str = 'oracle://user:password@' + dsnStr.replace('SID', 'SERVICE_NAME')
The makedns will create a TNS like this:
makedns 将创建一个像这样的 TNS：
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1530)))(CONNECT_DATA=(SID=MYSERVICENAME)))
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1530)))(CONNECT_DATA=(SID=MYSERVICENAME)))
replacing "SID" with "SERVICE_TYPE" got it to work for me.
用“SERVICE_TYPE”替换“SID”让它对我来说有效。
If you are using flask, sqlalchemy, and oracle:
如果您使用 Flask、sqlalchemy 和 oracle：
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import cx_Oracle
app = Flask(__name__)
dnsStr = cx_Oracle.makedsn('my.host.com', '1530', 'my.service.name')
dnsStr = dnsString.replace('SID', 'SERVICE_NAME')
app.config['SQLALCHEMY_DATABASE_URI'] = 'oracle://myschema:mypassword@' + dnsStr
db = SQLAlchemy(app)
回答by Dataku
The module sqlalchemy now can handle oracle service_names. Have a look:
模块 sqlalchemy 现在可以处理 oracle service_names。看一看：
from sqlalchemy.engine import create_engine
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username' #enter your username
PASSWORD = 'your_password' #enter your password
HOST = 'subdomain.domain.tld' #enter the oracle db host url
PORT = 1521 # enter the oracle port number
SERVICE = 'your_oracle_service_name' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)
#test query
import pandas as pd
test_df = pd.read_sql_query('SELECT * FROM global_name', engine)
回答by Brian Carpenter
cx_Oracle supports the passing of a service_name to the makedsn function.
cx_Oracle 支持将 service_name 传递给 makedsn 函数。
http://cx-oracle.sourceforge.net/html/module.html?highlight=makedsn#cx_Oracle.makedsn
http://cx-oracle.sourceforge.net/html/module.html?highlight=makedsn#cx_Oracle.makedsn
It would be nice if the create_engine() API passed the service_name through to the underlying call it makes to makedsn...something like this:
如果 create_engine() API 将 service_name 传递给它对 makedsn 进行的底层调用，那就太好了……像这样：
oracle = create_engine('oracle://user:pw@host:port', service_name='myservice')
TypeError: Invalid argument(s) 'service_name' sent to create_engine(), using configuration OracleDialect_cx_oracle/QueuePool/Engine.
Please check that the keyword arguments are appropriate for this combination of components.
