Multiple dialect support in Call Level Interface (CLI/ODBC) connections

[David Jung]

Several enterprise RDBMs support connection via the Call Level Interface (CLI) protocol (ISO/IEC 9075-3:2003) - commonly referred to as ODBC or as various vendor-specific names, though technically ODBC is only Microsoft's implementation of CLI.

For example, the IBM PureData product line of data warehouses run Redhat Linux and a modified version of PostgreSQL internally, but provide only CLI (ODBC) as an external service protocol.

Such RDBMSs can be connected to via SQLAlchemy using pyodbc, but alas SQLAlchemy seems to assume it is connecting to an MSSQL server and only allows the mssql dialect to be specified.

For example:

engine = create_engine('postgresql+pyodbc://user:pw@server')

results in the exception: NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.pyodbc

Whereas using:

engine = create_engine('mssql+pyodbc://user:pw@server')

will succeed in creating the connection, but attempted use results in the error:

ProgrammingError: (ProgrammingError) ('42S02', "[42S02] ERROR:  Function 'USER_NAME()' does not exist\n\tUnable to identify a function that satisfies the given argument types\n\tYou may need to add explicit typecasts (29) (SQLExecDirectW)") 'SELECT user_name()' () 

due to the use of an MSSQL specific function with PostgreSQL (the query issued was a simple SELECT * FROM table - so the SELECT user_name() was presumably generated by SQLAlchemy).

Please allow the specification of arbitrary dialects when connecting via the CLI/ODBC protocol (pyodbc) so that the dialect can be specified to match the RDBMS in question.

[Mike Bayer]

hi David -

a purely generic "pyodbc" connector is not possible. SQLAlchemy is not just a database connection library, it provides dialects which speak the specific SQL variant and integrate deeply with vendor-specific settings and extensions provided by the backend. So the specification of "mssql://" will of course produce the wrong dialect. Current Pyodbc backends included include SQL Server and MySQL. There is of course no "assumption" of backend.

Pyodbc itself in most cases needs to provide support specific to a backend. Please note at https://code.google.com/p/pyodbc/wiki/ConnectionStrings#Databases that Pyodbc supports connectivity for MSSQL, MySQL, and Excel; other backends may not be fully functional.

For Postgresql+pyodbc support, please specify a new ticket and a pull request which adds among other things an appropriate connector in lib/sqlalchemy/dialects/postgresql/pyodbc.py; all pyodbc dialects mix in the base dialect plus the connector in lib/sqlalchemy/connectors/pyodbc.py (where I again ask that you please note, hardly any "assumption" of any specific backend there). For examples see dialects/mysql/pyodbc.py and dialects/mssql/pyodbc.py.