Python cx_Oracle 在连接字符串上使用 SID 而不是服务名称时不连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24149138/
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
cx_Oracle doesn't connect when using SID instead of service name on connection string
提问by Andy
I have a connection string that looks like this
我有一个看起来像这样的连接字符串
con_str = "myuser/[email protected]:1521/ora1"
Where ora1is the SID of my database. Using this information in SQL Developer works fine, meaning that I can connect and query without problems.
ora1我的数据库的 SID在哪里。在 SQL Developer 中使用此信息工作正常，这意味着我可以毫无问题地连接和查询。
However, if I attempt to connect to Oracle using this string, it fails.
但是，如果我尝试使用此字符串连接到 Oracle，则会失败。
cx_Oracle.connect(con_str)
DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This connection string format works if the ora1is a service name, though.
ora1但是，如果是服务名称，则此连接字符串格式有效。
I have seen other questions that seem to have the reverse of my problem (it works with SID, but not Service name)
我已经看到其他问题似乎与我的问题相反（它适用于 SID，但不适用于服务名称）
- Using Oracle Service Names with SQLAlachemy
- Oracle SID and Service name; connection problems
- cx_Oracle & Connecting to Oracle DB Remotely
What is the proper way to connect to Oracle, using cx_Oracle, using an SIDand not a service name? How do I do this without the need to adjust the TNSNAMES.ORAfile? My application is distributed to many users internally and making changes to the TNSNAMESfile is less than ideal when dealing with users without administrator privileges on their Windows machines. Additionally, when I use service name, I don't need to touch this file at all and would like it keep it that way.
使用cx_Oracle、使用SID服务名称而不是服务名称连接到 Oracle 的正确方法是什么？如何在不需要调整TNSNAMES.ORA文件的情况下执行此操作？我的应用程序在内部分发给许多用户，TNSNAMES在处理 Windows 机器上没有管理员权限的用户时，对文件进行更改不太理想。此外，当我使用服务名称时，我根本不需要接触这个文件，并希望它保持这种状态。
采纳答案by Andreas Fester
I a similar scenario, I was able to connect to the database by using cx_Oracle.makedsn()to create a dsnstring with a given SID(instead of the service name):
我有一个类似的场景，我能够通过使用cx_Oracle.makedsn()给定（而不是服务名称）创建一个dsn字符串来连接到数据库SID：
dsnStr = cx_Oracle.makedsn("oracle.sub.example.com", "1521", "ora1")
This returns something like
这将返回类似
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.sub.example.com)(PORT=1521)))(CONNECT_DATA=(SID=ora1)))
which can then be used with cx_Oracle.connect()to connect to the database:
然后可以用于cx_Oracle.connect()连接到数据库：
con = cx_Oracle.connect(user="myuser", password="mypass", dsn=dsnStr)
print con.version
con.close()
回答by Srinivas Ganti
It still may not work. You need to take the output of dsnStr and modify the string by replacing SID with SERVICE_NAME and use that variable in the con string. This procedure worked for me.
它仍然可能不起作用。您需要获取 dsnStr 的输出并通过将 SID 替换为 SERVICE_NAME 并在 con 字符串中使用该变量来修改字符串。这个程序对我有用。
回答by daniel
I also met this issue. The solution is:
我也遇到了这个问题。解决办法是：
1: get the service name at tnsnames.ora
2: put the service name in
con_str = "myuser/[email protected]:1521/ora1"
回答by Piotr Dobrogost
For those looking for how to specify service_nameinstead of SID.
对于那些正在寻找如何指定service_name而不是 SID 的人。
From changelogfor SQLAlchemy 1.0.0b1 (released on March 13, 2015):
来自SQLAlchemy 1.0.0b1 的变更日志（2015 年 3 月 13 日发布）：
[oracle] [feature] Added support for cx_oracle connections to a specific service name, as opposed to a tns name, by passing
?service_name=<name>to the URL. Pull request courtesy S?awomir Ehlert.
[oracle] [功能] 通过传递
?service_name=<name>到 URL ，添加了对特定服务名称的 cx_oracle 连接的支持，而不是 tns 名称 。拉取请求由 S?awomir Ehlert 提供。
The change introduces new, Oracle dialect specific option service_namewhich can be used to build connect string like this:
该更改引入了新的 Oracle 方言特定选项service_name，可用于构建连接字符串，如下所示：
from sqlalchemy import create_engine
from sqlalchemy.engine import url
connect_url = url.URL(
'oracle+cx_oracle',
username='some_username',
password='some_password',
host='some_host',
port='some_port',
query=dict(service_name='some_oracle_service_name'))
engine = create_engine(connect_url)
回答by Mr. Mundkowsky
If you are using sqlalchemy and ORACLE 12, the following seems to work.
如果您使用的是 sqlalchemy 和 ORACLE 12，以下内容似乎有效。
from sqlalchemy import create_engine
con='oracle://user:password@hostname:1521/?service_name=DDDD'
engine = create_engine(con)
Note, you have to use the service name and not the SID. I don't know why, but the simple connection string that uses SID does not work.
请注意，您必须使用服务名称而不是 SID。我不知道为什么，但是使用 SID 的简单连接字符串不起作用。
回答by Daniel Holliday
SID's may not be easily accessible or you might not have it created for your database.
SID 可能不容易访问，或者您可能没有为您的数据库创建它。
In my case, I'm working from the client side requesting access to a cloud database so creating an SID didn't really make sense.
就我而言，我从客户端工作，请求访问云数据库，因此创建 SID 没有任何意义。
Instead, you might have a string that looks similar to this:
相反，您可能有一个类似于以下内容的字符串：
"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = something.cloud.company)
(PORT = 12345)) (ADDRESS = (PROTOCOL = TCP)(HOST = something.cloud.company)
(PORT = 12345)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
something.company)))"
You can use it in replacement of the SID.
您可以使用它来替换 SID。
connection = cx_Oracle.connect("username", "pw", "(DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP)(HOST = something.cloud.company)(PORT = 12345)) (ADDRESS =
(PROTOCOL = TCP)(HOST = something.cloud.company)(PORT = 12345))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = something.company)))")
回答by Christophe OGER
I thought during a while that I would not be able to use Magic SQL (%sql, %%sql) because of service name issue in connection that would force to use the alternative way described above with cx_Oracle.connect(), cx_Oracle.makedsn()...I finally found a solution working for me: declare and set a variable for the service name first and then use it in the command (since not working if literal string for service name put in the command !)
有一段时间我想我将无法使用 Magic SQL ( %sql, %%sql) 因为连接中的服务名称问题会强制使用上述与 cx_Oracle.connect() 描述的替代方式，cx_Oracle.makedsn()...我终于找到了一个适合我的解决方案：首先声明并设置服务名称的变量，然后在命令中使用它（因为如果将服务名称的文字字符串放入命令中，则不起作用！）
import cx_Oracle
user='youruser'
pwd='youruserpwd'
dbhost='xx.xx.xx.xx'
service='yourservice'
%load_ext sql
%sql oracle+cx_oracle://$user:$pwd@$dbhost:1521/?service_name=$service
output (what you get in successful connection):
输出（你在成功连接中得到的）：
u'Connected: youruser@'
