Django + SQL Alchemy
Paul Davis
*not* replaced django's ORM.
But I do have a small project that gives proof of concept for
automatically integrating SQL Alchemy ORM into the Django ORM. I've
been kicking the idea around for a bit, and after going through the
code I felt it was fairly hopeless to actually try and replace the
entire Django ORM with an sqlalchemy back end.
Major key points: (remember this is PoC so suggestions are indeed welcome)
SA = SQLAlchemy
1. SA creates its Table objects using reflection
2. SA ORM classes are dynamically created
3. SA ORM classes are directly importable via tranquil.models.*
4. I haven't got the ability to fully configure the setup of SA
tables, ORM classes, and mappers yet. This is planned, but I felt like
it wasn't important for PoC
5. Currently, SA reflection happens every time the server starts.
Obviously, caching this is on the ToDo list
6. All of this is provided by an external app with no patches to Django itself.
Project Page: http://code.google.com/p/tranquil/
Example views.py:
http://tranquil.googlecode.com/svn/trunk/proj_name/app_name/views.py
Basic usage:
Assuming tranquil is in the installed apps, just do:
from tranquil.models.app_name import ModelName
ModelName is the SQLAlchemy ORM object which means its pure SQLAlchemy
from there.
Any feedback is welcome. If there's enough interest I'll move forward
with filling out the corners.
Thanks,
Paul Davis
Ben Ford
I'd love to have time to play with this, but I don't at the moment. It's something I've been interested in for a while though, and also something that I want to take a long hard look at pretty soon. Have you seen: http://jeroen.concept-q.com/projects/djangoalchemy/? Maybe something you could look at in the spirit of DRY if you're both doing the same thing.
How do you feel about mapping Tables directly to django Models...? I.E:
db_table = <your_model>._meta.db_table
sastring = <connection string for your db> (I have a function to build this from settings.py )
engine = create_engine(sastring)
sa_table = Table(db_table, engine, autoload=True)
mapper(<your_model>, sa_table)
You can now use <your_model>.c to get access to all of the SA stuff, and do funky things with joins and what-not that you can't with django ORM. But all of the objects that the resulting query returns are Model instances so all of your django functionality (admin, etc) still works. This is kinda off the top of my head, and I've only used it superficially with no testing at all, but on first inspection it appears to work. what do you think?
Ben
Paul Davis
I contemplated something like this, but I assumed that field
properties would clash. For instance, after the call to mapper(...)
you would no longer have access to Django ORM fields with the
Model.field syntax (as they would be replaced by SA's). So what I did
was this:
To get Django models:
from django.contrib.auth.models import User
To get SA models:
from tranquil.models.auth import User
The other thing I wasn't so sure about in combining the two is if that
would require each page view to grab 2 connections to the db. Once for
the Django ORM and once for the SA ORM. There's a lot of implicit
stuff I haven't totally groked (specifically, if the connection
grabbing is lazy or not)
Anyway, I'm gonna take a closer look at the project the Jeroen guy is
working on to see how it works and if there's anything I can use.
Judging by reading through the description at the URL you posted it
seems quite a bit more clunky to use, but he does do some of the
things I had initially planned on doing.
Thanks for the heads on the project. I hadn't heard of it.
Paul Davis
SmileyChris
On Oct 6, 5:24 am, "Paul Davis" <paul.joseph.da...@gmail.com> wrote:
> Ben,
>
> I contemplated something like this, but I assumed that field
> properties would clash. For instance, after the call to mapper(...)
> you would no longer have access to Django ORM fields with the
> Model.field syntax (as they would be replaced by SA's).
Could you use the post_init signal to attach a LazySAModel object as
an SA (or something) property of all newly created Django models?
Paul Davis
At the moment, I'm using the class_prepared signal to gather all
loaded models. Theoretically I could use this to attach an SA
attribute.
Are there use cases out there where you'd want to use *both* the
Django and SA ORM's side by side? To me it makes more sense to import
one or the other depending on what you plan on doing. For instance all
of my code will use just the SA models, but the admin will still use
the Django models.
Paul
Ben Ford
I don't think it would entail two db lookups, but like I said I haven't tested it. SA will be instantiating a django Model, but it will be filling in it's data from the database, so there should be no lookup required by django.
It does overwrite the field as far as I know, but that shouldn't matter in because all you get from a field is that field's value from the database anyway. The django ORM is still usable... So you still get the ability to do reverse related lookups and such, even from objects in a list returned by SA: i.e. if you have the following
class Alarm(models.Model):
....
ran_obj = models.ForeignKey(RANObject, related_name='alarms')
.....
and you get a list of RANObjects from some complicated lookup that you use SA for, then you should still be able to do:
ran_ob.alarms.all()
This is one case where I could envisage using SA and django ORM side by side.
That way you won't have to mess with any of your views/admin/django core functionality, as the underlying objects are still just django models.
What do you think?
Ben
Regards,
Ben Ford
ben.f...@gmail.com
+6281317958862
Norjee
As you can see my version of mixing sqlalchemy and django is a bit
dormant. At the very least it needs some changes to work with
sqlalchemy .4. Besides my code is a bit sloppy, as much of the
translation django syntax -> sqlalchemy syntax was trial and error.
What my solution basically does is translate the django mapping to
sqlalchemy mapping, give the django model an inner class which will
then be the sqlalchemy model. http://jeroen.concept-q.com/projects/djangoalchemy/readme.txt
explains it nicely.
For now this isn't too exiting, however, it means you have only one
set of model to maintain, all the relations and custom names of fields
will be the same in the sqlalchemy model. Moreover, it should be
possible to use custom methods from the django model in the sqlalchemy
model. It is my guess that with little effort it should be possible to
emit the signals django models send as well..
One thing that does not work yet are generic relations. Those seem
pretty django specific.
One more advanced thing i would REALLY love to see is some form of
lazy object initialisation. Both the django and sqlalchemy ORM
immediately create an object, while often the basic dictionary of rows
would suffice (when you query for the last 10 posts, you really don't
need the post object, only the rows with post id, title, and time.
Only when you start manipulating a single post, you need the object to
call, for example, the save method. It seems to me that once an
unknown method is being called, it is a nice time to "upgrade" the
simple dictionary to a full blown object with save method..) However,
i'm a bit clueless how to implement this ;)
Anyway, good luck with the django + sqlalchemy solution. "Steal" as
many ideas you want. The development of my thingy will probably go
slow, as the main reason for publishing such an alpha status solution
was to get others to think on getting sqlalchemy to work inside
django. (Sadly (or fortunately as it means the django ORM _is_
sufficient for all) there seems not much interest in blending
sqlalchemy in django, so don't expect lots of (external) need for your
project)
Paul Davis
If I understand you right, you're arguing to merge the SA and Django
models so that we can use Django's reverse lookups.
SA supports reverse lookups and I'm just getting started on
implementing these automagically by introspecting the django models.
I'm still not convinced that overriding Django model fields with SA
model fields is a good idea. There seems to be quite a bit of code in
the Django fields for dealing with forms and what not. I'm assuming
that this is an artifact of oldfields and will be removed eventually,
but until then all code depending on oldfields would choke. Something
I'm trying to avoid.
The only reasons that I can come up with supporting using Django
models as a base would be to try and automatically use the extra
methods defined for a model or *maybe* using the custom managers.
Both of these 'features' seem like they would present an endless can
of worms because of all the implicit relationships between the django
models, fields, and managers.
At the moment, I'm trying to keep things as straightforward as
possible so its easy to switch back and forth between the two model
styles.
Anyway, thanks for your thoughts. Hopefully I can introspect all the
relationships and have back references working automatically before I
go home tonight.
Paul
Paul Davis
You touched on a point I've been trying to figure out for a bit. Using
SA is inevitably going to lead to having people maintaining two sets
of models. This was a bit of a concern for me.
My goal here is to make the SA models as minimal as possible. The end
idea will be to let people specify SA models just to override what
would be automatically created by my app. Ie, my code did something
wrong. So they just specify it by hand and all is good (and they
submit a bug, and I fix it, and then they're back to no extra SA
code).
Granted I think there might end up being bits that I won't be able to
introspect from the DB and/or the django models. (legacy databases
with some funky setup).
As to returning data as a list of dicts or some such, this isn't a
feature that I'm going to worry about. I can't say that I've done any
testing on this, but if you're worried about the overhead of creating
objects for each row, then maybe you should look into using the raw
DB-API to get your data.
Paul Davis
Ben Ford
Model field values are just the value of that field in the database, *not* the field itself. The fields themselves are atored in _meta.fields and _meta.many_to_many. The field does all of it's work by looking at the model, i.e. getattr(model_instance, field_name).
So it makes no difference if you change the value. Therefore when a model is created, it should make no difference if the field value are set by django or SA... as long as they are the same then there's no problem.
This is a bit of a superficial explanation, as of course it makes no allowance for the case where the db_column has been set in the field and is therefore different to the attribute name applied to the model. However I think the theory is sound.
Ben
Paul Davis
I've got this working so that Django models are automatically
introspected to create all the same backrefs for use in SA that you
would have access to in Django.
SA Table's and Mapper's can be overridden in your app so you can add
custom methods to your models.
Due to popular demand, you can use your Django models as the ORM objects.
I'm in the process of filling out some documentation on the wiki. If
anyone has any comments or suggestions I'd like to hear them.
All in all, adding SQLAlchemy to your Django project takes three lines of code.
Paul Davis