Peewee: column names are stripped their '_id' part

[maxdml]

It seems that when a column is named, say "table_id", the generated models is always "table".

Command used: python -m pwiz -e mysql -u root database >> models.py

edit: I observe the same behavior with names such as "app-id"

e.g.:

sql schema:
DROP TABLE IF EXISTS Application;
/_!40101 SET @saved_cs_client = @@character_set_client */;
/_!40101 SET character_set_client = utf8 _/;
CREATE TABLE Application (
id int(11) NOT NULL AUTO_INCREMENT,
app_id char(100) NOT NULL,
app_name char(100) NOT NULL,
app_env text,
app_submit text NOT NULL,
app_dag text,
app_master char(100) NOT NULL,
app_driver int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY app_driver (app_driver),
CONSTRAINT Application_ibfk_1 FOREIGN KEY (app_driver) REFERENCES Container (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/_!40101 SET character_set_client = @saved_cs_client */;

class Application(BaseModel):
app_dag = TextField(null=True)
app_driver = ForeignKeyField(db_column='app_driver', null=True, rel_model=Container, to_field='id')
app_env = TextField(null=True)
app = CharField(db_column='app_id')
app_master = CharField()
app_name = CharField()
app_submit = TextField()

[maxdml]

After having a look at the code, I found the responsible function, in reflection.py:

425     def make_column_name(self, column):
426         column = re.sub('_id$', '', column.lower().strip()) or column.lower()                                    
427         column = re.sub('[^\w]+', '_', column)
428         if column in RESERVED_WORDS:
429             column += '_'
430         return column

Any reason why '_id' would be removed? Would it be still safe to have it replace such a string by '-id' ?

[coleifer]

It is removed because generally "_id" is database schema cruft, i.e. an indication that the column is a foreign key or something similar.

[maxdml]

Well I actually use it to name foreign keys =) As far as I know, and at least for MySQL, the engine makes no assumption about how FK are named.

[weihaipy]

Is there any way to keep "_id" exist?

[coleifer]

You can name your fields whatever you want, just specify the actual column name using the column_name parameter:

class MyModel(Model):
    some_field = TextField(column_name='actual_column_name')
    another_field = IntegerField(column_name='another_field_id')

[Cheaterz]

Nevertheless, any way to keep '_id' part? I have 150+ tables in db, I don't want to put back all removed '_id's manually. Can it be fixed without changing sources?

[coleifer]

Just to be clear -- the "_id" bit is only stripped from foreign-key fields.

Peewee exposes foreign-keys as model instances.

Here are some sample models:

class User(Model):
    username = TextField()
    class Meta:
        table_name = 'users'  # Some databases do not like table named "user".

class Tweet(Model):
    user = ForeignKeyField(User, backref='tweets')
    content = TextField()
    timestamp = TimestampField()

The corresponding database schema would look something like:

create table "users" ("id" integer not null primary key, "username" text not null);
create table "tweet" (
  "id" integer not null primary key,
  "user_id" integer not null,
  "content" text not null,
  "timestamp" integer not null,
  foreign key ("user_id") references "users" ("id"))

When interacting with our models in Python, it makes sense to call the tweet.user_id foreign key "tweet.user" since it is exposed as a user instance:

# Get 20 most recent tweets and also load the associated user.
tweets = Tweet.select(Tweet, User).join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in tweets:
    print(tweet.user.username, '->', tweet.content)

Peewee also does a nice thing with foreign-keys where the actual underlying column-value (in this case the related user's id) is exposed using a special descriptor:

query = Tweet.select().join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id, '->', tweet.content)  # Print's the user's ID and the tweet content.

If the field were named "user_id" instead, my own opinion is that is would be confusing. Consider:

class Tweet(Model):
    user_id = ForeignKeyField(User, column_name='user_id', backref='tweets')
    ...

query = Tweet.select(Tweet, User).join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id.username, '->', tweet.content)  # Here the related user instance is called user_id?

query = Tweet.select().join(User).order_by(Tweet.timestamp.desc()).limit(20)
for tweet in query:
    print(tweet.user_id_id, '->', tweet.content)  # And the ID descriptor is called "user_id_id" :/

So by default, that is why pwiz strips the "_id" off of foreign-keys when generating models. Of course you are encouraged to edit the generated code, however.

[Cheaterz]

Well, yes, I see, thanks for the explanation.
But I encountered an interesting behaviour. I'm developing a script for a XenForo-based forum, so I used pwiz to generate a model based on mysql schema. On my local machine it worked as expected: the script stipped out all "_id" parts. But when pwiz was launched on a client's machine, all "_id"s were preserved. So I thought, there is some option that can manipulate script's behaviour

[coleifer]

Probably their pre-existing database schema was missing the foreign key constraints. Cuz the story with referential integrity and MySQL...

[Cheaterz]

Yes, maybe, by the way. XenForo uses an interesting schema without (in some versions, as far as I know) foreign keys.
Thank you.

[coleifer]

The MyISAM storage engine, default before 5.5.5, does not handle fk constraints.

[Cheaterz]

I don't think that this is the problem, but I'll check it. Thanks again