title: Reverse mapping from a table to a model in SQLAlchemy
body: <p>To provide an activity log in my SQLAlchemy-based app, I have a model like this:</p>
  
  <pre><code>class ActivityLog(Base):
      __tablename__ = 'activitylog'
      id = Column(Integer, primary_key=True)
      activity_by_id = Column(Integer, ForeignKey('users.id'), nullable=False)
      activity_by = relation(User, primaryjoin=activity_by_id == User.id)
      activity_at = Column(DateTime, default=datetime.utcnow, nullable=False)
      activity_type = Column(SmallInteger, nullable=False)
  
      target_table = Column(Unicode(20), nullable=False)
      target_id = Column(Integer, nullable=False)
      target_title = Column(Unicode(255), nullable=False)
  </code></pre>
  
  <p>The log contains entries for multiple tables, so I can't use ForeignKey relations. Log entries are made like this:</p>
  
  <pre><code>doc = Document(name=u'mydoc', title=u'My Test Document',
                 created_by=user, edited_by=user)
  session.add(doc)
  session.flush() # See note below
  log = ActivityLog(activity_by=user, activity_type=ACTIVITY_ADD,
                    target_table=Document.__table__.name, target_id=doc.id,
                    target_title=doc.title)
  session.add(log)
  </code></pre>
  
  <p>This leaves me with three problems:</p>
  
  <ol>
  <li><p>I have to flush the session before my <code>doc</code> object gets an <code>id</code>. If I had used a ForeignKey column and a <code>relation</code> mapper, I could have simply called <code>ActivityLog(target=doc)</code> and let SQLAlchemy do the work. Is there any way to work around needing to flush by hand?</p></li>
  <li><p>The <code>target_table</code> parameter is too verbose. I suppose I could solve this with a <code>target</code> property setter in ActivityLog that automatically retrieves the table name and id from a given instance.</p></li>
  <li><p>Biggest of all, I'm not sure how to retrieve a model instance from the database. Given an ActivityLog instance <code>log</code>, calling <code>self.session.query(log.target_table).get(log.target_id)</code> does not work, as <code>query()</code> expects a model as parameter.</p></li>
  </ol>
  
  <p>One workaround appears to be to use polymorphism and derive all my models from a base model which ActivityLog recognises. Something like this:</p>
  
  <pre><code>class Entity(Base):
      __tablename__ = 'entities'
      id = Column(Integer, primary_key=True)
      title = Column(Unicode(255), nullable=False)
      edited_at = Column(DateTime, onupdate=datetime.utcnow, nullable=False)
      entity_type = Column(Unicode(20), nullable=False)
      __mapper_args__ = {'polymorphic_on': entity_type}
  
  class Document(Entity):
      __tablename__ = 'documents'
      __mapper_args__ = {'polymorphic_identity': 'document'}
      body = Column(UnicodeText, nullable=False)
  
  class ActivityLog(Base):
      __tablename__ = 'activitylog'
      id = Column(Integer, primary_key=True)
      ...
      target_id = Column(Integer, ForeignKey('entities.id'), nullable=False)
      target = relation(Entity)
  </code></pre>
  
  <p>If I do this, <code>ActivityLog(...).target</code> will give me a Document instance when it refers to a Document, but I'm not sure it's worth the overhead of having two tables for everything. Should I go ahead and do it this way?</p>