Python SQLAlchemy after_insert MapperEvent

0
votes

So I am new to SQLAlchemy, and I would like after a new User object is created that a Permissions object is created by default. Reading the documentation it seems that after_insert mapper event is what I should be using. The problem is that the event listener is called before the object is actually committed.

Is there a way to have the Permissions object at least put in possibly a transaction or on the list of objects to be created after the user object is actually committed. 

class Users():
  __tablename__ = 'users'
  user_id = Column(String(36), primary_key=True, nullable=False)
  .......

class Permissions():
  __tablename__ = 'permissions'
  user_id = Column(String(36), ForeignKey('users.user_id'), primary_key=True,
                   nullable=False)
   ..........


@event.listens_for(Users, "after_insert)
def create_permissions(mapper, connection, user):
    connection.execute(Permissions.__table__.insert(user_id=user.user_id))

IntegrityError: (IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails

Ideally I would like to do this without a Database Trigger on the Users table.

2
pythonmysqlsqlalchemy

2 Answers

3
votes

You do not need to attach session to ORM object. Just use object_session function from sqlalchemy.orm module like following:

from sqlalchemy.orm import object_session

@event.listens_for(Users, 'after_insert')
def create_permissions(mapper, connection, user):
    object_session(user).add(Permissions(user_id=user.user_id))
1
votes

So by changing my class defintions around, and attaching the session object to each class I am able to add a new Permissions object at time after_insert is signaled. And the proper sequences of commits take place.

class Users():
  __tablename__ = 'users'
  user_id = Column(String(36), primary_key=True, nullable=False)
  ............

class Permissions():
  __tablename__ = 'permissions'
  user_id = Column(String(36), ForeignKey('users.user_id'), primary_key=True,
                   nullable=False)
   ............


@event.listens_for(Users, "after_insert")
def create_permissions(mapper, connection, user):
    user.session.add(Permissions(user_id=user.user_id))