Integrating Django and SQLAlchemy
Adrian Holovaty
Spawned by the recent discussion of the big picture of Python Web
frameworks, we've decided to start a new branch of Django development
that uses SQLAlchemy (http://www.sqlalchemy.org/) as the underlying
database library.
Robin Munn, author of an excellent SQLAlchemy tutorial
(http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html) and heavy
contributor to Django several months ago, has agreed to be the lead
developer on this branch. I'm sure he'll bring up any
issues/implementation questions on this mailing list, and if you want
to help out in some fashion, make yourself known!
Here are some notes about implementation:
* The Django database API would not change, and the SQLAlchemy backend
would be *optional*. The point of the SQLAlchemy backend would be to
expose the underlying SQLAlchemy API if you need to do something truly
complicated, rather than having to fall into raw SQL.
* At the moment, a Django model inherits from django.db.models.Model,
and convention is to put "from django.db import models" at the top of
your models.py file. I think it'd be smoothest (and really cool) if
people were able to switch that to "from django.contrib.sqlalchemy.db
import models" and not have to change any code. In other words,
SQLAlchemy models would be instances of
django.contrib.sqlalchemy.db.models.Model rather than
django.db.models.Model.
* As that implies, this would be an *optional* add-on -- the existing
model machinery would stay put. I.e., people could still use Django
without SQLAlchemy, and that would be the default. I wouldn't rule out
a full migration to SQLAlchemy (i.e., making it default) in the
future, but we'd rather not add a dependency at this point.
* A model object's "_meta" attribute should be preserved. (This is the
metadata about the model.) Although it starts with an underscore,
there's enough code out there using it that makes it a de-facto
standard part of the database API.
* As for refactoring the existing code to add hooks for SQLAlchemy --
that's fine as long as it's not too much of an added overhead for
non-SQLAlchemy users.
* This branch will be called "sqlalchemy" in the Django Subversion repository.
Adrian
--
Adrian Holovaty
holovaty.com | djangoproject.com
Robin Munn
>
> Hi all,
>
> Spawned by the recent discussion of the big picture of Python Web
> frameworks, we've decided to start a new branch of Django development
> that uses SQLAlchemy (http://www.sqlalchemy.org/) as the underlying
> database library.
>
> Robin Munn, author of an excellent SQLAlchemy tutorial
> (http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html) and heavy
> contributor to Django several months ago, has agreed to be the lead
> developer on this branch. I'm sure he'll bring up any
> issues/implementation questions on this mailing list, and if you want
> to help out in some fashion, make yourself known!
*waves*
Hi all!
As Adrian said, if you have any suggestions to make, even if it's as
simple as what you'd like the API to look like, now's the time to make
them. I'm only *just* getting started on this, and right now I'm just
plunging into a refresher course on what's changed in Django since I
last looked at it -- which was more like a year ago than the "several
months ago" that Adrian just said. :-)
The notes on implementation that Adrian posted pretty much match what
I'm thinking at this point. The plan is to make this 100% API
compatible (if possible -- you never know what will turn up once you
start implementing some idea), so that existing code doesn't need to
change. But if you want access to the underlying SQLAlchemy objects to
do something like a nine-table query involving five LEFT OUTER JOINs
and two subselects, well, you should be able to do that as well.
One quick request: I don't think I'm going to have time to read the
entire django-developers list, since I do have other commitments (such
as my day job, which isn't Django-related). So if you have a
suggestion about the SQLAlchemy branch, I'd appreciate it if you put
the word "SQLAlchemy" in the subject line somewhere. I'll have a GMail
filter set up to catch those, and I *will* read all the SQLAlchemy
threads in django-developers. And probably even answer them! ;-)
I also don't think I'll be able to commit to being on IRC much, or
even at all. That may change in the future, but I make no promises on
that score. If you want to contribute, or even just make requests, the
best way to get in touch with me will be via the django-developers
list. (And make sure "SQLAlchemy" is in the subject line so that I see
it).
I welcome your comments!
--
Robin Munn
Robin...@gmail.com
GPG key 0xD6497014
gabor
>
> The notes on implementation that Adrian posted pretty much match what
> I'm thinking at this point. The plan is to make this 100% API
> compatible (if possible -- you never know what will turn up once you
> start implementing some idea), so that existing code doesn't need to
> change. But if you want access to the underlying SQLAlchemy objects to
> do something like a nine-table query involving five LEFT OUTER JOINs
> and two subselects, well, you should be able to do that as well.
hi,
this is great news...
could you show some example code how would it work?
like....
without sqlalchemy you would have to do this in raw sql, but with
django+sqlalchemy, you could do that....
things like that?
because i checked the sqlalchemy tutorial, and i see that it is a
powerful framework... i just don't currently see how it would interact
with django....
please don't take this wrong, i very much welcome this django/sqlalchemy
integration... i'm just curious how exactly would it work... how will i
be able to go "deeper" (from django-level into sqlalchemy level) when
needed...
or ...well..maybe it's too early to ask questions like this :)
thanks,
gabor
JP
One question comes to mind first, because I am nothing if not
self-absorbed. :) I wonder how much of the multi-db branch I have been
working on will be made irrelevant by this. Any thoughts on how the
sqlalchemy backend might support connecting different models to
different engines? I'd be more than happy to suggest parts of multi-db
to steal or adapt, or help with the stealing and adapting, if you're
planning to include something like per-model engines in the
implementation.
More questions: Are you planning on building something like
ActiveMapper, or using ActiveMapper, or rolling up a whole new ORM
layer on top of the sqlalchemy core? If the last, I have some
play-around code (from sqlalchemy around 0.1, though) that I will
happily donate in case there is anything stealable or adaptable or
so-horrifying-you-know-never-to-repeat-it in there.
Great news.
JP
David Elias
Robin Munn wrote:
> The notes on implementation that Adrian posted pretty much match what
> I'm thinking at this point. The plan is to make this 100% API
> compatible (if possible -- you never know what will turn up once you
> start implementing some idea), so that existing code doesn't need to
> change. But if you want access to the underlying SQLAlchemy objects to
> do something like a nine-table query involving five LEFT OUTER JOINs
> and two subselects, well, you should be able to do that as well.
Indeed great news!!
At work i am trying to build the Django ORM on the top of SQLAlchemy
core. I've tried on the top of the ORM but it was overkill, but i could
had done something wrong. I was getting about ~10 request per second
oposed to ~50 request per second with firebird.
Are you planning to build the integration on the top of the SQLAlchemy
ORM?
Robin Munn
>
> Robin Munn wrote:
> >
> > The notes on implementation that Adrian posted pretty much match what
> > I'm thinking at this point. The plan is to make this 100% API
> > compatible (if possible -- you never know what will turn up once you
> > start implementing some idea), so that existing code doesn't need to
> > change. But if you want access to the underlying SQLAlchemy objects to
> > do something like a nine-table query involving five LEFT OUTER JOINs
> > and two subselects, well, you should be able to do that as well.
>
> hi,
>
> this is great news...
>
> could you show some example code how would it work?
> like....
> without sqlalchemy you would have to do this in raw sql, but with
> django+sqlalchemy, you could do that....
> things like that?
Not yet -- I don't have anything yet. It's only been a few days since
I even started thinking about it when Adrian contacted me. Once I have
the ideas fleshed out a little bit, I'll be able to show some
examples.
> because i checked the sqlalchemy tutorial, and i see that it is a
> powerful framework... i just don't currently see how it would interact
> with django....
>
>
> please don't take this wrong, i very much welcome this django/sqlalchemy
> integration... i'm just curious how exactly would it work... how will i
> be able to go "deeper" (from django-level into sqlalchemy level) when
> needed...
Basically, your code won't change much, and you'll use the Django API
as-is, except inheriting from django.contrib.sqlalchemy.db.models
instead of django.db.models. Then when you need to get at the
SQLAlchemy Table object, you'd do something like
Story._sa_table.some_method().
At least that's the current plan. It may change as I implement this.
> or ...well..maybe it's too early to ask questions like this :)
>
> thanks,
> gabor
It's never too early to ask questions. Sometimes it's too early to get
detailed answers, though. :-)
Robin Munn
>
> This is great news!
>
> One question comes to mind first, because I am nothing if not
> self-absorbed. :) I wonder how much of the multi-db branch I have been
> working on will be made irrelevant by this. Any thoughts on how the
> sqlalchemy backend might support connecting different models to
> different engines? I'd be more than happy to suggest parts of multi-db
> to steal or adapt, or help with the stealing and adapting, if you're
> planning to include something like per-model engines in the
> implementation.
SQLAlchemy already has a DynamicMetaData object that can connect model
objects to different database engines; I currently plan to use that.
I'll definitely take a look at the multi-db code and would welcome any
help you might want to offer.
Whether the multi-db branch becomes irrelevant or not will entirely
depend on adoption, I think. Maybe most people won't want to use the
SQLAlchemy branch, and it will wither and die from lack of interest,
while the multi-db branch will be widely used. Or maybe people will
jump on the SQLAlchemy branch like it's the best thing since sliced
bread, and it will become the default. It's far too early to tell.
Basically, don't stop work on multi-db just because I'm starting this
branch. I might hit a brick wall and not be able to complete it, or I
might get hit by a bus tomorrow, or whatever -- you never know what's
going to happen. I'd welcome any ideas or code you'd like to share,
though.
> More questions: Are you planning on building something like
> ActiveMapper, or using ActiveMapper, or rolling up a whole new ORM
> layer on top of the sqlalchemy core? If the last, I have some
> play-around code (from sqlalchemy around 0.1, though) that I will
> happily donate in case there is anything stealable or adaptable or
> so-horrifying-you-know-never-to-repeat-it in there.
Right now, I plan to use ActiveMapper -- why re-invent the wheel? I'd
be happy to look at your code, though. I like "borrowing" from other
people's work whenever I can, it means less work for me. :-) And
that's what open-source development is all about, isn't it?
Robin Munn
If I understand your question correctly, the answer is yes. Here's how
I plan to have it work:
User models
|
v
Django DB
| <-- This is the API I'll be building
v
SQLAlchemy ORM
|
v
Database (Postgres, SQLite, Firebird...)
The plan is for the user model to Django DB API won't change at all
(if possible), and I won't be touching the SQLAlchemy-to-database API
either. I'll be building that connector in the middle.
Karl Guertin
> Right now, I plan to use ActiveMapper -- why re-invent the wheel? I'd
> be happy to look at your code, though. I like "borrowing" from other
> people's work whenever I can, it means less work for me. :-) And
> that's what open-source development is all about, isn't it?
This is probably something I should submit a patch for in ActiveMapper
itself, but the biggest problem with ActiveMapper is that you can't
get at the primaryjoin and secondaryjoin attributes to do the more
involved mapping. As a result, I normally just do assign_mapper
throughout, as ActiveMapper and non-ActiveMapper models don't work
well together. AM itself is like 200 lines of code on top of the SA
core. ;]
zzzeek
Karl Guertin wrote:
> This is probably something I should submit a patch for in ActiveMapper
> itself, but the biggest problem with ActiveMapper is that you can't
> get at the primaryjoin and secondaryjoin attributes to do the more
> involved mapping. As a result, I normally just do assign_mapper
> throughout, as ActiveMapper and non-ActiveMapper models don't work
> well together. AM itself is like 200 lines of code on top of the SA
> core. ;]
starting to get off topic here, while I can believe that mixing AM and
non-AM models probably have glitches (since nobody has tried it out too
much), there shouldnt be anything deeply broken with mixing the two; an
AM object is basically a regular mapped object (via assign_mapper) with
just a slightly weird way of setting up its Table/Mapper/relationships.
While i dont have the cycles to address it single-handedly, Im sure the
issues could be resolved if we come up with some decent test cases as
well as some consistent and documented ways to get at the Table/Mapper
of an AM object. We really should have a real doc for ActiveMapper.
Robin Munn
I may come up with some halfway decent ActiveMapper tests in the
process of figuring out the Django-SQLAlchemy mapper; if I do, I'll
make sure to send patches to SQLAlchemy. Ditto documentation.
Russell Keith-Magee
Hi all,
Spawned by the recent discussion of the big picture of Python Web
frameworks, we've decided to start a new branch of Django development
that uses SQLAlchemy (http://www.sqlalchemy.org/ ) as the underlying
database library.
What impact will this change have on existing queries?
If the intention is to make the Django->SQLAlchemy model transition as easy as replacing an import statement, what is the transition plan for existing queries?
Will the model.objects.filter(...) syntax continue to work for SQLAlchemy models?
Is the intention to phase out the existing query syntax in preference for SQLAlchemy query syntax? Or will Django query syntax be maintained as a 'query Lite' syntax for simple problems?
Russ Magee %-)
JP
> On 8/30/06, JP <jpel...@gmail.com> wrote:
> >
> > This is great news!
> >
> > One question comes to mind first, because I am nothing if not
> > self-absorbed. :) I wonder how much of the multi-db branch I have been
> > working on will be made irrelevant by this. Any thoughts on how the
> > sqlalchemy backend might support connecting different models to
> > different engines? I'd be more than happy to suggest parts of multi-db
> > to steal or adapt, or help with the stealing and adapting, if you're
> > planning to include something like per-model engines in the
> > implementation.
>
> SQLAlchemy already has a DynamicMetaData object that can connect model
> objects to different database engines; I currently plan to use that.
> I'll definitely take a look at the multi-db code and would welcome any
> help you might want to offer.
That makes sense. The tricky part will be assigning different groups of
tables to different DynamicMetaData instances. For instance, say you
have an application that has to connect to two different legacy
databases, one for ... uh ... zoo animals and the other, plants in a
botantic garden. The multi-db branch handles this via a setting, like:
OTHER_DATABASES = {
'zoo': { 'DATABASE_ENGINE': ..,
'DATABASE_NAME': 'legacy_zoo',
'MODELS': [ 'zoo' ] # this is an app_label
},
'botany': { 'DATABASE_ENGINE': ...,
'DATABASE_NAME': 'legacy_plants',
'MODELS': ['plants']
}}
And then mediates database access through a property on each model's
default manager. The particular config syntax is secondary to that --
the important change is that all of a model's db access goes through a
property (however indirectly) of the model, and not a single global
connection.
> Basically, don't stop work on multi-db just because I'm starting this
> branch. I might hit a brick wall and not be able to complete it, or I
> might get hit by a bus tomorrow, or whatever -- you never know what's
> going to happen. I'd welcome any ideas or code you'd like to share,
> though.
Multi-db is pretty well done, aside from the never-ending task of
merging in trunk (which just got a whole lot harder as of r3661,
unfortunately). Many of the changes in there don't really apply, since
they are pretty specific to the django stock ORM. But there are at
least three that do, I think: moving the schema manipulation sql out of
django.core.management (extra challenging to preserve the nice terminal
colors in this case), adding the Model.objects.db property for access
to the db metadata for a model, and adding methods to Manager for
manipulating the schema of the attached model (create table, load
initial data, etc).
> I like "borrowing" from other
> people's work whenever I can, it means less work for me. :-) And
> that's what open-source development is all about, isn't it?
Indeed. Laziness is a virtue, someone once said. :) I managed to clean
up my old experiment enough that the tests pass under sqlalchemy 0.2.7.
You can grab a copy here:
http://somethingaboutorange.com/mrl/projects/models.tgz
The one thing I'd advocate stealing is making the model properties
descriptors that delegate to the columns of the sqlalchemy table (or
properties of the mapper). This allows a really nice query syntax,
like: Animal.objects.filter(Environment.last_cleaned < '2001-01-01'),
which I think is a lot better than (say)
Animal.objects.filter(Environment._meta.c.last_cleaned ... ) and
provides a natural place to put and access column metadata.
JP
JP
> SQLAlchemy Table object, you'd do something like
> Story._sa_table.some_method().
How about `Story._meta.table` or `Story._meta.c` or .columns or
something, to avoid littering the model's namespace with more
properties?
JP
Adrian Holovaty
> What impact will this change have on existing queries?
>
> If the intention is to make the Django->SQLAlchemy model transition as easy
> as replacing an import statement, what is the transition plan for existing
> queries?
>
> Will the model.objects.filter(...) syntax continue to work for SQLAlchemy
> models?
>
> Is the intention to phase out the existing query syntax in preference for
> SQLAlchemy query syntax? Or will Django query syntax be maintained as a
> 'query Lite' syntax for simple problems?
The goal is that this will have *zero* effect on existing queries. The
Django query syntax will remain exactly the same, and the database API
will stay the same. The only difference is that the SQLAlchemy backend
will have *extra* functionality -- namely, that people will be able to
fall into SQLAlchemy syntax if they want to.
Jay Parlar
> Django query syntax will remain exactly the same, and the database API
> will stay the same. The only difference is that the SQLAlchemy backend
> will have *extra* functionality -- namely, that people will be able to
> fall into SQLAlchemy syntax if they want to.
>
A question then: Once the SQLAlchemy stuff is in and solid, will there
be any reason *not* to use it?
Jay P.
JP
The Django query syntax is a great way to map a flattened dict to a
query. A flattened dict like, say, GET or POST data. ;)
JP
JP
after 10 pm, too many brain cells are asleep and the remainder can't
handle typing and thinking at the same time.
The reason not to use it I guess would be that you already have a
working app and you don't need it.
JP
Adrian Holovaty
> A question then: Once the SQLAlchemy stuff is in and solid, will there
> be any reason *not* to use it?
We'll decide that when we get there, but I'm inclined to answer your
question with a single word: "Simplicity." Django will continue to
work out of the box, with no dependencies.
Christopher Lenz
> On 8/30/06, Jay Parlar <par...@gmail.com> wrote:
>> A question then: Once the SQLAlchemy stuff is in and solid, will
>> there
>> be any reason *not* to use it?
>
> We'll decide that when we get there, but I'm inclined to answer your
> question with a single word: "Simplicity." Django will continue to
> work out of the box, with no dependencies.
"No dependencies" is just one part of simplicity :-)
Moving Django to SQLAlchemy wholesale would get rid of (or at least
simplify) much of the DB code in Django. Adding SQLAlchemy support as
an option is likely to increase the complexity of the code, and you
basically have two different code paths to the database... patching
and testing gets harder, understanding the code gets harder, writing
and understanding the documentation gets harder.
It's definitely a good idea to develop SQLAlchemy support as an
optional alternative to the built-in ORM for now. But if the project
works out, replacing the built-in DB code with SQLAlchemy is going to
make a *lot* of sense IMO.
Cheers,
Chris
--
Christopher Lenz
cmlenz at gmx.de
http://www.cmlenz.net/
zzzeek
> We'll decide that when we get there, but I'm inclined to answer your
> question with a single word: "Simplicity." Django will continue to
> work out of the box, with no dependencies.
you know, I get the "no dependencies" thing, I really have a similar
inclination to produce packages that arent going to create a headache
about downloading other packages, getting through firewalls, and stuff
like that. Even SA itself has had a little bit of criticism for this,
since its "sql construction" and "orm" APIs, of which the former has no
knowledge of the latter, are still all delivered in one package.
but if youre talking about a "web framework" that has functionality
spanning beyond a focused task, I think trying to keep it "all in one"
package is going to ultimately place a burden on its structure and
development. The standard way to deal with dependencies is to use
package distribution tools...Ruby has gems, Perl has CPAN.
Python has the cheeseshop and easy_install. Unfortunately the
cheeseshop/easy_install combination remains pretty klunky, not easy to
understand, and its been my experience that if you have any notions
that are in disagreement with its designer (such as the PYTHONPATH
variable maintaining its normal behavior), even if others agree with
you, youre out of luck. So its very understandable Django wants to
stay out of all that.
However, Python would really benefit from cheeseshop/easy_install
getting a lot more eyes and users on it, and ultimately getting the
improvements needed to make it as easy to use as CPAN or better
(probably better). To that end, it would be nice if Django and its
very large userbase could begin to embrace it more. Guido has said he
prefers django because its developers "get open source development".
Well i think priority #1 of open source development is to fully embrace
the platform, warts and all, and get some eyes on the issues so that
they can be improved (or, introduce another distutils/cheeseshop
implementation thats easier to use). The distutils-SIG list is usually
very low-traffic. For something this important to Python's survival, I
would think the whole community would be on that list bugreporting,
patching and complaining all day until Python has the easiest to use
and most world-class dependency-distribution system. Django is in a
great position to crack open some of the ivory towers out there, and i
think its shortsighted to ignore the tremendous usefulness (and
importance) of package-based software distribution.
just my rant for the day.
Brantley Harris
would be rendered useless if Django eventually did adopt SQLAlchemy,
including the Schema Evolution and the multiple DB branch.
Also this puts into question the philosophy of the full-stack, and if
you really want to be tied to SQLAlchemy... Both questions that need
to be answered later on down the road, I'm sure, but still something
to think about.
On 8/30/06, Adrian Holovaty <holo...@gmail.com> wrote:
>
pch...@gmail.com
support" for MS SQL. Would this help hasten support for MS SQL in
Django?
Sean De La Torre
If you need MSSQL
support today, you might want to take a look this ticket:
http://code.djangoproject.com/ticket/2358. It hasn't been officially
accepted, but I've been able been able to successfully use MSSQL with Django
after applying it.
Using ticket 2358, I wrote this patch http://code.djangoproject.com/ticket/2563
(introspection functionality for MSSQL
backends). Again, this
is another patch that hasn't been accepted, but if you really need to use Django with MSSQL like me, these tickets are certainly a good
place to start.
From what I've read, pagination might not work yet. but everything else that I've tried seems to work just fine.
Sean