Unknown column 'ModelName.id' in 'field list'"

Question

I am using Django with mysql. I have created models.py using command inspectdb from existing database in mysql. It has a model as below :

class Participationdata(models.Model):
    userid = models.ForeignKey('Volunteer', models.DO_NOTHING, db_column='UserId')  # Field name made lowercase.
    eventid = models.ForeignKey('Events', models.DO_NOTHING, db_column='EventId')  # Field name made lowercase.
    ptrflag = models.IntegerField(db_column='PtrFlag', blank=True, null=True)  # Field name made lowercase.

    class Meta:
        managed = False
        db_table = 'ParticipationData'
        unique_together = (('userid', 'eventid'),)

I have inserted the data into database using this model successfully. But while selecting data it is showing the error like :

django.db.utils.OperationalError: (1054, "Unknown column 'Participationdata.id' in 'field list'")

How should I resolve this ?

Possible duplicate of ["unknown column X.id" error in django using existing DB](http://stackoverflow.com/questions/4192409/unknown-column-x-id-error-in-django-using-existing-db) — shad0w_wa1k3r, Mar 23 '17 at 05:53
In database I have defined composite key as 'primary key(userid,eventid)'. so I am confused what should I do now — Rajadip, Mar 23 '17 at 06:39

score 4 · Answer 1 · edited May 23 '17 at 11:46

4

Usually you would set primary_key = True on your eventid field, so Django uses it as a default id. Look at the answer in Custom id field in Django model

But it is a ForeignKeyField, so you have to add id column to your database and model. You can remove managed = False (default is True) and then:

Create migrations for the model and migrate --fake-initial (since you have table already, see more: https://docs.djangoproject.com/en/1.10/ref/django-admin/#cmdoption-migrate-fake-initial )
Add AutoField id to your model
Run makemigrations and migrate as usual.

That way django uses your already-existing table, but you can modify it via models.

edited May 23 '17 at 11:46

Community

1
1

answered Mar 23 '17 at 07:48

Janusz 'Ivellios' Kamieński

584
1
6
19

Can I store multiple eventid by using primary key = true in this field ? – Rajadip Mar 23 '17 at 08:02
Sorry, didn't notice it's ForeignKey. Then you should add id field to the model/db. Editing answer now... – Janusz 'Ivellios' Kamieński Mar 23 '17 at 08:05
1

Is there any way to do this without adding id field ? – Rajadip Mar 23 '17 at 08:08
Probably not. See the answer here: http://stackoverflow.com/questions/16864283/django-models-and-primary-foreign-key-relationships – Janusz 'Ivellios' Kamieński Mar 23 '17 at 08:14

score 2 · Accepted Answer · answered Mar 23 '17 at 07:41

2

Do you have id field in Participationdate table?

answered Mar 23 '17 at 07:41

Szymon P.

1,008
7
11

No, I don't. These models are created from existing database using `inspectdb` – Rajadip Mar 23 '17 at 08:07
1

When You don't set primary key manually, Django automatically creates `id` field. You cannot explicitly set composite key - `unique_together` is some kind of trick. The error appears on selecting(also on other operations too) due to column `id` doesn't exist in database. but Django thinks that exists. You should consider use raw SQL queries and select only on columns that exists `userid`,`eventid`,`ptrflag`. – Szymon P. Mar 23 '17 at 08:24
Use of raw sql is giving error `django.db.models.query_utils.InvalidQuery: Raw query must include the primary key ` – Rajadip Mar 24 '17 at 12:05
I wrongly said - you need to execute SQL code raw: not by using `SomeModel.objects.raw("SOME SQL CODE")`, but use `cursor`, e.g. `cursor.execute('SELECT * FROM table')` – Szymon P. Mar 24 '17 at 12:13
1

I resolved this by using `("Select UserId as id from ParticipationData" )` – Rajadip Mar 24 '17 at 12:15

score 0 · Answer 3 · answered Apr 10 '22 at 17:16

I solve this error when I add primary_key=True to my id, because Django need a primary key. In my code, I add the primary key in my View id, because the inspectdb don't get the key from the database view

class ViewRespuestaEncuestas(models.Model):
    id_encuesta = models.IntegerField(primary_key=True)
    encuesta = models.CharField(max_length=45, db_collation='utf8_general_ci')
    id_usuario = models.IntegerField()
    nombre_usuario = models.CharField(
        max_length=40, db_collation='utf8_general_ci', blank=True, null=True)
    id_pregunta = models.IntegerField()
    pregunta = models.TextField(db_collation='utf8_general_ci')
    id_respuesta = models.IntegerField()
    respuesta = models.CharField(max_length=60, db_collation='utf8_general_ci')
    valor = models.IntegerField(blank=True, null=True)

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

Unknown column 'ModelName.id' in 'field list'"

3 Answers3