Database experiences in Python: Good or Bad?

[David Rushby]

John Hall wrote:
> Does anyone know if/how well Python plays with Firebird?
> (Firebird is an OSS DBMS derived from an Open version of
> Borland/Inprise/Borland's Interbase.)
> http://firebird.sourceforge.net/
> http://www.ibphoenix.com/

  I'm actively maintaining and enhancing a Python+(Firebird/Interbase)
package named kinterbasdb:
http://kinterbasdb.sourceforge.net

  At this point, it's closely DB API-complient and reliable; I use it
in production, and have communicated with numerous others who do the
same.  There are at least two Zope adapters built upon it (see
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/*checkout*/kinterbasdb/Kinterbasdb-3.0/docs/links.html
).

  I provide native Windows installers, a distutils-ified source
distribution, and reasonably extensive documentation.  You can consult
the documentation online here:
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/*checkout*/kinterbasdb/Kinterbasdb-3.0/docs/index.html?rev=HEAD&content-type=text/html

> I feel that Firebird is 'better' by several measures than MySQL and
> PostgreSQL, particularly on Windows...

  Here's a good overview of Firebird:
http://www.ibphoenix.com/ibp_act_db.html

  Firebird is undoubtedly better than MySQL, except perhaps with
regard to speed.

  I wouldn't go so far as to claim that Firebird is better than
PostgreSQL on Linux (the two have comparable feature sets, with
relative strengths and weaknesses), but Firebird works far more
smoothly on Windows since there's a native version.  I'm aware that
it's possible to run PostgreSQL on Windows via cygwin, but that setup
feels hackish and far-short-of-production-ready.

  As for ease of use, Firebird is excellent.  The Firebird server is
easy to set up (binary installer on Windows), kinterbasdb is easy to
set up (binary installer on Windows), and good administrative GUIs are
available (see http://www.ibphoenix.com/ibp_contrib_download.html#ADMIN
, and especially http://www.mengoni.it/downloads.html ).

  More important than ease of initial setup is the ease of routine
administration.  Firebird continues the Interbase tradition of
virtually administering itself.  This is crucial to me because my
software must run unattended in remote environments where a DBA is
unavailable; Firebird works splendidly in such environments. 
Additionally, I'm about to add support for the Firebird Services API
to kinterbasdb, which will allow administrative tasks to be performed
cleanly from Python (as opposed to invoking the command line tools). 
If you're operating in a centralized environment, hands-off
administration is not so important, and might even be a disadvantage
(tweakability is limited compared to SAPDB, Oracle, and their ilk).

  An advantage of Firebird compared to MySQL and PostgreSQL is that
the Interbase 6.0 core documentation (which is highly relevant to
Firebird 1.0) is very extensive.  The documentation has that
commercial "someone was paid to write about both sexy and unsexy
topics" feel:
ftp://ftpc.inprise.com/pub/interbase/techpubs/ib_b60_doc.zip

  My strongest caveat against the current version of Firebird is that
its scalability is limited.  The server doesn't utilize multiple CPUs
very well, and the client library is not thread-safe.  Combine the
thread-unsafe Python interpreter with the thread-unsafe Firebird
client library, and you've a recipe for lack of scalability.  Then
again, those for whom scalability is a primary concern would not be
well advised to choose CPython in the first place (say what you like
about multiprocess workarounds, the single-process/multithread
philosophy is steadily gaining ground against the multiprocess
approach--witness the evolution of Apache and many RDBMS servers).

  One scalability-friendly feature that Firebird shares with
PostgreSQL is Multi-Version Concurrency Control (see
http://www.ibphoenix.com/ibp_bill_todd_mga.html ).  Unfortunately,
Firebird's inability to properly utilize multiple CPUs undermines the
worth of its MVCC.

> ... but being better does not count for much unless it's also fashionable, 
> which it isn't, yet.

  This is a legitimate concern, but the same argument could be applied
to Python.   I use Python solely because I consider it "better" than
the competition overall, not because it's more "fashionable" or has
broader library availability.  In all fairness, though, Python
undeniably has more momentum than Firebird, so your concern is more
valid with regard to Firebird.

> I already have more unknowns than I'd like in a project I'm working
> on, so if Firebird/Python is currently dubious, I'll stay with MySQL
> for now, and consider changing the DB later.

  The Firebird/Python combination is not dubious (I am currently, and
will continue to, maintain and enhance kinterbasdb), except insofar as
the future of Firebird itself is dubious.

  Here's my advice:

1.  If you're serious about the relational model, use something other
than MySQL.  If you're not, use a non-relational database such as
ZODB.

2.  If you're operating on Windows (as you implied), use Firebird if
you need ease of use, virtually effortless database administration,
and low overhead; use SAPDB if you need greater scalability and more
advanced features (e.g., subtransactions) at the expense of
administrative effort.  If you're operating on Linux/Unix, also
consider PostgreSQL.