SQLAlchemy 多态进阶（__mapper_args__ ）、多对多标签

什么是polymorphic

熟悉SQLAlchemy的人往往知道polymorphic(多态)的模型定义，如果你恰好不熟悉SQLAlchemy，这里简单的举一个例子：

class Employee(Base):  
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

这里定义了雇员Employee 模型，指定type字段为多态所在字段，并且对于这个模型，当type字段为'employee'时，即为一个雇员

再看下面这两个继承之后的模型

class Engineer(Employee):  
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):  
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

这里又定义了两个模型，Engineer，Manager，

并对应了两张表，这两张表的结构除了有少许不同，类似的，polymorphic_identity指定了这两种模型对应的type字段值，

在上面的基础上，可以提出的问题：

1. 可不可以完全在一张表上实现这样的多态？
2. 这样的模型可以用作M2M关系吗？

两者的答案显然是肯定的。 对于第一个问题，只需要使得后两者的__tablename__ = None，并且不指定额外的字段即可。

第二个问题，即这几天我的重构的探索

如何设置多对多模型

对于一个多对多的关系表，按照 SQLAlchemy 文档：

association_table = Table('association', Base.metadata,  
    Column('left_id', Integer, ForeignKey('left.id')),
    Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):  
    __tablename__ = 'left'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",
                    secondary=association_table)

class Child(Base):  
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)

(虽然我们的基本不会按照SQLAlchemy那样定义ForeignKey了，万恶的ForeignKey。。)

关键在于应当有第三张表，存放M2M的关系。上面的association，就是这样的一张M2M表，有两个字段left_id和right_id

而且显然的，我们可以轻松地想象出取出M2M关系的SQL：

select left.id,right.id from left join association on left.id=association.left_id  
join right on association.right_id=right.id

是借助association实现两个表的JOIN关系

SQLAlchemy 的对应操作这里就不赘述了，大家看文档吧咩哈哈

M2M和多态

此次重构遇到的问题就是：如果我们的M2M的关系，如果是在多态上进行的，例如上面的Child，如果我不仅仅有Child，还分Boy和Girl，如何在这一张association_table进行控制呢？ 上面代码先稍作修改：

class Association(Base):  
    left_id = Column('left_id', Integer, ForeignKey('left.id')),
    right_id = Column('right_id', Integer, ForeignKey('right.id'))
    gender = Column('gender', Boolean)
    __mapper_args__ = {"polymorphic_on": gender}

增加了gender字段，并且增加了多态声明__mapper_args__ 我们先假设一下这样的SQL该怎么写吧，实际上是很简单的哈：

select left.id,right.id from left join association on (left.id=association.left_id and association.gender)  
join right on association.right_id=right.id

join的时候额外加一个字段即可。

如何让SQLalchemy可以生成出这样的SQL，并且还自动进行例如增删查改的SQL声明呢？

SQLAlchemy同样给出了对应的 样例

我基于这个样例做了一定的修改：

1. 完全不用ForeignKey声明。这一点很容易，from sqlalchemy.orm import foreign，用foreign函数包一下对应的字段，就可以当成外键来用
2. 样例中的Address声明了association = relationship("AddressAssociation", backref="addresses")，这样使得AddressAssociation有了一个addresses的反向引用(backref)，在实际的M2M模型设计中，考虑到是跨模块的模型映射，为了方便修改和维护，没有修改M2M左边的这个M，因此在AddressAssociation中动态声明了一个addresses
3. 增加relationship cascade属性，以进行删除操作

对应的diff如下(稍微修改了字段名），稍后有完整代码：

--- origin.py    2016-10-13 11:28:57.000000000 +0800  
+++ target.py    2016-10-13 11:29:44.000000000 +0800  
@@ -1,80 +1,84 @@  
 from sqlalchemy.ext.declarative import as_declarative, declared_attr
 from sqlalchemy import create_engine, Integer, Column, \
-    String, ForeignKey  
-from sqlalchemy.orm import Session, relationship, backref  
+    String, and_  
+from sqlalchemy.orm import Session, foreign, relationship, backref  
 from sqlalchemy.ext.associationproxy import association_proxy


 class AddressAssociation(Base):
     """Associates a collection of Address objects
     with a particular parent.

     """
     __tablename__ = "address_association"
-  
-    discriminator = Column(String)  
+    addr_id = Column(Integer,  
+                     primary_key=True,  
+                     )  
+    order_id = Column(Integer,  
+                      primary_key=True,  
+                      )  
+    discriminator = Column(String, primary_key=True)  
     """Refers to the type of parent."""

     __mapper_args__ = {"polymorphic_on": discriminator}


 class Address(Base):
     """The Address class.

     This represents all address records in a
     single table.

     """
-    association_id = Column(Integer, ForeignKey("address_association.id"))  
+    id = Column(Integer, primary_key=True)  
     street = Column(String)
     city = Column(String)
     zip = Column(String)
-    association = relationship("AddressAssociation", backref="addresses")  
-  
-    parent = association_proxy("association", "parent")

     def __repr__(self):
         return "%s(street=%r, city=%r, zip=%r)" % \
             (self.__class__.__name__, self.street,
              self.city, self.zip)


 class HasAddresses(object):
     """HasAddresses mixin, creates a relationship to
     the address_association table for each parent.

     """
     @declared_attr
-    def address_association_id(cls):  
-        return Column(Integer, ForeignKey("address_association.id"))  
-  
-    @declared_attr  
     def address_association(cls):
         name = cls.__name__
         discriminator = name.lower()

         assoc_cls = type(
             "%sAddressAssociation" % name,
                         (AddressAssociation, ),
             dict(
                 __tablename__=None,
                 __mapper_args__={
                     "polymorphic_identity": discriminator
-                }  
-            )  
+                },  
+                addresses=relationship(  
+                    Address,  
+                    primaryjoin="Address.idforeign({assoc_cls_name}.addr_id)".format(assoc_cls_name=assoc_cls_name))  
+                        )  
         )

         cls.addresses = association_proxy(
             "address_association", "addresses",
             creator=lambda addresses: assoc_cls(addresses=addresses)
         )
         return relationship(assoc_cls,
-                            backref=backref("parent", uselist=False))  
+                            primaryjoin=and_(foreign(assoc_cls.addr_id)  Address.id,  
+                                             foreign(assoc_cls.order_id) == cls.id),  
+                            cascade="save-update, merge, delete, delete-orphan",  
+                            )


 class Customer(HasAddresses, Base):
     name = Column(String)


 class Supplier(HasAddresses, Base):
     company_name = Column(String)

此后就可以通过Customer.addresses.append等操作M2M了