[DB-SIG] [summary] Connecting to MS SQL from Python

[Hrvoje Niksic]

Eight weeks ago, I asked about the possibilities of connecting to MS
SQL server from within Python.  I received several responses, and I
promised to post a summary of my experiences.

As promised, here is the summary of my experiences after investigating
the possible solutions.

To remind you, I needed to access MS SQL from Python applications on
Linux.  In my company, we currently use Michael Peppler's Sybperl
(CTlib) for this purpose.  The Perl solution is free, so the Python
one should be free, or at least inexpensive, to stand a chance of
acceptance.

To cut a long story short, I must sadly report that I have NOT been
able to find a free solution that offers the needed functionality.  If
someone can offer additional insight, I'll be more than helpful, but I
admit I'm beginning to lose hope.

The long story follows, in the form of a listing of various
possibilities, in no particular order:

* Wrap Sybase's CTlib into Python.  Peter Godman's Sybase module is
  supposed to do that, but it appears unmaintained and in a really bad
  shape.  Besides, it's compliant with the old version of the DB API.

  For someone knowledgable with databases or with Python internals, it
  would probably be easy to write his own wrapper, or pick up where
  Peter left off.  But I am new to the world of databases, and know
  next to nothing about Python C internals, and for me it's a daunting
  task.

* Find an ODBC driver for MS SQL, and use the excellent mxODBC module
  written by M.-A. Lemburg.  It turned out that finding a Linux ODBC
  driver for MS SQL is not an easy thing to do.  Which brings us to
  these four attempts:

  * EasySoft ODBC-ODBC bridge (OOB).  It's a nice product, but it's
    still in beta, and it will cost 800$ when finished (160$ for a
    personal version).  If I could persuade my boss to buy it, I'd
    certainly be using it.

  * OpenLink ODBC drivers for Linux (the limited version that comes
    free).  This sounded promising, but when i tried them, they proved
    very convoluted.  They install ODBC libraries and a request
    broker, as well as the agents for various databases.  The MS SQL
    agent uses (sic!) CTlib.  So what you end up doing as having the
    application use OpenLink libraries which contact the OpenLink
    request broker, which talks to the agent, which uses CTlib to
    connect to the database.  When I couldn't get it to work after
    long and frustrating hours of reading manuals and tweaking
    conffiles, I gave up on OpenLink.

  * Merant (formerly InterSolv) ODBC drivers for MS SQL.  These are
    supposed to be available for Linux, but I couldn't find them on
    their site.  It remains a mystery, and I intend to try again soon.

  * FreeTDS ODBC drivers.  The FreeTDS project
    (<http://www.freetds.org>) is dedicated to writing full support
    for TDS (Tabular Data Stream, protocol used by Sybase and MS SQL),
    including CTlib, DB-Lib, ODBC, and JDBC.  However, their work is
    far from complete; in fact, the work on ODBC has hardly begun.  In
    a year or so, it may be usable, but not now.

* Write a proxy running on NT that reads serialized Python ODBC
  requests from clients, and issues the appropriate mxODBC calls
  locally, then writes serialized results back to the client.

  The problem with this is twofold: first, I'd need physical access to
  the NT machine, a compiler on Windows, Python on Windows, etc., and
  I have none of it.  Second, the performance of such a setup could
  prove to be a serious problem.  Python is no speed champion, and the
  overhead of all the serializations could prove fatal.  The OOB
  people at EasySoft did a lot of work to optimize their proxy, and
  they use C.  :-(