Is this a good case to deviate from Django's ORM and use raw SQL? (Unless models can be dynamically generated)

Question

So I have been using Django's ORM for everything; however, I have gotten to a part of this application where I don't think it makes sense. That is unless, models can be dynamically generated and dropped into models.py so I don't have to update it every year.

The company this application is for aggregates data from various industries. There is no overlap between them. Furthermore, the data collected from each industry can vary from year-to-year. Thus tables are broken up like:

Industry_A_15
Industry_A_16
Industry_A_17
Industry_B_15
Industry_B_16
Industry_B_17

I don't see how the ORM could work unless I added them to models.py every year (there are over a hundred columns for each). The most efficient way, I can think of, for doing it would be to do something like ./manage.py inspectdb > models.py, but that tends to not work on tables with constraints.

So would this be a good case to write in raw SQL?

It sounds like I'd want to avoid the model layer altogether really:

https://docs.djangoproject.com/en/2.0/topics/db/sql/#executing-custom-sql-directly

Then I could dynamically refer to the tables and really not have to touch it in the future. Also, the queries would be pretty basic SELECT such that I would think it should be portable between major databases. (The tables are created from a CSV upload so nothing is really being done manually to create them).

If the models' fields are going to change, I imagine you'd be facing a similar problem, but instead of having to update/create-new django models, you'd have to update/create-new SQL tables. Wouldn't that be the case? — Savir, Jan 05 '18 at 23:31
The creation of the tables is done by uploading the CSV from a FE utility. While the fields will change, the query that is being done is `SELECT * FROM table_name WHERE x = 'foo' AND y = 'bar'` which are values passed from the FE (and are fields that are constant across all of these tables). If using a model, I'd have to map out each field name for the model. Sure, I can dynamically refer to the model using the ORM, but mapping fields seems unnecessary. — cjones, Jan 05 '18 at 23:46
An explanation for the down vote and how to improve the question would be appreciated. Just comes off snobby and unconstructive otherwise. — cjones, Jan 05 '18 at 23:50
*fields that are constant across all of these tables* --> So `x` and `y` are not going to change over time? Because then maybe you don't really need to even create new models... The mapping in django is rather... Simple, after all. You can add as many new columns to your SQL as you want. As long as the mapped columns exist in the table, Django won't care about having **more** columns... — Savir, Jan 05 '18 at 23:51
Actually, I suspect that your downvoter and mine are the same. I didn't get any explanation neither. — keepAlive, Jan 05 '18 at 23:51
Also, if your db happened to be Postgres, you can add [JSON fields](https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#querying-jsonfield) Is not... great, and shouldn't be abused, but if you don't particularly care for the new changes, maybe it could be a decent place to put those changes? (kind of like almost like a "trash" or a *yeah, yeah, yeah... whatever...* **:-D** ) — Savir, Jan 05 '18 at 23:54
@BorrajaX While the having additional columns in the DB makes sense and that they don't necessarily have to be in a Django model, wouldn't I still need to make at least class model for each table even if it just contains `X` and `Y`? Can I get away with one Django model and dynamically refer to the appropriate tables? — cjones, Jan 08 '18 at 20:27

keepAlive · Accepted Answer · 2018-01-09T12:59:44.810

2

Models can be generated dynamically. What about doing, for instance

my_fields = {
    'field_one':models.CharField(max_length=22),
    'field_two':models.CharField(max_length=22),
}
MyModel  = type('MyModel', (models.Model,), my_fields)

Instead of

class MyModel(models.Model):
    field_one = models.CharField(max_length=22)
    field_two = models.CharField(max_length=22)

For details see How can I dynamically create derived classes from a base class

Even with a flexible strategy, you will still have to confirm changes in model when making migrations. Something you could do is creating one application per year, whose models would be dynamically generated.

edited Jan 09 '18 at 12:59

answered Jan 05 '18 at 23:26

keepAlive

6,369
5
24
39

*this solution works and addresses directly the question* --> Agreed... it's a pretty good way of creating models from, let's say, headers on a CSV import (as the OP detailed in the question) Dunno... Maybe it'd help if you added an example on how to do that? (I don't understand the downvote either, though) – Savir Jan 05 '18 at 23:45
@BorrajaX. Your support is appreciated: always insecuring not to know why one are downvoted. – keepAlive Jan 05 '18 at 23:56

Is this a good case to deviate from Django's ORM and use raw SQL? (Unless models can be dynamically generated)

1 Answers1