pymssql versus pyodbc versus adodbapi versus...

Question

I am relatively new to databases, performing SQL queries, etc. At the moment I am exclusively using MS SQL Server for my DBs (that's what we use at my work), but hopefully answers to my question can be more general than just for that particular DB.

I do a good chunk of my work using the DB interface app itself (in this case, SQL Server Management Studio), but I also do some work through python scripts, some of which run as cron jobs, some of which are websites such that users can submit queries into the DB.

I have been using the pymssql module for querying into & writing into DBs from python. But there are a few annoying problems that I'm running into. (It seems pymssql cannot use temporary tables, and also I've had troubles with "autocommit", but that's likely my own fault.)

So being ignorant to much of this, I'd just like a flavor of what direction I should go. As far as I can tell, I could either:

• use pymssql, which talks directly to the MS SQL servers
• use pyodbc, which requires installing FreeTDS & unixODBC first, and setting them up
• use adodbapi module, and I'm not sure what requirements are needed here.
• another method??

Thing is, I don't FULLY get what ODBC does. It seems like some sort of "authentification intermediary", sort of like a middle man to ensure that the user is who he / she says. And I have even less understanding of ADO.

While pymssql seems the easiest way to go, it also seems the one that folks think less highly of, and that it will eventually be deprecated. I'd rather learn the "right" way since I am pretty much just starting out with this stuff.

So I'm looking for some knowledgeable opinions regarding the best way to get python to talk to MS SQL Server, and to talk to DBs in general. I would define "best" as the best for someone who will do moderately complex stuff with it. (While I am just getting started, it's something that I'll eventually have to ramp up on quite a bit.)

Thanks! Mike

Answer 1

pyodbc (recommended)

I've started using this module a few months ago and have no complaints so far. The development community seems more active than pymssql. I had no installation issues so far (32bit Win XP and 64bit Server 2008 R2). You'll have the additional advantage that you are not limited to MS SQL Servers.

You might find: http://www.lfd.uci.edu/~gohlke/pythonlibs/ helpful if you're installing on windows.

pymssql

I've worked with the 1.x version for about 2.5 years. I had some minor problems with the size limitations for large objects. I also ran into massive problems when multi-threading (to the point I had to turn if off). 1.x was based on deprecated MS libraries. However, the recent version is 2.x and does not use these deprecated libraries any longer. Instead it uses FreeTDS (like pyodbc). It sounds good, but I haven't tried it yet.

adodbapi

I've tried switching out pymssql and replacing it with adodbapi about a year ago and some of the queries which worked fine with pymssql failed for no obvious reason. I quickly abandoned adodbapi again.

Answer 2

From my experience, PYODBC is a bit problematic these days. Firstly, it does not seem to manage unicode strings properly or reliably. Secondly, the API is a bit out of date and specific and less Python DB-API (PEP-249) compliant.

I am in the midst of migrating project code to PYMSSQL which, during my testing thus far, covers the bases on both of the issues above. I'm not aware of any deprecation with this API as it seems to be updated somewhat frequently (major version in Oct 2013), the docs are more or less up to date and the is active.

These are just my current findings and progress with PYODBC and PYMSSQL. All subject to change of course :)