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.

5 Comments so far »

  1. Hugh said,

    Wrote on June 23, 2008 @ 8:57 pm

    Hello,

    I just wanted to thank-you for your comments and information. I was directed here from the python newsgroup.

    It’s very nice to have an up-to-date summary of what is happening in and around SQLServer with respects to Python.

    Being new too using Python with SQLServer it was hard to figure out what is out there that is still active and / or worthwhile.

    I don’t plan on doing anything too complex, but I will take up your preference and look at using pyodbc for accessing SQL server from python.

    Thanks again,

    Hugh

  2. Alexander said,

    Wrote on October 15, 2008 @ 7:20 am

    A year has passed since you created this post, I am interested in seeing how your rankings would be now maybe? Thanks.

  3. Matthias said,

    Wrote on October 16, 2008 @ 8:21 am

    Beter late than never! Thanks for this roundup, has been very helpful in making a choice.

    I’d also love to see an updated version though, after some googling around, I think pyodbc still seems to be the solution to go with.

    Again, thanks and best regards.

  4. Using Python and MS-SQL Server | keyongtech said,

    Wrote on January 18, 2009 @ 6:31 pm

    […] not concerned about performance so ODBC would be fine to use as > well. Have a look at this: http://ramblings.timgolden.me.uk/200…hin-python-25/ It’s hardly comprehensive, but it more-or-less answers your question. > Also, being new to […]

  5. Vitaliy said,

    Wrote on February 20, 2012 @ 2:09 pm

    Thanks, i’ve spend an hour trying to compile pymssql 2 beta on my winxp box. Probably i have to use pyodbc.

Comment RSS · TrackBack URI

Leave a Comment

OpenID

Sign in with your OpenID ?

Anonymous

Name: (Required)

E-mail: (Required)

Website:

Comment: