What's the proper war to describe an associative object by SQLalchemy the declarative way

Question

Hello.

I'm looking for a way to describe an associative object the declarative way. Beyond storing the foreign keys in the association table, I need to store information like the creation date of the association.

Today, my model looks like that :

# Define the User class
class User(Base):
    __tablename__ = 'users'

    # Define User fields
    id = schema.Column(types.Integer(unsigned=True),
        schema.Sequence('users_seq_id', optional=True), primary_key=True)
    password = schema.Column(types.Unicode(64), nullable=False)

# Define the UserSubset class
class UserSubset(Base):
    __tablename__ = 'subsets'

    # Define UserSubset fields
    id = schema.Column(types.Integer(unsigned=True),
        schema.Sequence('subsets_seq_id', optional=True), primary_key=True)
    some_short_description = schema.Column(types.Unicode(50), nullable=False)

# Define the subset memberships table
subset_memberships = schema.Table('group_memberships', Base.metadata,
    schema.Column('user_id', types.Integer(unsigned=True), ForeignKey('users.id')),
    schema.Column('subset_id', types.Integer(unsigned=True), ForeignKey('subsets.id')),
    schema.Column('created', types.DateTime(), default=now, nullable=False),
)

Can I connect everything in an associative object ? Or should I change stop using the declarative way ?

Answer 1

As you can see in the manual, configuring a one to many relation is really simple:

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    addresses = relation("Address", backref="user")

class Address(Base):
    __tablename__ = 'addresses'

    id = Column(Integer, primary_key=True)
    email = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))

Many to many relations isn't much harder:

There’s nothing special about many-to-many with declarative. The secondary argument to relation() still requires a Table object, not a declarative class. The Table should share the same MetaData object used by the declarative base:

 keywords = Table('keywords', Base.metadata,
                     Column('author_id', Integer, ForeignKey('authors.id')),
                     Column('keyword_id', Integer, ForeignKey('keywords.id'))
             )

 class Author(Base):
     __tablename__ = 'authors'
     id = Column(Integer, primary_key=True)
     keywords = relation("Keyword", secondary=keywords)

You should generally not map a class and also specify its table in a many-to-many relation, since the ORM may issue duplicate INSERT and DELETE statements.

Anyway, what you seem to be doing might be better served with inheritance. Of course, there can be complex table relations that will be a pathological case for the declarative way, but this doesn't seem to be one of them.

_{One more thing, code comments should state what the following code does ans why, not how it does it. Having a # Define the User class comment is almost like having a line of code saying a = 1 # assing value 1 to variable "a".}

Answer 2

What you are using at the moment is just a Many-to-Many-relation. How to work with association objects is described in the docs.

There is also an extension called associationproxy which simplifies the relation.