cx_Oracle connect to Oracle Connection Manager without tnsnames.ora file

Question

I need to connect to Oracle via OCM a non OCM connection looks to be simple

conn_str = 'user/password@host:port/service_name'

# oracle connection
self.con = cx_Oracle.connect(conn_str)

same can be translated to this when using tnsnames.ora

db_alias = 
       (DESCRIPTION=               
           ) 
           (ADDRESS= 
              (PROTOCOL=TCP)(HOST=DatabaseHost)(PORT=DatabasePort) 
           ) 
           (CONNECT_DATA= 
              (SERVICE_NAME=DatabaseService)
           ) 
       )

when we connect to OCM using tnsnames.ora, the following is added

db_alias = 
   (DESCRIPTION= 
          ***(SOURCE_ROUTE=YES)
          (ADDRESS= 
              (PROTOCOL=TCP) (HOST=ConnectionManagerHost)(PORT=ConnectionManagerPort)***
       ) 
       (ADDRESS= 
          (PROTOCOL=TCP)(HOST=DatabaseHost)(PORT=DatabasePort) 
       ) 
       (CONNECT_DATA= 
          (SERVICE_NAME=DatabaseService)
       ) 
   )

how do we do the same in python with cx_Oracle?

Answer 1

[Update: cx_Oracle was renamed to python-oracledb. See the release announcement - use this version instead of cx_Oracle. Also the old makedsn() function was obsoleted by the ConnectParams object.]

Use the TNS alias in the connection string:

conn_str = 'chris/mypassword@db_alias'  # use the entry from the tnsnames.ora file
self.con = cx_Oracle.connect(conn_str)

or use the alias with the other form of connect() like this:

self.con = cx_Oracle.connect(user='chris', password='mypassword', dsn='db_alias') 

If you know the actual connect descriptor then you could do:

cs = """(DESCRIPTION=
             (FAILOVER=on)
             (ADDRESS_LIST=
               (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))
             (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))"""

self.con = cx_Oracle.connect(user="chris", password='mypassword', dsn=cs)

See Connection Strings