SQLAlchemy - How to map ManyToMany using autoloaded association tables

I am autoloading an MSSQL db. There a开发者_JS百科re a few ManyToMany assoc tables. I'm not sure how to map each side. Here's a typical example of how they look in the db:

Table: tbUsersToGroups
PK: ID_UserToGroup
FK: User_ID
FK: Group_ID

So I can successfully autoload that assoc table and the Users and Groups tables per below, but everything I've tried to map the sides has failed.

class UserToGroup(Base):
    __tablename__ = 'tbUsersToGroups'
    __table_args__ = {'autoload':True,'extend_existing':True,'schema':'dbo'}

and

class User(Base):
    __tablename__ = 'tbUsers'
    __table_args__ = {'autoload':True,'schema':'dbo'}

and

class Group(Base):
    __tablename__ = 'tbGoups'
    __table_args__ = {'autoload':True,'schema':'dbo'}

Any help would be great.

You have mapped the association table to a class. It's very unusual and probably going to cause you some pain to combine an association object with a many-to-many relationship. If the association table doesn't have any other columns of interest, you can drop the mapping and use a many-to-many relationship:

Edit: I missed the fact that you're doing per-table reflection, rather than full database reflection; For a many-to-many, you have to tell sqlalchemy about the table, but without mapping it to a class:

user_to_groups_table = sqlalchemy.Table('tbUsersToGroups', Base.metadata,
                   autoload=True,
                   extend_existing=True
                   schema='dbo')

class User(Base):
    __tablename__ = 'tbUsers'
    __table_args__ = {'autoload':True,'schema':'dbo'}

class Group(Base):
    __tablename__ = 'tbGoups'
    __table_args__ = {'autoload':True,'schema':'dbo'}
    users = relationship(User, secondary=user_to_groups_table, backref="groups")

If there are columns in the association table that you want to have an object-oriented access to, you should use two One-To-Many relationships to relate the three classes; Optionally, you can also use an association proxy to get a convenient many-to-many property for when you only need to use those extra columns occasionally (and they have defaults):

from sqlalchemy.ext.associationproxy import association_proxy
class UserToGroup(Base):
    __tablename__ = 'tbUsersToGroups'
    __table_args__ = {'autoload':True,'extend_existing':True,'schema':'dbo'}

class User(Base):
    __tablename__ = 'tbUsers'
    __table_args__ = {'autoload':True,'schema':'dbo'}
    usergroups = relationship(UserToGroup, backref="user")
    groups = association_proxy("usergroups", "group")

class Group(Base):
    __tablename__ = 'tbGoups'
    __table_args__ = {'autoload':True,'schema':'dbo'}
    usergroups = relationship(UserToGroup, backref="group")
    users = association_proxy("usergroups", "user")