(Python) - Peewee - How to create a foreign key

Question

I'm trying to create a relationship between two table fields via a foreign key but failing with the examples in the doc or related threads at stackoverflow. Here's my code:

class User(BaseModel):
    """ Field Types """
    user_id = PrimaryKeyField()
    username = CharField(25)
    role = ForeignKeyField(User, to_field='role_id')

    class Meta:
        db_table = 'users'


class User(BaseModel):
    """ Field Types """
    role_id = PrimaryKeyField()
    rolename = CharField(25)

    class Meta:
        db_table = 'roles'

to_field refers to the related field in the equivalent table 'users' in my code snippet. In the docs of peewee I saw related_names as parameter which refers to the non-existing field tweets in the User model.

class User(Model):
    name = CharField()

class Tweet(Model):
    user = ForeignKeyField(User, related_name='tweets')
    content = TextField()

Hopefully someone could explain this syntax.

Answer 1

I don't think you wanted to have 2 "User" models. And tweets has nothing to do with your User and Role schema.

It's not entirely clear what you mean, but I think something like this should work.

class Role(BaseModel):
    """ Field Types """
    role_id = PrimaryKeyField()
    rolename = CharField(25)

    class Meta:
        db_table = 'roles'

class User(BaseModel):
    """ Field Types """
    user_id = PrimaryKeyField()
    username = CharField(25)
    role = ForeignKeyField(Role, to_field="role_id")

    class Meta:
        db_table = 'users'

Hopefully the meaning of to_field is clear. It says, that the role field on the User model should point to a record in the Role table based on the role_id column.

Related name is used for the back reference. For example, you could combine them so that you could access all the users that have a given role.

For example, change your user class:

class User(BaseModel):
    """ Field Types """
    user_id = PrimaryKeyField()
    username = CharField(25)
    role = ForeignKeyField(Role, to_field="role_id", related_name="users")

    class Meta:
        db_table = 'users'

Now given you've got your say, "Superuser" role loaded you should be able to do something like this to loop over all the users that have that role.

for user in superuser.users
    print user.username