Many to many relationship with a composite key on SQLAlchemy

Question

Let's say I have the following model:

class Molecule(Base):
   db = Column(Integer, primary_key=True)
   id = Column(Integer, primary_key=True)
   data = Column(Integer)

class Atom(Base):
   id = Column(Integer, primary_key=True)
   weight = Column(Integer)

And I want to establish a many-to-many relationship between Molecule and Atom, what would be the best way to do it? Notice that the primary key of Molecule is composite.

Thanks

Answer 1

many-to-many association tables should be defined like this:

molecule2atom = Table(
  'molecule2atom',
  Base.metadata, 
  Column('molecule_db', Integer),
  Column('molecule_id', Integer),
  Column('atom_id', Integer, ForeignKey('atom.id')),
  ForeignKeyConstraint( 
    ('molecule_db', 'molecule_id'),
    ('molecule.db', 'molecule.id')  ),
)

And add the relatiohship to one of the models as usual, for example, in Class Atom add:

molecules = relationship("Molecule", secondary=molecule2atom, backref="atoms")

Answer 2

I liked the solution given here better - composite key many to many