两个表中具有外部id的SQL字段

Question

我正在用sqlalchemy构建一个postgressql数据库。我想知道是否有可能有这样的结构：

两张由人、人和组织组成的桌子。你知道吗

Table - Person - 
person_id, name
1,         Jeffery
2,         Frank

Table - Org -
org_id, name
a,      Pepsi
b,      Microsoft

第三个地址表或个人或组织：

Table - Addresses -
address_id, type_of_id, (either person_id or org_id), addresses
1,          person,     1, "2 Jeffery way"
2,          person,     1, "7 Holland Avenue"
3,          org,        b, "1 Microsoft way"
4,          person,     2, "2 Frank Street"

这对博士后有可能吗？如何将其写入sqlalchemy python代码？你知道吗

更新：

我认为这被称为多态关联，如： Possible to do a MySQL foreign key to one of two possible tables?

Answer 1

我在关系数据库中看到了两种建模方法：

1. 关系表

   CREATE TABLE address_assignment (
      person_id integer REFERENCES person,
      org_id text REFERENCES org,
      address_id integer NOT NULL REFERENCES address,
      CHECK (person_id IS     NULL AND org_id IS NOT NULL
          OR person_id IS NOT NULL AND org_id IS     NULL),
      UNIQUE (person_id, org_id, address_id)
   );
   

   如果不同的实体可以共享一个地址，这个模型将特别有用。

2. “穷人的超类”

   创建一个表，为person和organization实现一个超类，可能包含所有公共属性：

   CREATE TABLE located_entity (
      locent_id INTEGER PRIMARY KEY,
      name text NOT NULL
   );
   

   person和org都获得了located_entity的外键，您还可以通过将located_entity的外键添加到address来实现与address的关系。你知道吗

   如果您通常按地址而不是按人查找人的地址，则可以考虑在located_entity和person之间添加外键，或者在另一个方向添加org人的外键，其检查约束与解决方案1类似。

Answer 2

你的问题由两部分组成：

1. 创建个人和组织表的联合。你知道吗
2. 将实体类型（个人/组织）添加到表中。你知道吗

第一部分可以使用union\u all来完成。有关详细信息，请访问：

• How to concatenate unrelated queries in SqlAlchemy
• http://docs.sqlalchemy.org/en/latest/orm/query.html

第二部分，添加实体类型（个人/组织）可以使用literal\列实现。有关此项的详细信息：

• How do I select literal values in an sqlalchemy query?（请参阅有关在使用postgresql时引用文本的注释）

下面是结合这些元素的简短示例：

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


## Define sample models


class Person(Base):
    __tablename__ = 'person'
    person_id = Column('id', Integer, primary_key=True)
    name = Column('name', String(250))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '[{person_id}] - {name}'.format(person_id=self.person_id, name=self.name)


class Orqanisation(Base):
    __tablename__ = 'organisation'
    organisation_id = Column('id', Integer, primary_key=True)
    name = Column('name', String(250))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return '[{organisation_id}] - {name}'.format(organisation_id=self.organisation_id, name=self.name)


engine = create_engine('sqlite:///')
session = sessionmaker()
session.configure(bind=engine)
ex_ses = session()
Base.metadata.create_all(engine)

## Create sample data

# http://listofrandomnames.com/ for something else then A,B,...
names = ['Virgil', 'Ina', 'Oleta', 'Suzette', 'Usha', 'Ilda', 'Lorean', 'Cinthia', 'Sheba', 'Waneta', 'Donnette']

organisations = ['stackoverflow','Cross validated','Meta stack overflow','Area 51']

# Create persons
for name in names:
    ex_ses.add(Person(name=name))

# Create organisations
for org in organisations:
    ex_ses.add(Orqanisation(name=org))

ex_ses.commit()

# queries
print('Persons:')
for person in ex_ses.query(Person).all():
    print('* ',person)

'''
Persons:
*  [1] - Virgil
*  [2] - Ina
*  [3] - Oleta
*  [4] - Suzette
*  [5] - Usha
*  [6] - Ilda
*  [7] - Lorean
*  [8] - Cinthia
*  [9] - Sheba
*  [10] - Waneta
*  [11] - Donnette
'''

