handling unicode data

[Tim Golden]

[Filipe]

| I've done some searching and settled for pymssql, but it's 
| not too late to change yet.

Indeed, the good thing about the DBAPI-compatibility of
such libraries is that you can often switch and switch
about with no cost to you at all. (Believe me, I've done
it). Sometimes there is a cost because... well, see the
notes below.

| I've found these options to connect to a MSSqlServer database:
| 
| SQLServer for Python (discontinued?)
| http://www.object-craft.com.au/projects/mssql/ 
| Pymssql
| http://pymssql.sourceforge.net/

These two are broadly equivalent, wrapping the ntwdblib.dll
library which -- as someone else has pointed out -- is going
the way of all mortal libs. 

Advantage: 
+ pretty much to-the-metal access to the database.
+ Both will work on Linux via FreeTDS

Disadvantage: 
+ the object-craft one, which I've been using for years, 
isn't supplied for 2.4+. I compiled it with the MingW 
compiler but the result didn't work (in some way which I now 
can't remember). I still use it for 2.3 work.
+ the pymssql one works in (indeed requires) 2.4 but doesn't 
allow for passthrough authentication, as far as I can determine. 
Worked around by having our DBA create named logons, but still
a bit of a pain. As noted, doesn't seem to handle unicode
especially well, which surprises me since the author is obviously
from a non-Western character set nation.

(I'm ashamed to say I've never tried to contact the pymssql
developer to point out its shortcomings).

| mxODBC (commercial license)
| http://www.egenix.com/files/python/mxODBC.html

This works fine, but requires a commercial license for
non-personal use. Since I was happily using an opensource
library, I've never looked into this too much. Also, doesn't
support .nextset on cursors, which was a bit of a pain
for me.

Should work on Linux via iODBC or UnixODBC and FreeTDS

| ADODB for Python (windows only)
| http://phplens.com/lens/adodb/adodb-py-docs.htm

This is a non-DBAPI extension, which is no problem if
that's what you're after. It also requires mxODBC for
MSSQL access (cf above).

| ASPN Recipe
| http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/144183

There seems to be quite enough discussion on the comments on this
recipe! Personally, I felt that, unless I had no other options 
open to me, this would be an unwieldy solution.

I can add:

Adodbapi
http://adodbapi.sourceforge.net/

which I also use for 2.4 work. 

Advantages:
+ Works wherever you have pywin32 (or ctypes, I suppose) and COM
available.
+ DBAPI-compliant, wrapping standard(ish) Win32 functionality.

Disadvantages:
+ The module developer has had to work with different behaviours among
different drivers, and the results can be unwieldy, especially when
looking
at tracebacks.
+ I never quite worked out the transactional behaviour (altho' that
probably
says more about me than about the module). Basically, you generally have
to
commit with this module where you don't with the others.
+ There are one or two small bugs to do with, IIRC, empty row returns.
There
was some talk on the DBAPI-SIG (which I don't follow closely) about
someone
picking up the module as the original developer didn't seem to be
around,
but I'm not aware that anyone's done that.
+ Won't work on Linux (

In summary

+ Depends on what your requirements are, but...

+ Go for ADODBAPI for the widest spread of versions and licenses, but
the least cross-platformability
+ Go for Object Craft MSSQL for <= 2.3 and best overall behaviour
+ Go for pymssql for >= 2.4 with some small limitations
+ Go for mxODBC for general purpose databasing, cross-platform, but
without .nextset and possibly Commercial licensing
+ If all else fails, and you've got the SQL Server command line tools,
use the ASPN Recipe

TJG

________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________