Using sqlalchemy dburi with Oracle using External Password Store?

Question

I am trying to get a oracle sqlalchemy dburi working with an external password store (Oracle Wallet)

I have tried using the standard sqlplus syntax for a external password store with no luck..

sqlalchemy.dburi="oracle:///@PROD"

I can connect successfully using sqlplus as follows:

sqlplus /@PROD

I can't find any information on how to use external password stores with sqlalchemy or turbogears?

Answer 1

Off the top comment: Make sure you are running version 5.2.1+ of cx_oracle (http://cx-oracle.readthedocs.io/en/latest/releasenotes.html). Prior to that a bug in SessionPools existed.

Answer 2

Based off digging through the source code for sqlalchemy, the correct way to do this is to set the username property (not the database property) of the sqlalchemy.engine.url.URL to your TNS alias for your wallet.

With sqlalchemy.engine.url.make_url

import sqlalchemy.engine.url

url = url.make_url('oracle+cx_oracle://')
url.username = '/@PROD'
engine = create_engine(url)

With sqlalchemy.engine.url.URL

import sqlalchemy.engine.url

url = url.URL('oracle+cx_oracle', username='/@PROD')
engine = create_engine(url)

This is because ultimately, for cx_Oracle to use an Oracle Wallet, you must use the user parameter in cx_Oracle.connect.

e.g.

import cx_Oracle

connection = cx_Oracle.connect(user='/@PROD')
# Because the first parameter in connect is user, the following is also valid
# connection = cx_Oracle.connect('/@PROD')