Using MSSQL from within Python
As you may have gathered from earlier posts I’m a professional database developer, currently using Microsoft SQL Server 2000 / 2005. Wherever it makes sense, I use Python to access and manipulate the data. Over the years I’ve tried out several different libraries for connecting to SQL Server so I present below what seems to be the current landscape.
ODBC Solutions
ntwdblib.dll Solutions
Other Solutions
My own default choice these days is pyodbc. It’s active and pretty much feature-complete (they added the .nextset method at my request). It’s also the dbapi module of choice for sqlalchemy’s mssql backend. ceODBC is a relative newcomer, and it’s not clear what it offers which the others don’t. mxODBC is of course a stable and mature product which recently released v3, and probably the most fully-featured, but it does require a commercial license.
The ntwdblib solutions all suffer from the fact that MS are removing support for that method of db access after MSSQL 2005 (and even in SQL2005 it’s only there in SQL2000-compatibility mode), so their days are numbered. In addition, the Object Craft module hasn’t been updated for 3 years with no supplied binary past Python 2.3. (I used it for years before that so I can attest to its robustness). The pymssql module also hasn’t updated in about a year, and does have some slightly funny issues with Unicode.
Of the two other solutions, the osql one is a bit of a curiosity, parsing the output from SQL Server’s commandline tools. As is noted in the comments on the recipe, it’s mostly handy when you’ve got no other possibilities, perhaps in an admin situation. The adodbapi module was moribund for several years, but has recently been picked up again by Vernon Cole who’s resurrected the SourceForge project and applied some of the outstanding patches. That said, I’m not aware that it offers anything which I can’t achieve with an ODBC solution.
Update (June 2008): adodbapi is now maintained as part of the pywin32 packages, as of release 211.