Python and ODBC?

[Alex Martelli]

"Ryujin" <ryu-jin at bigfoot.com> wrote in message news:93tfo0$12p$1 at ryu.it...
> Which is the best solution to work with python and a SQL database under
Windows?

So much depends on what _scale_ you need to work...!  Multi-user, or
single?  Do you need transactions, stored procedures/triggers, etc?
Even typical database size is a factor.

For really small-scale needs, for example, Gadfly has interesting
characteristics -- you might want to look into it -- but it is not
designed to scale up to intensive multi-user accesses or really
huge databases.

So, hard to give a 100% answer, but, if you're Windows-only, you might
want to try MSDE, the freely-redistributable version of SQL Server
(shorn of the various graphical front-ends &c); you can download and
use/redistribute it freely if you have a license for Microsoft Studio
or Office Developer.  It scales well up to 5 simultaneous users, and
has all the trimmings you'd expect from a professional RDBMS, such
as transactions, stored-procedures, etc, etc.  If you don't care about
redistribution, you can also download and use (but not redistribute)
SQL Server Developer Edition, which is the same thing plus the
graphical aids for DB administration/development/debugging.

It does have an upper boundary of 2 gigabytes per database -- which
was not a problem for us, but might be in other applications.

I have not tried out the versions of those related to SQL Server 2000,
only the ones related to SQL Server 7 (the previous release), which
were still around for download on the MS site a couple months ago
(sorry, don't have the URL and don't know if they're still there, as
I found out the same bits also were on our MSDN Universal subscription
CDROM's, so I saved downloading the big packs) -- in any case, I have
no reason to doubt that SQL Server 2000 is at least as good (but I
only did a few tests on that myself, and not on the MSDE-equivalents).

> I mean which module do I have to install on Python 2.0?

That's part of what I like re MSDE -- you can talk to it directly
from Python via ADO and ADOX (the client-side parts of the MSDAC,
which is also freely downloadable/redistributable), and that turns
out to be VERY fast as well as quite convenient.  It may be more so
in our case since we don't _only_ use Python to talk to those DB's;
ADO/ADOX can be accessed equally well from a great variety of
programming/scripting languages.

Really, the only serious downside I can see is the very strong
Microsoft lock-in that is implicit in all of this.  Since our app
was MS-platforms-only anyway for another variety of reason, I
chose MSDE with a perfectly clear conscience.  If I had to avoid
platform lock-ins, and still get transactions and stored procedures
(each of those being a real 'must' for us), then, I dunno -- maybe
Interbase, now open-source, has those, but I don't really know
much about it; maybe Postgres (again, no first-hand experience).


> What about under Linux? I'm trying to install mxODBC module with MySQL db
> on a Linuxppc box.

I'll pass on this one, having no experience with MySQL (I don't even
know if it supports transactions and stored procedures!).  I do know
it's very popular, so I guess one can't go TOO far wrong with it.


Alex