Django Models (1054, "Unknown column in 'field list'")

Question

No idea why this error is popping up. Here are the models I created -

from django.db import models
from django.contrib.auth.models import User

class Shows(models.Model):
    showid= models.CharField(max_length=10, unique=True, db_index=True)
    name  = models.CharField(max_length=256, db_index=True)
    aka   = models.CharField(max_length=256, db_index=True)
    score = models.FloatField()

class UserShow(models.Model):
    user  = models.ForeignKey(User)
    show  = models.ForeignKey(Shows)

Here is the view from which I access these models -

from django.http import HttpResponse
from django.template import Context
from django.template.loader import get_template
from django.http import HttpResponse, Http404
from django.contrib.auth.models import User

def user_page(request, username):
    try:
        user = User.objects.get(username=username)
    except:
        raise Http404('Requested user not found.')

    shows     = user.usershow_set.all()
    template  = get_template('user_page.html')
    variables = Context({
        'username': username,
        'shows'   : shows})
    output = template.render(variables)
    return HttpResponse(output)

At this point I get an error -

OperationalError: (1054, "Unknown column 'appname_usershow.show_id' in 'field list'")

As you see this column is not even present in my models? Why this error?

Strange. Usually you get a model validation error if you declare `id` as a field in a model. I am referring to the first field of the `Shows` model. Can you check again? — Manoj Govindan, Sep 24 '10 at 13:02
that was a typo. it should have been 'showid'. Coming back, syncdb command ends up creating the models in the DB. No errors at this stage. Only when the view is invoked, this error comes out... — Srikar Appalaraju, Sep 24 '10 at 13:06
Can you post the stack trace of the error? I'd like to know which line inside your view is raising it. — Manoj Govindan, Sep 24 '10 at 13:11
@movieyoda: See my answer below. Something is going wrong in your template. Can you edit your question and post the template code? — Manoj Govindan, Sep 24 '10 at 14:28
@Manjo Fixed it myself. Apparently any changes to the models when updated through syncdb does not change (as in update/modify) the actual tables. So I dropped the relevant DB & ran syncdb on empty DB. now it works fine :) Thanks all... — Srikar Appalaraju, Sep 24 '10 at 14:31
@movieyoda: yep, syncdb will not act on existing tables. Have a look at south for database migrations. — Matthew Schinckel, Sep 25 '10 at 13:48

score 28 · Answer 1 · answered Nov 08 '10 at 06:24

28

maybe your tables schema has been changed? Also, running syncdb does not update already created tables.

You might need to drop all the tables & then run syncdb again. Also remember to take backup of your data!!

answered Nov 08 '10 at 06:24

1

+1, This saved me a lot of hair-pulling (after spending far too long with the same problem) – Problematic Jan 23 '11 at 00:46

Srikar Appalaraju · Accepted Answer · 2019-09-29T20:05:31.023

As @inception said my tables schema had changed & running syncdb did not update already created tables.

Apparently any changes to the models when updated through syncdb does not change (as in update/modify) the actual tables. So I dropped the relevant DB & ran syncdb on empty DB. Now it works fine. :)

For others, SOUTH data migration tool for Django seems to be favorite option. It seems to provide options which django models & syncdb falls short on. Must check out...

Update 29th Sept 2019: From Django 1.7 upwards, migrations are built into the core of Django. If you are running a previous lower version of Django, you can find the repository on BitBucket.

score 6 · Answer 3 · answered Sep 24 '10 at 13:44

6

Normally I get this when when I'm trying to access field which doesn't exist in Database.

Check if the field exist in the database. If you change model and perform syncdb it won't update the database, I'm not sure if that's the case.

On other note Django offers shortcut to replace try/except block in your code using get_object_or_404. (available in django.shortcuts )

try:
     user = User.objects.get(username=username)
except:
     raise Http404('Requested user not found.')

can be changed to:

user = get_object_or_404(User, username=username)

answered Sep 24 '10 at 13:44

Srikanth Chundi

897
9
8

This is essentially what happens when trying to use Django Rest Framework serializers. Make sure if you have a serializer that subclasses `ModelSerializer`, that you migrate any changes to the Models before writing your serializer classes (or just comment anything related to the serializer, migrate, then uncomment). – keverly Feb 22 '17 at 06:05

Ni Xiaoni · Answer 4 · 2013-10-07T14:02:46.123

4

I have met the same problems:

First, run

manage.py sqlall [appname]

and you can find:

