Way to represent SQL queries as objects in Django/Python

Question

The following query:

SELECT L1.task_id FROM task_log L1
  LEFT JOIN task_log L2 ON L1.started BETWEEN L2.started AND L2.ended
  WHERE L2.task_id IS NULL;

Simply reads all task logs that were started when no other tasks were running. An abstraction in PHP/ZF2 looks like this:

$db->select(array('L1' => 'task_log'))
   ->columns('task_id')
   ->join(array('L2' => 'task_log'), "L1.started BETWEEN L2.started AND L2.ended", array(), 'left');

I was looking for something similar in Django, or at least python. As per this discussion, it is (understandably) out of question to build this in django models. Are there any other alternatives in python/django?

Yes, it does not. But my question is about any way to generate SQL in python/django, — András Gyömrey, Mar 15 '17 at 14:40
Do you want to convert Django ORM's to raw SQL or pass raw SQL to Django ORM? — nik_m, Mar 15 '17 at 14:45
Actually, _Django ORM_ does a fantastic job until you have to join the table with itself with a custom `ON` condition. That's the reason i have no problem using a completely different framework. Right now my only option is to write the complete query in _SQL_. — András Gyömrey, Mar 15 '17 at 14:52
For the 1st use the [`extra()`](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.query.QuerySet.extra). For the 2nd use [this](http://stackoverflow.com/questions/3748295/getting-the-sql-from-a-django-queryset#answer-3748307) — nik_m, Mar 15 '17 at 14:53
Ok if I understand you correctly, you mean i should use `extra` for the `left join` with the custom condition. The second link is to convert to _SQL_. To be clear, if I can express the query using django models, there's no need to convert it to _SQL_. — András Gyömrey, Mar 15 '17 at 14:57
Of course. If you can express the query using the ORM there's no need to convert ti to SQL! On the other hand when ORM is not enough for you, use the [`extra`](https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.query.QuerySet.extra). — nik_m, Mar 15 '17 at 15:00
This would seem interesting, but as far as i know, this example is not possible to implement with `extra`. That's the reason I ask — András Gyömrey, Mar 16 '17 at 16:18

score 0 · Answer 1 · answered Mar 15 '17 at 17:15

0

There seem to be these options:

(A) RawSQL with string templates and maybe some generic model field reflection code to make it a bit more generic.

(B) Django's Expression API which might allow you to re-use some of the Django ORM stuff and only extend it in order to make SELF JOINs work. Though I'm wondering why there wouldn't be some Django module already. Maybe it's too rare of a use case.

answered Mar 15 '17 at 17:15

Risadinha

16,058
2
88
91

Thanks, but exactly `RawSQL` is what i want to stay away from. It wasn't also exactly easy to come up with this example, but when you encounter it, then it's really hard not to notice the limitation with _Django models_. The problem with the _expression API_ is that it isn't designed to contain a whole query. – András Gyömrey Mar 16 '17 at 16:21

score 0 · Accepted Answer · answered Mar 28 '17 at 15:05

0

Looking up i found this: http://docs.sqlalchemy.org/en/latest/core/tutorial.html which is exactly what i needed.

answered Mar 28 '17 at 15:05

András Gyömrey

1,770
1
15
36

Way to represent SQL queries as objects in Django/Python

2 Answers2