SQLAlchemy ManyToMany secondary table with additional fields

by

You will 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")

More Related Contents:

  1. flask-sqlalchemy or sqlalchemy
  2. SQLAlchemy ordering by count on a many to many relationship
  3. Flask SQLAlchemy query, specify column names
  4. AttributeError: can’t set attribute when connecting to sqlite database with flask-sqlalchemy
  5. Case Insensitive Flask-SQLAlchemy Query
  6. Flask-SQLalchemy update a row’s information
  7. SQL Alchemy ORM returning a single column, how to avoid common post processing
  8. Efficiently updating database using SQLAlchemy ORM
  9. How to build a flask application around an already existing database?
  10. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  11. Bulk insert with SQLAlchemy ORM
  12. How can I verify Column data types in the SQLAlchemy ORM?
  13. Updates to JSON field don’t persist to DB
  14. Flask sqlalchemy many-to-many insert data
  15. How to delete a record by id in Flask-SQLAlchemy
  16. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  17. SQLAlchemy: engine, connection and session difference
  18. sqlalchemy: create relations but without foreign key constraint in db?
  19. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  20. SQLAlchemy: how to filter date field?
  21. Getting first row from sqlalchemy
  22. ImportError: No module named sqlalchemy
  23. How to update sqlalchemy orm object by a python dict
  24. Flask-SQLAlchemy Constructor
  25. Using SQLAlchemy models in and out of Flask
  26. Is possible to mapping view with class using mapper in SqlAlchemy?
  27. Retrieve query results as dict in SQLAlchemy
  28. SQLALchemy dynamic filter_by
  29. How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?
  30. flask sqlalchemy query with keyword as variable

Leave a Comment