[Django]-OperationalError, no such column. Django

66👍

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 is 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.

29👍

Step 1: Delete the db.sqlite3 file.

Step 2 : $ python manage.py migrate

Step 3 : $ python manage.py makemigrations

Step 4: Create the super user using $ python manage.py createsuperuser

new db.sqlite3 will generates automatically

12👍

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!

11👍

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.

👤copser

8👍

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

👤marw

8👍

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. 🙂

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 0001, 0002, 0003 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).

👤hygull

5👍

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

3👍

I did the following

  1. Delete my db.sqlite3 database
  2. python manage.py makemigrations
  3. python manage.py migrate

It renewed the database and fixed the issues without affecting my project. Please note you might need to do python manage.py createsuperuser because it will affect all your objects being created.

2👍

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.

2👍

1.First delete only 0001_initial.py from migration file

2.Delete dbsqulite file

3.python manage.py makemigrations appname

4.python manage.py migrate

finally solved

1👍

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.

1👍

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..:)

1👍

You did not migrated all changes you made in model. so
1) python manage.py makemigrations
2) python manage.py migrate
3) python manag.py runserver
it works 100%

1👍

This may be one of the most annoying and time-consuming issue, the traceback is not helpful for solving the problem.

This means your database is messed up, which you must delete the database to start again.

It is worth-mentioning to restruct some existing field:

  • When we tries to connect models, there are basically two ways:

first one

from comments.models import Comment

class Article(models.Model):
    comments = models.ManyToManyField(Comment)

second one

class Article(models.Model):
    comments = models.ManyToManyField('comments.Comment')

The first method, widely used by beginners and it may cause circular import issue, which occurs when you tries to import A.py in B.py and in B.py it also imports A.py.

Therefore, we must abandon the first approach, and implement the second to all. Here’s the way to solve the problem, unfortunately you need to delete the database.

  • delete db.sqlite3
  • for all apps, open migration folder, delete all except for __init__.py
  • python manage.py makemigrations
  • python manage.py migrate

One the problem is solved, if you follow the convention, the issue will never occur again:

  • Do not import models wherever you are, always use this instead:
from django.apps import apps
apps.get_model(app_name, model_name)
# Article = apps.get_model('articles', 'Article')

in fields it can be briefed to string, which <app_name>_<model_name>

class Article(models.Model):
    comments = models.ManyToManyField('comments.Comment')

here is a script to delete all migrations, create a file clean.py at the same level to manage.py

import os


def recursor(dirpath):
    # print(dirpath)
    delfiles = []
    deldirs = []
    with os.scandir(dirpath) as l1:
        for e1 in l1:
            if not e1.is_file():
                with os.scandir(e1.path) as l2:
                    for e2 in l2:
                        if e2.name == 'migrations':
                            with os.scandir(e2.path) as l3:
                                for e3 in l3:
                                    if not e3.name == '__init__.py':
                                        print(e3.path)
                                        if e3.is_file():
                                            delfiles.append(e3.path)
                                        else:
                                            deldirs.append(e3.path)
                                            with os.scandir(e3.path) as l4:
                                                for e4 in l4:
                                                    delfiles.append(e4)
    yn = input('are you sure to delete all the files above?(y/n)')
    if yn == 'y':
        for dp in delfiles:
            os.remove(dp)
        for dp in deldirs:
            os.rmdir(dp)



recursor(os.path.dirname(os.path.realpath(__file__)))

0👍

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

0👍

I had this problem recently, even though on a different tutorial. I had the django version 2.2.3 so I thought I should not have this kind of issue.
In my case, once I add a new field to a model and try to access it in admin, it would say no such column.
I learnt the ‘right’ way after three days of searching for solution with nothing working.
First, if you are making a change to a model, you should make sure that the server is not running. This is what caused my own problem.
And this is not easy to rectify. I had to rename the field (while server was not running) and re-apply migrations.
Second, I found that python manage.py makemigrations <app_name> captured the change as opposed to just python manage.py makemigrations. I don’t know why.
You could also follow that up with python manage.py migrate <app_name>. I’m glad I found this out by myself.

0👍

just remember there is a pycache folder hidden inside the migrations folder so if you change your models and delete all your migration files you MUST delete the pycache folder also.

