Error 1054, "Unknown column 'student_info.id' in 'field list'" - Django

Question

I had an existing database with a particular table which contained 5 columns and a lot of data. (I imported this from CSV)

If I execute DESCRIBE student_info in MySql, it shows:

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| pic_id | int(11)     | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| hostel | varchar(3)  | YES  |     | NULL    |       |
| room   | int(11)     | YES  |     | NULL    |       |
| branch | varchar(4)  | YES  |     | NULL    |       |
| id_no  | varchar(12) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

This table has all the data, as I've tested by various SELECT statements and via Workbench

In settings.py, I configured my project to connect with that database, successfuly. Then, I gave the command

python manage.py inspectdb > studentinfo/models.py

And the contents of models.py became:

class StudentInfo(models.Model):
    pic_id = models.IntegerField(null=True, blank=True)
    name = models.CharField(max_length=150, blank=True)
    hostel = models.CharField(max_length=9, blank=True)
    room = models.IntegerField(null=True, blank=True)
    branch = models.CharField(max_length=12, blank=True)
    id_no = models.CharField(max_length=36, blank=True)
    class Meta:
        db_table = u'student_info'

Then, if I execute this after syncdb:

python manage.py sqlall studentinfo
    BEGIN;
    CREATE TABLE `student_info` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `pic_id` integer,
        `name` varchar(150) NOT NULL,
        `hostel` varchar(9) NOT NULL,
        `room` integer,
        `branch` varchar(12) NOT NULL,
        `id_no` varchar(36) NOT NULL
    )
    ;
    COMMIT;

So everything seems to be in order, except that I'm getting this error if I try to retreive anything:

>>> from studentinfo.models import StudentInfo
>>> list=StudentInfo.objects.all()
>>> list
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/django/db/models/query.py", line 69, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/usr/lib/python2.7/dist-packages/django/db/models/query.py", line 84, in __len__
    self._result_cache.extend(self._iter)
  File "/usr/lib/python2.7/dist-packages/django/db/models/query.py", line 273, in iterator
    for row in compiler.results_iter():
  File "/usr/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 680, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/lib/python2.7/dist-packages/django/db/models/sql/compiler.py", line 735, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
**OperationalError: (1054, "Unknown column 'student_info.id' in 'field list'")**

Any idea what might be causing this?

Answer 1

sqlall just shows what would be created if you were running syncdb with an empty database. As is obvious from your DESCRIBE, there is no id field - in fact no primary key at all, which is a very odd design. You will need to add the field manually via SQL.

Answer 2

I had the same situation working with a legacy database.

The problem is that that a vanilla queryset, will try to fetch the id to build the model object. What I ended up doing, is querying using .values(). That way, there is no model building, and no id.