SQLAlchemy 多态进阶

SQLAlchemy 多态进阶

手机里随便找了一张照片做题图( less 怎么能is more呢...less is better then more...

最近在做模型设计的重构,之前的M2O的设计有些不符合需求了,平添了很多工作量,产品最初的设计和后来改来改去的需求明显产生了矛盾,重构成M2M的过程中,学到了很多SQLAlchemy的知识,因此在这里记录一下

什么是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了

(哦...知乎不支持diff的code?

最后的感觉就是:在对M2M进行操作的时候, 很符合直觉,但是实际上的模型定义,晦涩难懂....

可以掀桌了好把

编辑于 2017-03-29

文章被以下专栏收录

    微信公众号同名,欢迎投稿。全平台约20万开发者关注,会员来自全球十多个国家和地区,拥有十多个线上线下技术社群,向本专栏投稿即默认发布到Python中文社区全平台。GitHub:github.com/pycn