CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
titusjaka
titusjaka

Reputation: 179

How do I create many-to-many association using Flask-sqlalchemy with extra data?

I need to associate two models employee and skill using flask-sqlalchemy with extra data.

For example, user1 can do skill1 with efficiency 100% and skill2 with efficiency 80%.

So, I make 3 models:

class Employee(db.Model):
    __tablename__ = 'employees'

    id = db.Column(db.Integer, primary_key=True)
    login = db.Column(db.String(64), index=True, unique=True, nullable=False)

    skills = db.relationship('Skill', secondary='employee_skills',
                             backref=db.backref('employees', lazy='dynamic'))

    def __repr__(self):
        return '<Employee %r, Skills: %r>' % (self.login, self.skills)


class Skill(db.Model):
    __tablename__ = 'skills'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False, unique=True)

    def __repr__(self):
        return '<Skill %r>' % self.name


class EmployeeSkill(db.Model):
    __tablename__ = 'employee_skills'

    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'), primary_key=True)
    skill_id = db.Column(db.Integer, db.ForeignKey('skills.id'), primary_key=True)
    efficiency = db.Column(db.Float)

    skills_efficiency = db.relationship('Skill', backref=db.backref('employee_efficiency', lazy='dynamic'))

    def __repr__(self):
        return '<Engineer %r, skill %r, efficiency %r>' % (
            self.employee_id, self.skill_id, self.efficiency)

After that I try to access to this associantion:

>>> from app import db, models      
>>> emp_=models.Employee.query.all()
>>> emp_[0]
<Employee u'user1', Skills: [<Skill u'skill1'>, <Skill u'skill2'>]>
>>> emp_[0].skills
[<Skill u'skill1'>, <Skill u'skill2'>]
>>> emp_[0].skills[0]
<Skill u'skill1'>
>>> emp_[0].skills[0].employee_efficiency
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x0000000003A68FD0>
>>> emp_[0].skills[0].employee_efficiency[0]
<Engineer 1, skill 1, efficiency u'1'>

What I want is to represent Employee instance as

<Employee u'user1', Skills: <u'skill1', efficiency: '1'>, <u'skill2', efficiency: '0.8'>>

But I don't understand: Do I use db.relationship in a wrong way? Or do I need to complite my repr method?

Thanks in advance

Upvotes: 2

Views: 2703

Answers (2)

Celeo
Celeo

Reputation: 5682

Yes, you could change the repr method:

class Skill(db.Model):
    __tablename__ = 'skills'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), nullable=False, unique=True)

    def __repr__(self):
        return '<Skill %r, efficiency: %r>' % (self.name, self.employee_efficiency[0].efficiency)

You already have the backref, so use that.

Upvotes: -2

Miguel Grinberg
Miguel Grinberg

Reputation: 67479

When you need extra columns in the association table you need to use the association object pattern instead of the secondary attribute. This pattern basically consists in decomposing the many-to-many relationship into two one-to-many relationships.

In your case you would create a one-to-many relationship from Employee to EmployeeSkills, and you would keep the many-to-one relationship you already have between EmployeeSkills and Skill.

When you use the secondary attribute SQLAlchemy manages the association table automatically for you. When you use the association object pattern you have to manage the association table yourself, and that gives you access to set or query the extra columns.

Upvotes: 5

Related Questions