SQLAlchemy Mapping problem

Question

Dear Everyone, I am trying to sqlalchemy to correctly map my data. Note that a unified group is basically a group of groups. (One unifiedGroup maps to many groups but each group can only map to one ug).

So basically this is the definition of my unifiedGroups:

 CREATE TABLE `unifiedGroups` (     
    `ugID` INT AUTO_INCREMENT,
    `gID` INT NOT NULL,
    PRIMARY KEY(`ugID`, `gID`),
    KEY( `gID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
;

Note that each row is a ugID, gID tuple. ( I do not know before hand how many gID is per ugID so this is probably the most sensible and simplest method).

Definition for my UnifiedGroup class

class UnifiedGroup(object):
    """UnifiedProduct behaves very much like a group
    """
    def __init__(self, ugID):
        self.ugID=ugID

        #Added by mapping
        self.groups=False

    def __str__(self):
        return '<%s:%s>' % (self.ugID, ','.join( [g for g in self.groups]))

These are my mapping tables:

tb_groupsInfo = Table( 'groupsInfo', metadata, 
    Column('gID', Integer, primary_key=True), 
    Column('gName', String(128)),
)

tb_unifiedGroups = Table( 'unifiedGroups', metadata, 
    Column('ugID', Integer, primary_key=True), 
    Column('gID', Integer, ForeignKey('groupsInfo.gID')),
)

My mapper maps in the following manner:

mapper( UnifiedGroup, tb_unifiedGroups, properties={
    'groups': relation( Group, backref='unifiedGroup')
})

However, when I tried to do groupInstance.unifiedGroup, I am getting an empty list [], while groupInstance.unifiedGroup.groups returns me an error: AttributeError: 'InstrumentedList' object has no attribute 'groups'

Traceback (most recent call last):
  File "Mapping.py", line 119, in <module>
    print p.group.unifiedGroup.groups
AttributeError: 'InstrumentedList' object has no attribute 'groups'

What is wrong?

Answer 1

Like I told you on IRC, if you want a one-to-many relationship between unifiedgroups and groups, you will need to have the foreign key be in the groups table. It simply doesn't make sense any other way.