Inserting into MySQL table using peewee raises "unknown column" exception

Question

I have the following script:

from peewee import *

db = MySQLDatabase('database', user='root')

class BaseModel(Model):
    class Meta:
        database = db

class Locations(BaseModel):
    location_id = PrimaryKeyField()
    location_name = CharField()

class Units(BaseModel):
    unit_id = PrimaryKeyField()
    unit_num = IntegerField()
    location_id = ForeignKeyField(Locations, related_name='units')

db.connect()

for location in Locations.select():
    for pod_num in range (1, 9):
        unit = Units.create(unit_num=pod_num, location_id=location.location_id)

table locations has few rows, table units is empty. When I try to start it, I keep getting exception:

(1054, "Unknown column 'location_id_id' in 'field list'")

What am I doing wrong?

Here is part of SQL script for creating table:

CREATE  TABLE IF NOT EXISTS `database`.`units` (
  `unit_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `unit_num` TINYINT UNSIGNED NOT NULL ,
  `location_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`unit_id`) ,
  UNIQUE INDEX `ID_UNIQUE` (`unit_id` ASC) ,
  INDEX `location_idx` (`location_id` ASC) ,
  CONSTRAINT `location_id`
    FOREIGN KEY (`location_id` )
    REFERENCES `database`.`locations` (`location_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Thank you in advance!

Goings

Did you change your model's fields after table creation? – Khajeh

Denis, not I didn't, I've just started to learn peewee, the code above is almost everything I have. – Goings

and how did you create this table? – Khajeh

With SQL script above (I use Workbench, so it was EER model -> export script -> run script). Just found that renaming column 'location_id' to 'location' fixes the problem :) – Goings

but still I would like to use 'location_id' name (since i'm not allowed to change its name in production db), so question is still actual – Goings

Then you should name your field location_id_id, because ForeignKeyField automaticly add _id suffix at your queries. – Khajeh

Answer 1

If you want to explicitly specify a column, use db_column:

class Units(BaseModel):
    unit_id = PrimaryKeyField()
    unit_num = IntegerField()
    location_id = ForeignKeyField(Locations, db_column='location_id', related_name='units')

This is documented: http://peewee.readthedocs.org/en/latest/peewee/models.html#field-types-table

Reorganize