The only one you should not delete is your init file.

Hope this helps

0👍

In my case, in admin.py I was querying from the table in which new ForeignKey field was added. So comment out admin.py then run makemigrations and migrate command as usual. Finally uncomment admin.py.

0👍

If the error persists after doing what @Burhan Khalid said

Try this line: python manage.py migrate –run-syncdb

0👍

Anyone coming here:

Remove all migrations
Remove db.sqlite file

redo migrations

👤uno

0👍

I simple made a careless mistake of forgetting to actually apply the migration (migrate) after making migrations. Writing this just in case anyone might make the same mistake.

0👍

I think you skipped this steps…run the following commands to see if you had forgotten to execute them…it worked for me.

$ python manage.py makemigrations

$ python manage.py migrate

Thank you.

0👍

Deleting all your migrations in the migration folder of your django app, then run makemigrations followed by migrate commands. You should be able to get out of the woods.

0👍

I had same issue with sqlite. My models.py looked all right. I did the following:

sqlite3 db.sqlite3
.tables
PRAGMA table_info(table_name);

Thru PRAGMA I was able to see that there was columns missing in the table failing.
I dropped all tables in the app. Be careful because this will make lose your data in the tables.

DROP table table_name
.quit

Then do this:

python manage.py makemigrations your_app
python manage.py migrate your_app
python manage.py sqlmigrate your_app 0001

Then enter again to sqlite as follows and paste all the code you got from sqlimigrate:

sqlite3 db.sqlite3

As an example, this is what I pasted inside sqlite:

CREATE TABLE "adpet_ad" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" varchar(200) NOT NULL, "breed" varchar(30) NULL, "weight" decimal NULL, "age" integer NULL, "text" text NOT NULL, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "picture" BLOB NULL, "content_type" varchar(256) NULL, "name" varchar(100) NULL, "phone" varchar(31) NOT NULL, "gender_id" integer NULL REFERENCES "adpet_gender" ("id") DEFERRABLE INITIALLY DEFERRED, "owner_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED, "size_id" integer NULL REFERENCES "adpet_size" ("id") DEFERRABLE INITIALLY DEFERRED, "specie_id" integer NULL REFERENCES "adpet_specie" ("id") DEFERRABLE INITIALLY DEFERRED, "sterilized_id" integer NULL REFERENCES "adpet_sterilized" ("id") DEFERRABLE INITIALLY DEFERRED, "vaccinated_id" integer NULL REFERENCES "adpet_vaccinated" ("id") DEFERRABLE INITIALLY DEFERRED);

0👍

Instead of deleting any existing migrations as some have said, do this:

python manage.py migrate --fake #(only if the below commands don't work)
python manage.py migrate --run-syncdb
python manage.py runserver makemigrations
python manage.py runserver

0👍

I add this because, if your problem persists, it might be that you are attempting to access the database in the init function of a class. The makemigrations process checks all of your modules and classes, and if you attempt to access a table in the database in the init function it might raise an error. Comment that line out and then try makemigrations and migrate

0👍

Whenever you add a new fields you need to change in your database too so you need to run some commands to add a new column

python manage.py makemigrations
python manage.py migrate
python manage.py runserver

If it’s still not working delete your migrations files that name starts with 00 something similar and run those same commands from above. If it’s still not working. delete SQLite database from your project and run those commands once again

0👍

Well, I cannot speak particularly to the db aspect of the issue, or the answers…And I tried deleting the 0001_initial.py but the error persisted.

So for of you(us) experiencing the "no such column: snippets_snippet.owner_id" type of error with django (not necessarily REST), the issue is from the foreign key variable, it’s expecting a default value which isn’t specified.

Solution: add default='' to the foreign key arguments, and it you’ll be fine. For context to this person’s code; owner = models.ForeignKey('auth.User', related_name='snippets', default='')

👤Potato

0👍

Keeping SQLite database solution

Let’s say for example I got this error:

django.db.utils.OperationalError: no such column: shop_product.likes

In order to keep your current SQLite database, you can do the following steps:

  1. Connect to the SQLite database:

    sqlite3 db.sqlite3
    
  2. Alter the the shop_product table, like this:

    ALTER TABLE shop_product ADD COLUMN likes;
    

-1👍

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

Leave a comment