[BioSQL-l] Python ORM mapping for BioSQL

[Peter]

Nick Loman wrote:
>
>> According to the MySQL schema, taxon_name has a unique restraint but
>> does NOT have a primary key:
>
> Just to say I've had good results using the Django
> (http://www.djangoproject.com) ORM system with a BioSQL database.
>
> You can get going quite quickly using the Django introspection feature
> (configure your settings.py file and run python manage.py inspectdb to get a
> models file).
>
> However, the version of Django I use (not sure about latest) didn't support
> multi-column indexes as primary key, so I had to add another auto_increment
> column to use as a primary key.

When I started looking at web-frameworks and ORM, I didn't want to
modify a perfectly good schema (BioSQL) just to cope with a limited
tool.

I investigated Django earlier this month, and rejected it because it
doesn't yet support multi-column indices as primary keys.  They've had
an open bug on this for 3 years, with no expected date yet:
http://code.djangoproject.com/wiki/MultipleColumnPrimaryKeys
http://code.djangoproject.com/ticket/373

My impression is that Django's philosophy is that they expect you to
define your objects which then automatically defines the database
schema.  Note the title of this FAQ page refers to an existing schema
as a "legacy" database:
http://docs.djangoproject.com/en/dev/howto/legacy-databases/
If Django can cope with an existing schema, then it does look like an
excellent package, and seems well documented.

I also rejected another python ORM system, SQLObjects, on similar
grounds.  Their documentation says "SQLObject does not support primary
keys made up of multiple columns (that probably won't change)".  In
fact, they currently are even less flexible in that SQLObject requires
an *integer* primary key on each table!

This left SQLAlchemy as the remaining python ORM candidate, which
seems to cope just fine with the unmodified BioSQL schema.  Brad and I
have reported using BioSQL with SQLAlchemy successfully (within the
python web-frameworks TurboGears and Pylons respectively).

Peter