Debug ODBC Connection String for Custom Dialect

[Lycovian]

TL;DR:

I'm trying to debug what is actually being sent to the pyodbc.connect function on connect in a custom dialect.  I need to see the connection string that is being sent to the pyodbc.connect function *right* before it is sent but it has been difficult for me to unravel the layers of indirection on the create_engine call. 

Long version:

If you care for more information I have this DSN related connect string for the custom dialect I am writing for Teradata:

engine = sqlalchemy.create_engine("teradata://testsqlatd:password@td_testsqlatd", encoding='utf-8', echo=True)

This connection string works and connects properly to my Teradata box (yay!).

In my custom dialect I have subclassed so I can get some visibility into the ODBC connect string SQLA is constructing:

    def create_connect_args(self, url):

        connector = super(TeradataDialect_pyodbc, self).create_connect_args(url)

        print connector

        return connector

This code appears to call sqlalchemy/connectors/pyodbc.py [create_connect_args].  And returns:

[['dsn=td_testsqlatd;UID=testsqlatd;PWD=password'], {}]

I assume that this string is roughly what is passed by SQLA to pyodbc at some future point as the ODBC connection string.  In my case the string above connects successfully.  Oddly enough though UID is not a valid ODBC connection parameter for the Teradata ODBC driver.  For Teradata the parameter must be Username.  Same with PWD, this isn't valid for the Teradata ODBC driver.  It should be Password. 

According to my testing and the Teradata ODBC docs the valid version of this ODBC connection string should be:

'dsn=td_testsqlatd;Username=testsqlatd;Password=password'

I have verified directly with pyodbc that the first form of the connect string fails and the version directly above works, yet somehow in SQLAlchemy it connects.  Because of this I believe that SQLA is rewriting the string further before connecting to the Teradata ODBC driver via pyodbc.  I can't find out where that is happening though.  Because of this I would like to intercept the pyodbc.connect call and see exactly what ODBC connection string SQLA is invoking it with. 

Any ideas how to log what exactly the connection string that SQLAlchemy is sending to pyodbc.connect?

[Michael Bayer]

Lycovian <mfwi...@gmail.com> wrote:

> TL;DR:
> I'm trying to debug what is actually being sent to the pyodbc.connect function on connect in a custom dialect. I need to see the connection string that is being sent to the pyodbc.connect function *right* before it is sent but it has been difficult for me to unravel the layers of indirection on the create_engine call.
>
> Long version:
> If you care for more information I have this DSN related connect string for the custom dialect I am writing for Teradata:
> engine = sqlalchemy.create_engine("teradata://testsqlatd:password@td_testsqlatd", encoding='utf-8', echo=True)
>
> This connection string works and connects properly to my Teradata box (yay!).
>
> In my custom dialect I have subclassed so I can get some visibility into the ODBC connect string SQLA is constructing:
> def create_connect_args(self, url):
> connector = super(TeradataDialect_pyodbc, self).create_connect_args(url)
> print connector
> return connector
>
> This code appears to call sqlalchemy/connectors/pyodbc.py [create_connect_args]. And returns:
> [['dsn=td_testsqlatd;UID=testsqlatd;PWD=password'], {}]
>
> I assume that this string is roughly what is passed by SQLA to pyodbc at some future point as the ODBC connection string.

that is exactly the string that is passed to pyodbc. The examples at http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html#additional-connection-examples go into great detail with many examples of how this function works, even without digging into the function itself.

> In my case the string above connects successfully. Oddly enough though UID is not a valid ODBC connection parameter for the Teradata ODBC driver. For Teradata the parameter must be Username. Same with PWD, this isn't valid for the Teradata ODBC driver. It should be Password.

>
> According to my testing and the Teradata ODBC docs the valid version of this ODBC connection string should be:

> 'dsn=td_testsqlatd;Username=testsqlatd;Password=password’

Well then it seems like your custom dialect might want to define its own version of create_connect_args().

> I have verified directly with pyodbc that the first form of the connect string fails and the version directly above works, yet somehow in SQLAlchemy it connects. Because of this I believe that SQLA is rewriting the string further before connecting to the Teradata ODBC driver via pyodbc.

it is not. however, when we want to analyze things like this quickly, we can use pdb.

Applying this patch:

diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index a5af6ff..f0fb0ec 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -374,6 +374,8 @@ class DefaultDialect(interfaces.Dialect):
)

def connect(self, *cargs, **cparams):
+ import pdb
+ pdb.set_trace()
return self.dbapi.connect(*cargs, **cparams)

def create_connect_args(self, url):


then fire up a shell and run:

$ python
Python 2.7.5 (default, Mar 7 2014, 19:17:16)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.2.79)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import create_engine
>>> e = create_engine("mssql+pyodbc://scott:tiger@ms2005")
>>> e.connect()
> /Users/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py(379)connect()
-> return self.dbapi.connect(*cargs, **cparams)
(Pdb) cargs
('dsn=ms2005;UID=scott;PWD=tiger',)
(Pdb) cparams
{}
(Pdb) self.dbapi
<module 'pyodbc' from '/Users/classic/.python-eggs/pyodbc-3.0.7_beta10-py2.7-macosx-10.8-intel.egg-tmp/pyodbc.so'>
(Pdb)

we can verify this is the same thing that create_connect_args is giving us:

(Pdb) from sqlalchemy.engine import url
(Pdb) self.create_connect_args(url.make_url("mssql+pyodbc://scott:tiger@ms2005"))
[['dsn=ms2005;UID=scott;PWD=tiger'], {}]

OK, a list and not a tuple, slight implementation detail. But there you have it.

[Lycovian]

Looks like the *nix version of the ODBC driver I was using is simply wrapping the Windows logic for parameters for DNS-less connections contrary to the documentation

For the record in Teradata for DNS-based connections the ODBC connection string would be on Linux:

'dsn=mydsn;Username=user;Password=pwd'

BUT for DNS-less connections the *nix version of the driver actually uses this connection string:

'driver=Teradata;UID=user;PWD=pwd'

Possibly they re-used the parsing logic from the Windows driver?  At any rate, thanks for the information.  That gave me the information I needed to figure out what was going on.

[Chuanlong Du]

Hi Lycovian,

Can you share your dialect for Teradata using pyodbc? I'm very interested.

Best,