another cgi / database concern (SQL vs. 'shelve')

[David Rushby]

"Stephen Aichele" <stephen at mochamail.com> wrote in message news:<mailman.1037124918.32702.python-list at python.org>...

> So the next topic:  For the site I'm building, I'll need to access
> user stats and log user input rather frequently.  The amount of data
> to be stored for any given user is not significant, but there will be
> a lot of accessing of database files.
>
> I have no experience with SQL, but I do have experience using shelve
> for persistent storage of data.  I'd like to get some exposure to
> SQL, but I'm not sure if it is overkill for what I'm doing -
> considerations would be the learning curve of SQL and whether or not
> continual access of and writing to a db file using 'shelve' would be
> wise.

  In the context of learning, overkill is a concept for people with
tight deadlines or deficient hardware.  Are you afflicted by either of
those?  What you'll probably find is that if your application is even
mildly complex, you'll end up using either a relational or an
object-oriented database by the time you really get underway.  The
only question is whether that database system will be a crude, ad hoc
affair that grew like mold on the crust of your application, or a
sturdy foundation stone already sculpted by the relentless sandstorm
of widely varied requirements and years of trial and error.

  Regardless of the specifics of your current project, learning the
relational approach to data modelling (and also learning SQL in the
process) is worth your time.  Even if you eventually come to prefer
object-oriented databases (such as ZODB) or some yet-to-be-discovered
paradigm, exposure to relational data modelling and to SQL will serve
you well, both as a point of reference and because relational
databases are pivotal to a great many existing applications.

  What must be stressed is that SQL itself is just the dominant Query
Language used to define and manipulate data stored according to the
relational model.  I would argue that the hardest aspect of using
relational databases effectively is learning to create robust
relational designs, not learning SQL itself.  In my opinion, that's
not because the relational model is dreadfully esoteric (it isn't),
but because it's so tempting to just dash off a few SQL statements
while subscribing to the popular myth that relational databases are
merely glorified spreadsheets, and call the resulting mess a database
application.

  But don't be put off by my pedantic chatter.  At worst, you'll paint
yourself into a corner a few times before the crucial concepts of
relational modelling emerge from the fog.  Whether you approach it the
awkward way (SQL-->relational model) or the coherent way (relational
model-->SQL), you'll eventually learn a profoundly useful approach to
data manipulation.

  Despite all my yapping about the relational model, I don't know of
an excellent tutorial for it on the web (maybe
http://escher.cs.ucdavis.edu/ecs189f/lecnotes/lect20.htm or
http://www.islandnet.com/~tmc/html/articles/datamodl.htm or
http://www.databasejournal.com/sqletc/article.php/1428511 ).

  As for SQL itself, I think O'Reilly's AboutSQL column is pretty good
(start at the bottom of the page):
http://www.onlamp.com/pub/q/aboutSQL


  As for specific database software, I recommend starting with either:
- Gadfly ( http://gadfly.sourceforge.net/ ) via its native library
- SQLite ( http://www.hwaci.com/sw/sqlite/ ) via the PySQLite module (
http://pysqlite.sourceforge.net/ )
- or, if you want to start a little higher on the
complexity/capability curve, Firebird ( http://firebirdsql.org ) via
the KInterbasDB module ( http://kinterbasdb.sourceforge.net/ ), in
combination with the IBOConsole IDE (
http://www.mengoni.it/downloads.html ).

  I recommend against starting with MySQL, not primarily because of
properties intrinsic to the MySQL software itself, but because so many
members of the MySQL community have convinced themselves--and seem
desperate to convince others--that MySQL is the ultimate relational
database, whereas in truth it's critically deficient in some areas,
like a hamburger without the bun, pickles, lettuce, and catsup. 
Gadfly and SQLite aren't designed to be and don't claim to be
heavyweights; their communities won't brand as heretical an honest
appraisal of their limitations.  Due to my personal involvement with
KInterbasDB (a Firebird module), I have a conflict of interest in
critiquing Firebird, but I will say that I regard Firebird as a well
balanced compromise between the vast feature set and complexity of
high-end commercial databases, and entry-level ones such as Gadfly and
SQLite.  Just as Perl 6 is leaning in a Pythonic direction, MySQL is
trying to become what Firebird already is.  I fail to mention
PostgreSQL only because its lack of portability has precluded my
significant involvement with it; SAP DB because I consider it
inappropriate as an introductory learning tool.

  Now, where's that flame-proof suit?  I've dissed Perl and MySQL; why
not Apache and Linux while I'm at it ;)