Make create_engine accept parameterized URL when Oracle TNS name is used

Hey, SQLAlchemy folks... to finish switching sqlpython over to sqlalchemy-managed connections, we need a little extra capability for create_engine with Oracle, and I'm attaching a patch for engine/url.py to supply it. Can you consider applying it to your trunk?

I've got an Oracle database ORCL on localhost, but I need to connect to it with the TNSnames.ora label of DBASE. Here's what's happening as of svn rev 5883 in the 0.5.3 trunk:

>>> from sqlalchemy import *
>>> create_engine('oracle://jrrt:jrrt@dbase').connect()
<sqlalchemy.engine.base.Connection object at 0xb65a148c>
>>> create_engine('oracle://sys:syspasswd@localhost/orcl?mode=2').connect()
<sqlalchemy.engine.base.Connection object at 0xb76daecc>
>>> create_engine('oracle://sys:syspasswd@dbase?mode=2').connect()
Traceback (most recent call last):
  *** boom ***  (screams)  (sirens)
  File "/home/catherine/dev/sqla4336/lib/sqlalchemy/engine/strategies.py", line 80, in connect
    raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12154: TNS:could not resolve the connect identifier specified
 None None

In other words, ?param=val parameters, right now, only work when the url has the full hostname and database name explicitly - not when the TNS name is used in the url. With this patch, they work either way.

I think the patch fits with the documentation, or at least doesn't contradict it.

From the docs at http://www.sqlalchemy.org/docs/05/reference/dialects/oracle.html:

Connecting with create_engine() uses the standard URL approach of oracle://user:pass@host:port/dbname?key=value&key=value.... If dbname is present, the host, port, and dbname tokens are converted to a TNS name using the cx_oracle makedsn() function. Otherwise, the host token is taken directly as a TNS name.

Additional arguments which may be specified either as query string arguments on the URL, or as keyword arguments to create_engine() are:

* allow_twophase - enable two-phase transactions. Defaults to True.
* auto_convert_lobs - defaults to True, see the section on LOB objects.
* auto_setinputsizes - the cx_oracle.setinputsizes() call is issued for all bind parameters. This is required for LOB datatypes but can be disabled to reduce overhead. Defaults to True.
* mode - This is given the string value of SYSDBA or SYSOPER, or alternatively an integer value. This value is only available as a URL query string argument.

From the docs at http://www.sqlalchemy.org/docs/05/reference/dialects/oracle.html:

Comments (9)