Basic gadfly question

[Alex Martelli]

"W. Jarrett Campbell" <jarrett at engineer.com> wrote in message
news:9e6V6.15027$lq1.4843531 at typhoon.austin.rr.com...
    ...
> I'm new to the world of Python, Databases, and SQL but it has been a fun
> adventure so far!
>
> I have basic question regarding the Gadfly package.  I'm working with the
> sample database generated by the gftest.py module.  I'm trying to execute
an
> SQL statement such as:
>
> INSERT INTO frequents (DRINKER, BAR) values ('Jarrett', 'cheers')
>
> When I do this I get an error back saying that I did not define a value
for
> the column 'PERWEEK'
>
> My questions are these?  Do you have to set all values in the insert
> statement or is there some way around this?  Is this bad database
> programming form to want to set only some fields?  Is this related to
gadfly
> not supporting Null values?

Yes, it's related to Nulls not being supported.  NULL means "value
not known".  If a column is constrained to NOT NULL, and in Gadfly
in practice they all are (although you're not allowed to SAY so in
the CREATE TABLE statement:-), this means you HAVE to give a
value for it on each insert.  It's absolutely NOT bad DB design to
desire NULL fields (that's why they're there... Dr Codd well knew
whereof he spoke!-) -- it's just that you can't have them in Gadfly.


> When using the ODBC package in the win32all distribution, I can achieve
this
> functionality with MS Access databases.  I wanted to stick with gadfly to
> remain platform independent and not require my users to purchase Access.
> Any advice?

The Jet engine (which is probably what you mean by the widely but
incorrectly used name "MS Access database" -- Access is just a front
end, able to interface to many different engines -- MS marketing was
first guilty of these confusions, many years ago) is freely redistributable
as part of your application.  So are MUCH better engines -- if you're
keen on Microsoft products, I suggest MSDE, the engine underlying
MS SQL Server -- you cannot distribute the GUI tools for various
admin tasks etc, but the engine and some basic command line tools
are OK (and you can use ODBC, though Lemburg's mxODBC is no
doubt preferable to the odbc package currently in the win32all pack).

BTW, "win32all's" odbc.cpp has some horrid bugs, although I guess
Hammond's build process may make them disappear (I do not have
a sufficient understanding of that build process to say for sure!).  As
distributed, for example, it builds objects with PyObject_NEW (yes,
the _macro_...), then frees them with PyMem_DEL (the macro, again).
I don't think you're supposed to do that (I was rightly admonished
for it when I did it in a toy source I posted:-), and sure enough it
"reliably" crashes on my machine.  Fortunately, it's easy enough to
fix, the sources are still marked as public domain ("Donated to the
Python community by EShop"), and distutils (what a GREAT idea!)
make it trivial to package the thing up, so I did although unsure of
how to feed it back to win32all (not understanding the basis of the
special build process makes that a bit iffy:-).  Unfortunately I still
have a crash in some error-cases (apparently only when using the
thing with Ocelot's ODBC -- Ocelot is an excellent, tiny, free SQL-92
standard-with-SQL99-extensions database -- not open source, nor
redistributable, one has to go to their site and download it, I think
their business model is selling books, training and consulting -- but
I don't think the bug is with Ocelot, as other ODBC clients works
happily with it:-) -- I keep telling myself I'll debug it well one of
these days, but round tuits supplies are scarce...

Anyway... this is all by the by, because...:

This won't let you be cross-platform, admittedly.  Gadfly is quite
attractive that way.  Besides the NULL's, there's the fact that it
uses the now-obsoleted regex and regsub modules, so it spews
warnings under Python 2.1 -- somebody should port it...:-).

Also attractive is MySQL, though, particularly now that it FINALLY
supports some form of transactions; and PostgresSQL (maybe
overkill for your needs).  Lots of people seem keen to donate DB's,
at least to developers (SAP, for example, seems to be donating
it freely; Borland, with Interbase; Oracle, and IBM with DB/2,
with restrictions...).  But that still doesn't solve the problem of
finding ONE simple, lightweight, usable, standard-ish SQL DB
for free, unencumbered cross-platform redistribution in a
package with one's programs using it...


Alex