0

My problem is somewhat similar to what is discussed here: Django automatically create primary keys for existing database tables

But instead of simple python manage.py inspectdb I did python manage.py inspectdb --include-views. And my problem is with those classes based on views- I get Error 1054, ("Unknown column 'station_measurements_with_data.id' in 'field list'")

I've tried to simply add id = models.IntegerField(primary_key=True) to the model, but no joy.

The model class looks like this:

class StationMeasurementsWithData(models.Model):
    station_id = models.IntegerField()
    measurement_id = models.IntegerField()
    time_utc = models.DateTimeField()
    pollution_name = models.CharField(max_length=45)
    measurement_value = models.FloatField(blank=True, null=True)

    class Meta:
        managed = False  # Created from a view. Don't remove.
        db_table = 'station_measurements_with_data'

And the view data int the database looks like this:

# station_id, measurement_id, time_utc, pollution_name, measurement_value
955, 87318, 2012-01-01 02:00:00, pm2.5, 73
955, 87318, 2012-01-01 02:00:00, pm10, 308
956, 87319, 2012-01-01 02:00:00, pm2.5, 123
956, 87319, 2012-01-01 02:00:00, pm10, 152
957, 87320, 2012-01-01 02:00:00, pm2.5, 163
957, 87320, 2012-01-01 02:00:00, pm10, 198

If any column contained unique values I would just make it a primary key, but there isn't one. What should I do in this case?

Iorweth333
  • 150
  • 2
  • 9
  • `id = models.IntegerField(primary_key=True)` should have worked. Do you have the error that it gave out? Did you migrate your DB after adding the new column? – Sergio Pulgarin Aug 31 '19 at 20:22
  • Error is exactly the same. Do you mean commands ```python manage.py makemigrations``` ```python manage.py migrate ```? – Iorweth333 Aug 31 '19 at 20:47

1 Answers1

0

inspectdb management command only generates models reflecting tables and views in the database it is run against.

Adding an automatic primary key field to the model if you don’t declare it. To avoid confusion for later code readers, it’s recommended to specify all the columns from the database table you are modeling when using unmanaged models.
Reference: https://docs.djangoproject.com/en/2.2/ref/models/options/#managed

I suggest recreating the view that includes an id field.

You can imitate an auto-incrementing ids using the ROW_NUMBER window function over the result set if your database server is postgresql or find another way for your specific database server. e.g.

CREATE VIEW v_foo AS
SELECT ROW_NUMBER() OVER () AS id, [[rest fields]]  FROM foo;
Oluwafemi Sule
  • 36,144
  • 1
  • 56
  • 81
  • Altering the database- that's an idea I'd like to avoid (even though I could do it), because I'm quite happy with how it looks like now. Also: I use MySQL which supporty ROW_NUM() since version 8.0, but I use 5.7.14. I can still emulate ROW_NUM() function, but it's imho very dirty. – Iorweth333 Sep 01 '19 at 10:17
  • I don't see how you can proceed without altering the view. Django models are for managing database tables. If you find that rows are unique across your view, you can compute an id field that is an md5 hash of each column. – Oluwafemi Sule Sep 03 '19 at 20:39
  • Allright @Oluwafemi Sule, let's say I can give up on views. But I still need the data they provide. What should I do? Copy-paste SELECTs into Python? – Iorweth333 Sep 03 '19 at 21:46
  • I'm not saying to give up on views.They are not without their benefits. I'm suggesting to alter the view so that it has an id field. You can keep the models for writing the queries. The compromise being that they stay as read-only models – Oluwafemi Sule Sep 03 '19 at 22:08
  • I tried that idea with md5 and it worked BUT it also killed already not so good preformace (fetching all rows takes around 18 sec. instead of around 8). Any cheaper idea? – Iorweth333 Sep 03 '19 at 23:39