OperationalError, no such column. Django

问题:

I am very new to django and was able to finish the tutorial on djangoproject.com without any errors. I am now going through the Django REST framework tutorial found at http://www.django-rest-framework.org/ I am almost finished with it and just added authentication. Now I am getting :

OperationalError at /snippets/
no such column: snippets_snippet.owner_id
Request Method: GET
Request URL:    http://localhost:8000/snippets/
Django Version: 1.7
Exception Type: OperationalError
Exception Value:    
no such column: snippets_snippet.owner_id
Exception Location: /Users/taylorallred/Desktop/env/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py in execute, line 485
Python Executable:  /Users/taylorallred/Desktop/env/bin/python
Python Version: 2.7.5
Python Path:    
['/Users/taylorallred/Desktop/tutorial',
 '/Users/taylorallred/Desktop/env/lib/python27.zip',
 '/Users/taylorallred/Desktop/env/lib/python2.7',
 '/Users/taylorallred/Desktop/env/lib/python2.7/plat-darwin',
 '/Users/taylorallred/Desktop/env/lib/python2.7/plat-mac',
 '/Users/taylorallred/Desktop/env/lib/python2.7/plat-mac/lib-scriptpackages',
 '/Users/taylorallred/Desktop/env/Extras/lib/python',
 '/Users/taylorallred/Desktop/env/lib/python2.7/lib-tk',
 '/Users/taylorallred/Desktop/env/lib/python2.7/lib-old',
 '/Users/taylorallred/Desktop/env/lib/python2.7/lib-dynload',
 '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7',
 '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-darwin',
 '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-tk',
 '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac',
 '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac/lib-scriptpackages',
 '/Users/taylorallred/Desktop/env/lib/python2.7/site-packages']
Server time:    Sat, 11 Oct 2014 07:02:34 +0000

I have looked in several places on the web, not just stackoverflow for the solution, it seems like in general that the problem is with my database and need to delete it then remake it, I have done this several times, the tutorial even has me delete the database and remake it at point. Here is my models.py:

from django.db import models
from pygments.lexers import get_all_lexers
from pygments.styles import get_all_styles
from pygments.lexers import get_lexer_by_name
from pygments.formatters.html import HtmlFormatter
from pygments import highlight


LEXERS = [item for item in get_all_lexers() if item[1]]
LANGUAGE_CHOICES = sorted([(item[1][0], item[0]) for item in LEXERS])
STYLE_CHOICES = sorted((item, item) for item in get_all_styles())



class Snippet(models.Model):
    owner = models.ForeignKey('auth.User', related_name='snippets')
    highlighted = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    title = models.CharField(max_length=100, blank=True, default='')
    code = models.TextField()
    linenos = models.BooleanField(default=False)
    language = models.CharField(choices=LANGUAGE_CHOICES,
                                            default='python',
                                            max_length=100)
    style = models.CharField(choices=STYLE_CHOICES,
                                     default='friendly',
                                     max_length=100)
    class Meta:
        ordering = ('created',)
def save(self, *args, **kwargs):
    """
    Use the 'pygments' library to create a highlighted HTML
    representation of the code snippet.
    """
    lexer = get_lexer_by_name(self.language)
    linenos = self.linenos and 'table' or False
    options = self.title and {'title': self.title} or {}
    formatter = HtmlFormatter(style=self.style, linenos=linenos,
                                      full=true, **options)
    self.highlighted = highlight(self.code, lexer, formatter)
    super(Snippet, self).save(*args, **kwargs)

My serializers.py:

from django.forms import widgets
from rest_framework import serializers
from snippets.models import Snippet, LANGUAGE_CHOICES, STYLE_CHOICES
from django.contrib.auth.models import User



class SnippetSerializer(serializers.ModelSerializer):
    owner = serializers.Field(source='owner.username')
    class Meta:
        model = Snippet
        fields = ('id', 'title', 'code', 'linenos', 'language', 'style', 'owner')


class UserSerializer(serializers.ModelSerializer):
    snippets = serializers.PrimaryKeyRelatedField(many=True)


    class Meta:
        model = User
        fields = ('id', 'username', 'snippets')

My views.py:

from snippets.models import Snippet
from snippets.serializers import SnippetSerializer
from rest_framework import generics
from django.contrib.auth.models import User
from snippets.serializers import UserSerializer
from rest_framework import permissions