print('Organisations:')
for org in ex_ses.query(Orqanisation).all():
    print('* ',org)

'''
Organisations:
*  [1] - stackoverflow
*  [2] - Cross validated
*  [3] - Meta stack overflow
*  [4] - Area 51
'''

# Query combining tables, adding type of entity

from sqlalchemy.sql.expression import literal_column

persons = ex_ses.query(Person.person_id, Person.name,literal_column("'person'").label('type'))
organisations = ex_ses.query(Orqanisation.organisation_id, Orqanisation.name,literal_column("'Organisation'").label('type'))

print('Persons - Organisations:')
for pers_org in persons.union_all(organisations).all():
    print('* {id} - {name} (type: {type})'.format(id=pers_org[0],name=pers_org[1],type=pers_org[2]))

'''
Persons - Organisations:
* 1 - Virgil (type: person)
* 2 - Ina (type: person)
* 3 - Oleta (type: person)
* 4 - Suzette (type: person)
* 5 - Usha (type: person)
* 6 - Ilda (type: person)
* 7 - Lorean (type: person)
* 8 - Cinthia (type: person)
* 9 - Sheba (type: person)
* 10 - Waneta (type: person)
* 11 - Donnette (type: person)
* 1 - stackoverflow (type: Organisation)
* 2 - Cross validated (type: Organisation)
* 3 - Meta stack overflow (type: Organisation)
* 4 - Area 51 (type: Organisation)
'''

Answer 3

改编自：http://www.duanqu.tech/questions/2898814/sqlalchemy-polymorphic-association 和http://docs.sqlalchemy.org/en/latest/orm/inheritance.html

from sqlalchemy.ext.declarative import as_declarative, declared_attr
from sqlalchemy import create_engine, Integer, Column, \
    String, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy_utils import database_exists, create_database
from sqlalchemy.dialects.postgresql import UUID
import uuid


@as_declarative()
class Base(object):
    # Set the tablenames
    # to the class names
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
    # give an id to each class
    id = Column(UUID, default=lambda: str(uuid.uuid4()), primary_key=True)
# Base = declarative_base()


class EntityInterface(Base):
    discriminator = Column(String)

    __mapper_args__ = {"polymorphic_on": discriminator}


class Address(Base):
    street = Column(String)
    city = Column(String)
    zip = Column(String)
    entity_id = Column(UUID, ForeignKey(EntityInterface.id),
                       default=lambda: str(uuid.uuid4()))
    entity = relationship(EntityInterface)

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


class Person(EntityInterface):
    id = Column(UUID, ForeignKey(EntityInterface.id),
                default=lambda: str(uuid.uuid4()), primary_key=True)
    name = Column(String)
    __mapper_args__ = {"polymorphic_identity": "Person"}


class Organization(EntityInterface):
    id = Column(UUID, ForeignKey(EntityInterface.id),
                default=lambda: str(uuid.uuid4()), primary_key=True)
    name = Column(String)
    __mapper_args__ = {"polymorphic_identity": "Organization"}


engine = create_engine(
    'postgresql://paul_tsc:paul123@localhost/' + "poly_testing",
    echo=False)

# if it doesn't exist, create it
if not database_exists(engine.url):
    create_database(engine.url)

Base.metadata.create_all(engine)
session = Session(engine)

address1 = Address(street='test-1', city="Detroit", zip="56785")
address2 = Address(street='test-2', city="Phoenix", zip="110322")
address3 = Address(street='test-3', city="Washington", zip="432414")

org1 = Organization(name="Org-1 TEST")
org2 = Organization(name="Org-2 TEST")
person1 = Person(name="Person-1 TEST")
person2 = Person(name="Person-2 TEST")

address1.entity = org1
address2.entity = person1
address3.entity = person1

session.add_all([address1, address2, address3])

session.commit()

address3.entity = org2

session.commit()

print("PRINTING, TOTAL = %s" % session.query(Address).count())
for address in session.query(Address):
    print("ADDRESS = %s" % address)