MyTetra Share
Делитесь знаниями!
Как в SQLAlchemy создать связь "многие ко многим" с дополнительными столбцами
14.01.2018
14:09
Автор: br0ke
Текстовые метки: python, sqlalchemy, db, dbms, many2many, many-to-many, relationship
Раздел: Информационные технологии - Python - Библиотеки - SQLAlchemy

Question:

I have 3 tables: User, Community, community_members (for relationship many2many of users and community).

I create this tables using Flask-SQLAlchemy:

community_members = db.Table('community_members',

db.Column('user_id', db.Integer, db.ForeignKey('user.id')),

db.Column('community_id', db.Integer, db.ForeignKey('community.id')),

)



class Community(db.Model):

__tablename__ = 'community'


id = db.Column(db.Integer, primary_key=True)

name = db.Column(db.String(100), nullable=False, unique=True)

members = db.relationship(User, secondary=community_members,

backref=db.backref('community_members', lazy='dynamic'))

 

Now I want add additional field to community_members like this:

community_members = db.Table('community_members',

db.Column('id', db.Integer, primary_key=True),

db.Column('user_id', db.Integer, db.ForeignKey('user.id')),

db.Column('community_id', db.Integer, db.ForeignKey('community.id')),

db.Column('time_create', db.DateTime, nullable=False, default=func.now()),

)

 

And now in python shell I can do this:

create community:

> c = Community()

> c.name = 'c1'

> db.session.add(c)

> db.session.commit()

 

add members to community:

> u1 = User.query.get(1)

> u2 = User.query.get(2)

> c.members.append(u1)

> c.members.append(u2)

> db.session.commit()


> c.members

[<User 1>, <User 2>]

 

Ok, this works.

But how now I can get time_create of community_members table?

Answer:

You'll have to switch from using a plain, many-to-many relationship to using an "Association Object", which is basically just taking the association table and giving it a proper class mapping. You'll then define one-to-many relationships to User and Community:

class Membership(db.Model):

__tablename__ = 'community_members'


id = db.Column('id', db.Integer, primary_key=True),

user_id = db.Column(db.Integer, db.ForeignKey('user.id')),

community_id = db.Column(db.Integer, db.ForeignKey('community.id')),

time_create = db.Column(db.DateTime, nullable=False, default=func.now()),


community = db.relationship(Community, backref="memberships")

user = db.relationship(User, backref="memberships")



class Community(db.Model):

__tablename__ = 'community'


id = db.Column(db.Integer, primary_key=True)

name = db.Column(db.String(100), nullable=False, unique=True)

 

But you may only occasionally be interested in the create time; you want the old relationship back! well, you don't want to set up the relationship twice; because sqlalchemy will think that you somehow want two associations; which must mean something different! You can do this by adding in an association proxy.

from sqlalchemy.ext.associationproxy import association_proxy

Community.members = association_proxy("memberships", "user")

User.communities = association_proxy("memberships", "community")

 
 
MyTetra Share v.0.52
Яндекс индекс цитирования