Using Oracle Service Names with SQLAlchemy

Question

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")

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

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.

Answer 1

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")

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

Answer 2

The module sqlalchemy now can handle oracle service_names. Have 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)

Answer 3

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:

(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.

---

If you are using 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)

Answer 4

cx_Oracle supports the passing of a 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 service_name through to the underlying call it makes to 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.