Geek Answers Handbook

Using Oracle Service Names with SQLAlchemy

I had an unpleasant problem connecting to an Oracle schema through SQLAlchemy using the service name. Here is my code as a script. (elements between angle brackets are space holders for real values ​​for safety 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")

Where "devdb" is the name of the service, not the SID. The result of running this script is a 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

If "devdb" was the SID and not the service name, this example will work fine, I am trying to use different permutations of the connection string, but I did not find anything that works. There is also nothing in the SQLAlchemy documentation that explicitly explains how to handle SID style service names for Oracle connections.

+8
source share
4 answers

I found the answer that you should use the same connection string that will be used in the tnsnames.ora file in the connection string after "@", 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")

This example works very well, and you can comment on the statement and check the database to see that the table has been created.

+18
source 
 import cx_Oracle dsnStr = cx_Oracle.makedsn('myhost','port','MYSERVICENAME') dsnStr = dsnStr.replace('SID', 'SERVICE_NAME') connect_str = 'oracle://user: password@ ' + dnsStr

The Macedon will create TNS as follows:

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = MyHost) (PORT = 1530))) (CONNECT_DATA = (SID = MYSERVICENAME)))

replacing "SID" with "SERVICE_TYPE" made it work for me.

If you use flask, sqlalchemy and 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)
+8
source

cx_Oracle supports passing the service_name to the makedsn function.

http://cx-oracle.sourceforge.net/html/module.html?highlight=makedsn#cx_Oracle.makedsn

It would be nice if the create_engine () API passed the name service_name to the base call it makes for makedsn ... something like this:

 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.
+1
source

The sqlalchemy module can now handle oracle service names. Take a look:

 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)
0
source

More articles:


All Articles