sqlalchemy通用外键（像在django ORM中） [英] sqlalchemy generic foreign key (like in django ORM)
问题描述
谢谢！
我最常使用的最简单的模式是，每个关系实际上都有单独的注释表。这可能看起来可怕，但它并不会引入任何其他代码，而不是使用任何其他方法 - 这些表是自动创建的，模型使用模式 Post.Comment ， Project.Comment 等。注释的定义保持在一个地方。从参考的角度来看，这种方法是最简单和最有效的方式，以及最多的DBA友好，因为不同种类的评论被保存在自己的表中，可以分别进行大小。
另一种使用的模式是单个注释表，但不同的关联表。这种模式提供了一个用例，您可能希望一次将注释链接到多种类型的对象（如同时发布和项目）。这种模式仍然是有效的。
第三，有多态关联表。此模式使用固定数量的表来表示集合和相关类，而不牺牲引用完整性。尽管它不像以前的两种方法那样简单，但仍然保持参照完整性，这种模式尝试来最接近Django风格的通用外键。
模仿ROR / Django使用的模式，其中没有使用真正的外键，并且使用应用程序逻辑进行匹配，也是可能的。
前三种模式如SQLAlchemy分布中的现代形式在examples / generic_associations /下。
ROR / Django模式，由于经常被询问，我还将添加到SQLAlchemy示例，即使我不喜欢它。我使用的方法与Django做的不一样，因为他们似乎使用contenttypes表来跟踪类型，这对我来说似乎是多余的，但是整数列的一般想法指向基于鉴别器列的任何数量的表。这里是：
从sqlalchemy.ext.declarative import declarative_base，declared_attr
从sqlalchemy import create_engine，Integer，Column ，\
String，and_
from sqlalchemy.orm import会话，关系，外部，远程，backref
从sqlalchemy导入事件
class Base （object）：
提供自动表名称
和代理主键列的基类
@declared_attr
def __tablename __（cls）：
return cls .__ name __。lower（）
id =列（Integer，primary_key = True）
Base = declarative_base（cls = Base）
class Address（Base）：
地址类
这表示
单表中的所有地址记录
street =列（String）
city =列（String）
zip =列（String）
discriminator =列（String）
指t
parent_id =列（整数）
指父级的主键。
这可以指任何表。
@property
def parent（self）：
通过选择
提供对父的Python访问适当的关系。
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，为每个父项创建一个关系到
的address_association表。
@ 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 .parent_id）），
Address.discriminator == discriminator
），
backref = backref（
parent_％s％discriminator，
primaryjoin = remote（class_ $ id
@ event.listens_for（class_.addresses，append）
def append_address（target，value，initiator ）：
value.discriminator = discriminator
class Customer（HasAd
name =列（String）
class供应商（HasAddresses，Base）：
company_name =列（String）
引擎= create_engine（'sqlite：//'，echo = True）
Base.metadata.create_all（引擎）
会话=会话（引擎）
会话。 add_all（[
Customer（
name ='customer 1'，
addresses = [
Address（
street ='123 anywhere street'，
city =纽约，
zip =10110），
地址（
street = '40主街'，
city =旧金山，
供应商（
company_name =Ace Hammers，
地址= [
地址（$ b $）
） b street ='2569 west elm'，
city =Detroit，
zip =56785）
]
），
]）
session.commit（）
在session.query（客户）中的客户：
在customer.addresses中的地址：
print（address）
print（address.parent）
Does sqlalchemy have something like django's GenericForeignKey? And is it right to use generic foreign fields.
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?
Thanks!
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.
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.
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.
The first three patterns are illustrated in modern form in the SQLAlchemy distribution under examples/generic_associations/.
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屋！