`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,

and I add the column manual:

ALTER TABLE tb_realtime_data ADD id integer AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST;

and then it worked.

I think django will add the column called id itself.

For convenience, each model is given an autoincrementing primary key field named id unless you explicitly specify primary_key=True on a field (see the section titled “AutoField” in Appendix A).

you can click here for details.

Good luck.

edited Oct 07 '13 at 14:02

answered Oct 07 '13 at 13:00

Ni Xiaoni

1,639
2
13
10

Can you please enlighten me how you added that line and where it needs to be added? – Sravan K Ghantasala Jan 17 '14 at 12:11
I am very sorry that I reply you today. Ok, this is a good question. You use it when you want to use django ORM to access the database which is created by _handed_ not django. I mean that you’ve defined a model, you’ll use this API any time you need to access the database. But the database is not created by django ORM. When this, you should add a column called `id` to the **table**. You can connect to the database by using sql directly, I think. – Ni Xiaoni Feb 01 '14 at 06:53

score 4 · Answer 5 · answered Jun 03 '20 at 04:41

I faced the same error like you posted above with MySQL database back-end, after long time of resolving this error, I ended up with below solution.

Manually added column to database through workbench with name exactly the same as it is shown in your error message.
After that run below command

python manage.py makemigrations

Now migrations will be successfully created

After that run below command

python manage.py migrate --fake

Now every thing works fine without any data lose issue

score 3 · Answer 6 · answered Nov 03 '14 at 16:30

3

In the appropriate field explicitly set

primary_key = True

answered Nov 03 '14 at 16:30

Yurkol

1,414
1
19
28

yeah, for my case, I am having a table which doesnt have primary key causing the error. – zinking Jan 31 '16 at 13:29

slajma · Answer 7 · 2018-12-20T15:11:05.917

This is quite an old question but you might find the below useful anyway: Check the database, you're most likely missing the show_id in the appname_usershow table.

This could occur when you change or add a field but you forget running migration.

OR

When you're accessing a non managed table and you're trying to add a ForeignKey to your model. Following the example above:

class UserShow(models.Model):
    # some attributes ...
    show = models.ForeignKey(Show, models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'appname_departments'

score 1 · Answer 8 · answered Aug 01 '20 at 15:35

This is a late response but hopefully someone will find this useful.

I was working on Django REST APIs using DB models. When I added a new column to my existing model(said column already existed in the db table from the start), I received the error shown below: "Unknown column ',column name>' in 'field list'"executed".

What I missed was migrating the model over to the database.

So I executed the following commands from python terminal:

py -3 manage.py makemigrations ---> It will not allow NULL values for the new column added to the model, even though the column is present in the database from the start. So you need to add a default value, mine was an Integerfield, so I updated my column definition in the model as IntegerField(default=0). From here onwards it should be straightforward, if there are no other errors.
py -3 manage.py migrate
py -3 manage.py runserver

After executing these steps when I called my REST APIs they were working properly.

score 0 · Answer 9 · answered Sep 10 '13 at 15:46

I created a model file for my app and then did several sqlall as I refined my tables. One of the changes I made was I set primary_key=True to one of my fields. At the end called for syncdb. Added a dummy value and and tried to access it with User.objects.all(), User being my model class. Although this worked fine, this error came up while printing the list returned by the all() call. It read DatabaseError: (1054, "Unknown column 'polls_user.id' in 'field list'")

Surprisingly, I tried and could get it resolved with another call to syncdb. I remember not having seen the id column in the table at any time throughout the exercise when I checked it through the mysql command line client.

score 0 · Answer 10 · answered Feb 22 '17 at 06:02

I received this error while trying to use Django Rest Framework serializers. Make sure if you have a serializer that subclasses ModelSerializer, that you migrate any changes to the Models before writing your serializer classes (or just comment anything related to the serializer, migrate, then uncomment).

score 0 · Answer 11 · answered Oct 21 '19 at 09:39

PS F:\WebApp> python manage.py makemigrations You are trying to add a non-nullable field 'price' to destination without a default; we can't do that (the database needs something to populate existing rows). Please select a fix: 1) Provide a one-off default now (will be set on all existing rows with a null value for this column) 2) Quit, and let me add a default in models.py Select an option: 2 PS F:\WebApp> python manage.py sqlmigrate travello 0001

BEGIN;

-- Create model Destination

CREATE TABLE travello_destination (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, name varchar(100) NOT NULL, img varchar(100) NOT NULL, desc longtext NOT NULL, offer bool NOT NULL); COMMIT; PS F:\WebApp> python manage.py makemigrations Migrations for 'travello': travello\migrations\0002_destination_price.py - Add field price to destination PS F:\WebApp> python manage.py migrate Operations to perform: Apply all migrations: admin, auth, contenttypes, sessions, travello Running migrations: Applying travello.0002_destination_price... OK

if you you want to add extra field like price into mysql db.1. — user12250631, Oct 21 '19 at 09:42

score 0 · Answer 12 · edited May 19 '20 at 14:08

I had this issue when using a composite Primary Key of several VarChar fields and trying to call a table_set.all() queryset. Django wanted a table_name_id PK column for this queryset, there wasn't one so it threw out this error. I fixed it by manually creating the table_name_id and setting it to an auto-incremented, integer PK column instead of the composite PK. I then specified those VarChar composite columns as unique_together in the Model's meta section so they act like a PK.

score 0 · Answer 13 · answered Mar 28 '23 at 03:43

0

The model of the app which has that field you have to run two commands.

python manage.py migrate appname zero
python manage.py migrate

answered Mar 28 '23 at 03:43

Anshuman Bisoyi

149
4

score -2 · Answer 14 · answered Dec 11 '17 at 08:00

-2

The direct solution is delete files under folder ../Project/App/migrations, and the method to avoid this problem is create new databaes table column instead of chagne the existing one.

answered Dec 11 '17 at 08:00

misybing

29
2

Django Models (1054, "Unknown column in 'field list'")

14 Answers14

BEGIN;

-- Create model Destination

Linked