sqlalchemy 通用外键(如在 django ORM 中) [英] sqlalchemy generic foreign key (like in django ORM)
问题描述
sqlalchemy 有类似 django 的 GenericForeignKey 的东西吗?以及使用通用的外部字段是否正确.
Does sqlalchemy have something like django's GenericForeignKey? And is it right to use generic foreign fields.
我的问题是:我有几个模型(例如，发布、项目、空缺，没有什么特别的)，我想为每个模型添加评论.而且我只想使用一种 Comment 模型.值得吗?还是应该使用 PostComment、ProjectComment 等?两种方式的优缺点?
My problem is: I have several models (for example, Post, Project, Vacancy, nothing special there) and I want to add comments to each of them. And I want to use only one Comment model. Does it worth to? Or should I use PostComment, ProjectComment etc.? Pros/cons of both ways?
谢谢！
推荐答案
我最常使用的最简单的模式是实际上每个关系都有单独的 Comment 表.这乍一看似乎很可怕，但与使用任何其他方法相比，它不会产生任何额外的代码 - 表格是自动创建的，并且使用模式 Post.Comment, 引用模型>Project.Comment 等.Comment 的定义在一处维护.从引用的角度来看，这种方法是最简单有效的，也是对 DBA 最友好的方法，因为不同类型的评论保存在它们自己的表中，可以单独调整大小.
The simplest pattern which I use most often is that you actually have separate Comment tables for each relationship. This may seem frightening at first, but it doesn't incur any additional code versus using any other approach - the tables are created automatically, and the models are referred to using the pattern Post.Comment, Project.Comment, etc. The definition of Comment is maintained in one place. This approach from a referential point of view is the most simple and efficient, as well as the most DBA friendly as different kinds of Comments are kept in their own tables which can be sized individually.
另一种使用的模式是单个评论表，但不同的关联表.此模式提供了一个用例，您可能希望评论一次链接到多个对象(例如同时链接到一个帖子和一个项目).这种模式仍然相当有效.
Another pattern to use is a single Comment table, but distinct association tables. This pattern offers the use case that you might want a Comment linked to more than one kind of object at a time (like a Post and a Project at the same time). This pattern is still reasonably efficient.
第三，多态关联表.此模式使用固定数量的表来表示集合和相关类，而不会牺牲参照完整性.这种模式试图与 Django 风格的通用外键"最接近.在保持参照完整性的同时，虽然不像前两种方法那么简单.
Thirdly, there's the polymorphic association table. This pattern uses a fixed number of tables to represent the collections and the related class without sacrificing referential integrity. This pattern tries to come the closest to the Django-style "generic foreign key" while still maintaining referential integrity, though it's not as simple as the previous two approaches.
模仿 ROR/Django 使用的模式，其中没有使用真正的外键并且使用应用程序逻辑匹配行，也是可能的.
Imitating the pattern used by ROR/Django, where there are no real foreign keys used and rows are matched using application logic, is also possible.
前三种模式在 examples 下的 SQLAlchemy 发行版中以现代形式进行了说明/generic_associations/.
ROR/Django 模式，因为它经常被问到，我也会添加到 SQLAlchemy 示例中，即使我不太喜欢它.我使用的方法与 Django 所做的并不完全相同，因为它们似乎使用了内容类型".table 来跟踪类型，这对我来说似乎有点多余，但是存在基于鉴别器列指向任意数量表的整数列的一般概念.这是:
The ROR/Django pattern, since it gets asked about so often, I will also add to the SQLAlchemy examples, even though I don't like it much. The approach I'm using is not exactly the same as what Django does as they seem to make use of a "contenttypes" table to keep track of types, that seems kind of superfluous to me, but the general idea of an integer column that points to any number of tables based on a discriminator column is present. Here it is:
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import create_engine, Integer, Column,
String, and_
from sqlalchemy.orm import Session, relationship, foreign, remote, backref
from sqlalchemy import event
class Base(object):
"""Base class which provides automated table name
and surrogate primary key column.
"""
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
id = Column(Integer, primary_key=True)
Base = declarative_base(cls=Base)
class Address(Base):
"""The Address class.
This represents all address records in a
single table.
"""
street = Column(String)
city = Column(String)
zip = Column(String)
discriminator = Column(String)
"""Refers to the type of parent."""
parent_id = Column(Integer)
"""Refers to the primary key of the parent.
This could refer to any table.
"""
@property
def parent(self):
"""Provides in-Python access to the "parent" by choosing
the appropriate relationship.
"""
return getattr(self, "parent_%s" % self.discriminator)
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.
"""
@event.listens_for(HasAddresses, "mapper_configured", propagate=True)
def setup_listener(mapper, class_):
name = class_.__name__
discriminator = name.lower()
class_.addresses = relationship(Address,
primaryjoin=and_(
class_.id == foreign(remote(Address.parent_id)),
Address.discriminator == discriminator
),
backref=backref(
"parent_%s" % discriminator,
primaryjoin=remote(class_.id) == foreign(Address.parent_id)
)
)
@event.listens_for(class_.addresses, "append")
def append_address(target, value, initiator):
value.discriminator = discriminator
class Customer(HasAddresses, Base):
name = Column(String)
class Supplier(HasAddresses, Base):
company_name = Column(String)
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
session.add_all([
Customer(
name='customer 1',
addresses=[
Address(
street='123 anywhere street',
city="New York",
zip="10110"),
Address(
street='40 main street',
city="San Francisco",
zip="95732")
]
),
Supplier(
company_name="Ace Hammers",
addresses=[
Address(
street='2569 west elm',
city="Detroit",
zip="56785")
]
),
])
session.commit()
for customer in session.query(Customer):
for address in customer.addresses:
print(address)
print(address.parent)
这篇关于sqlalchemy 通用外键(如在 django ORM 中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