class SnippetList(generics.ListCreateAPIView):
    """
    List all snippets, or create a new snippet.
    """
    queryset = Snippet.objects.all()
    serializer_class = SnippetSerializer
    def pre_save(self, obj):
        obj.owner = self.request.user
    permission_classes = (permissions.IsAuthenticatedOrReadOnly,)

class SnippetDetail(generics.RetrieveUpdateDestroyAPIView):
    """
    Retrieve, update or delete a nippet instance.
    """
    queryset = Snippet.objects.all()
    serializer_class = SnippetSerializer
    def pre_save(self, obj):
        obj.owner = self.request.user
    permission_classes = (permissions.IsAuthenticatedOrReadOnly,)

class UserList(generics.ListAPIView):
    queryset = User.objects.all()
    serializer_class = UserSerializer

class UserDetail(generics.RetrieveAPIView):
    queryset = User.objects.all()
    serializer_class = UserSerializer

And finally my urls.py

from django.conf.urls import include
from django.conf.urls import patterns, url
from rest_framework.urlpatterns import format_suffix_patterns
from snippets import views


urlpatterns = patterns('',
    url(r'^snippets/$', views.SnippetList.as_view()),
    url(r'^snippets/(?P<pk>[0-9]+)/$', views.SnippetDetail.as_view()),
    url(r'^users/$', views.UserList.as_view()),
    url(r'^users/(?P<pk>[0-9]+)/$', views.UserDetail.as_view()),
)

urlpatterns = format_suffix_patterns(urlpatterns)

urlpatterns += patterns('',
    url(r'^api-auth/', include('rest_framework.urls',
                                       namespace='rest_framework')),
)

I apologize if I posted a bunch of unnecessary info. Thanks in advance guys.

Edit: DB Schema:

CREATE TABLE "snippets_snippet" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
"created" datetime NOT NULL, "title" varchar(100) NOT NULL, "code" text NOT NULL, 
"linenos" bool NOT NULL, "language" varchar(100) NOT NULL, "style" varchar(100) NOT NULL);

After doing some digging I found that when deleting and recreating the db (as the tutorial says to) instead of using the makemigrations command it would not only NOT add the columns but it would also not tell me something was wrong, when running the makemigrations command it tells me:

You are trying to add a non-nullable field 'highlighted' to snippet 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)
 2) Quit, and let me add a default in models.py

If I comment out the highlighted section in models.py it will post the same message above but for the owner line. So it wants a default for both highlighted and owner, but I am not sure what to use as it. As well as the tutorial isn't helping me either on it.

回答1:

As you went through the tutorial you must have come across the section on migration, as this was one of the major changes in django 1.7

Prior to django 1.7, the syncdb command never made any change that had a chance to destroy data currently in the database. This meant that if you did syncdb for a model, then added a new row to the model (a new column, effectively), syncdb would not affect that change in the database.

So either you dropped that table by hand and then ran syncdb again (to recreate it from scratch, losing any data), or you manually entered the correct statements at the database to add only that column.

Then a project came along called south which implemented migrations. This meant that there was a way to migrate forward (and reverse, undo) any changes to the database and preserve the integrity of data.

In django 1.7, the functionality of south was integrated directly into django. When working with migrations, the process is a bit different.

1. Make changes to models.py (as normal).
2. Create a migration. This generates code to go from the current state, to the next state of your model. This is done with the makemigrations command. This command is smart enough to detect what has changed, and will create a script to effect that change to your database.
3. Next, you apply that migration with migrate. This command applies all migrations in order.

So your normal syncdb is now a two step process, python manage.py makemigrations followed by python manage.py migrate.

Now, on to your specific problem:

class Snippet(models.Model):
    owner = models.ForeignKey('auth.User', related_name='snippets')
    highlighted = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    title = models.CharField(max_length=100, blank=True, default='')
    code = models.TextField()
    linenos = models.BooleanField(default=False)
    language = models.CharField(choices=LANGUAGE_CHOICES,
                                            default='python',
                                            max_length=100)
    style = models.CharField(choices=STYLE_CHOICES,
                                     default='friendly',
                                     max_length=100)

In this model, you have two fields highlighted and code that are required (they cannot be null).

Had you added these fields from the start, there wouldn't be a problem because the table has no existing rows.

However, if the table has already been created and you add a field that cannot be null, you have to define a default value to provide for any existing rows - otherwise the database will not accept your changes because they would violate the data integrity constraints.

This is what the command is prompting you about. You can tell django to apply a default during migration, or you can give it a "blank" default highlighted = models.TextField(default='') in the model itself.

回答2:

Let's focus on the error:

Exception Value: no such column: snippets_snippet.owner_id

Let's see if that's true...

You can use the manage.py command to access your db shell (this will use the settings.py variables, so you're sure to connect to the right one).

manage.py dbshell

You can now show the details of your table by typing:

.schema TABLE_NAME

Or in your case:

.schema snippets_snippet

More sqlite commands can be found here or by issuing a:

.help

Lastly, end your session by typing:

.quit

This doesn't get you out of the woods, but it helps you know what end of the problem to work on :)

Good luck!

回答3:

I see we have the same problem here, I have the same error. I want to write this for the future user who will experience the same error. After making changes to your class Snippet model like @Burhan Khalid said, you must migrate tables:

python manage.py makemigrations snippets
python manage.py migrate

And that should resolve the error. Enjoy.

回答4:

This error can happen if you instantiate a class that relies on that table, for example in views.py.

回答5:

The most direct way of solving this type of problem is just the following 3 steps process:

1. Delete all the migration related files from App's migrations folder/directory(these basically starts with 0000, 0001, 0002 etc).

2. Delete/Rename the existing database file named db.sqlite3 from App directory.

3. Now run the following command:

   python manage.py migrate

   Finally execute

   python manage.py createsuperuser

   to perform the administrative tasks(If you want).

回答6:

I am also faced same problem.

If you're adding a new field then it gives the error as no column found.

Then you apply make migration command and after that migrate command Then still same error.. EX...

 path=models.FilePathField()

Add default value for field

  path=models.FilePathField(default='')

and than apply command

  python manage.py makemigrations

  python manage.py migrate

It may help you

回答7:

If your issue is like mine, then this a workaround. The good news is that you wont have to delete your db.

Check that there isn't some other model that uses this model as a reference.

django.db.utils.OperationalError: no such column: parts_part_type.blah

This was only happening to me because I had another model called "product" in a different app called "products" that referenced this model.

part = models.ForeignKey("parts.Part", related_name="some part", limit_choices_to={'part_type':Part_Type.objects.get(prefix='PART')},)

My solution was:

1. comment out the other app (in this case prodcuts) from settings.py
2. python manage.py makemigrations; python manage.py migrate
3. un-comment the other app so its enabled again.
4. python manage.py makemigrations; python manage.py migrate

Technically I think I need to change the limit_choices_to reference so

回答8:

Taken from Burhan Khalid's answer and his comment about migrations: what worked for me was removing the content of the "migrations" folder along with the database, and then running manage.py migrate. Removing the database is not enough because of the saved information about table structure in the migrations folder.

回答9:

You did every thing correct, I have been gone through same problem. First delete you db and migrations I solved my adding name of my app in makemigrations:

python manage.py makemigrations appname
python manage.py migrate

This will definitely work.

回答10:

Agree with Rishikesh. I too tried to solve this issue for a long time. This will be solved with either or both of 2 methods-

1.Try deleting the migrations in the app's migrations folder(except init.py) and then running makemigrations command

2.If that doesn't work, try renaming the model (this is the last resort and might get a little messy but will work for sure.If django asks "did you rename the model? just press N.").Hope it helps..:)

回答11:

I faced this problem and this is how I solved it.

1) Delete all the migration records from your app's migration directory. These are files named 0001_,0002_,0003_ etc. Be careful as to not delete the _init__.py file.

2) Delete the db.sqlite3 file. It will be regenerated later.

Now, run the following commands:

python manage.py makemigrations appname
python manage.py migrate

Be sure to write the name of your app after makemigrations. You might have to create a superuser to access your database again. Do so by the following

python manage.py createsuperuser

回答12:

Initially ,I have commented my new fields which is causing those errors, and run python manage.py makemigrations and then python manage.py migrate to actually delete those new fields.

class FootballScore(models.Model):
    team = models.ForeignKey(Team, related_name='teams_football', on_delete=models.CASCADE)
    # match_played = models.IntegerField(default='0')
    # lose = models.IntegerField(default='0')
    win = models.IntegerField(default='0')
    # points = models.IntegerField(default='0')

class FootballScore(models.Model):
    team = models.ForeignKey(Team, related_name='teams_football', on_delete=models.CASCADE)
    match_played = models.IntegerField(default='0')
    lose = models.IntegerField(default='0')
    win = models.IntegerField(default='0')
    points = models.IntegerField(default='0')

Then i freshly uncommented them and run python manage.py makemigrations and python manage.py migrate and boom. It worked for me. :)