CODEWITHSUNDEEP
pythonsqlalchemy
3van
3van

Reputation: 21

sqlalchemy mapper event issue

Ii defined the following classes in sqlalchemy:

class User(Base):
    __tablename__ = 'user'
    userId = Column(Integer, primary_key=True)
    email = Column(String(60), unique=True,nullable=False)
    userName = Column(Unicode(16),unique=True)
    _password = Column('password', Unicode(60))
    groups = relationship("Group",secondary='userGroup') 
    profile = relationship("UserProfile",uselist=False,backref='user') 

class Group(Base):
        __tablename__ = 'group'
        groupId = Column(Integer,
                       Sequence('group_id_seq', optional=True),
                       primary_key=True)
        groupName = Column(Unicode(255), unique=True)

UserGroupTable = Table('userGroup', Base.metadata,
        Column('userId', Integer, ForeignKey(User.userId)),
        Column('groupId', Integer, ForeignKey(Group.groupId))
    )


class UserProfile(Base):
    __tablename__='userProfile'
    userId = Column(Integer, ForeignKey('user.userId'), primary_key=True)
    dateJoin = Column(Date,nullable=False,default=func.current_date())
    gender = Column(Integer)

The user is in a many-to-many relationship with group and when I initialised the tables, I added two groups 'user' and 'admin' as below. The problem is that when I use the mapper event to add a user to 'user' group when insert, it does not work:

def before_insert_user_listener(mapper,connection,target):
    session=DBSession()
    ug=session.query(Group).filter(Group.groupName=='user').first()
    target.groups.append(ug)    
    target.profile=UserProfile()
    log.debug(">>>>>")

def initialize_sql(engine):
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine
    Base.metadata.create_all(engine)
    try:
        session = DBSession()
        #event register
        event.listen(User,'before_insert',before_insert_user_listener)
        group1 = Group('admin')
        group2 = Group('user') 
        session.add(group1)
        session.add(group2)           
        transaction.commit()
    except IntegrityError:
        pass

When i try to add a new user, the user is added, and I can see the log print from the event listener, but the user is not added to the 'user' group. I also tried the 'after_insert' event, but it also didn't work.

Edit: I add the userProfile table which is a one-to-one relationship to user,and a user profile can be create with default join date in above before_insert event. add user to group still not working though.

What did I miss?

Thanks!

Upvotes: 0

Views: 1219

Answers (1)

van
van

Reputation: 77082

As the extract from the before_insert documentation suggests:

Column-based attributes can be modified within this method which will result in the new value being inserted. However no changes to the overall flush plan can be made, and manipulation of the Session will not have the desired effect. To manipulate the Session within an extension, use SessionExtension.

from which I conclude that one cannot add new objects to the session which is being commited/flushed or add/set relationships.

I guess the better way would be to listen to before_commit or before_flush session event, check all new User objects and adding them to the User group. For example:

def my_before_commit(session):
    g=session.query(Group).filter(Group.groupName=='user').one()
    for target in session:
        if isinstance(target, User) and target in session.new:
            target.groups.append(ug)

Upvotes: 1

Related Questions