<p>你的问题由两部分组成:</p>
<ol>
<li>创建个人和组织表的联合。你知道吗</li>
<li>将实体类型(个人/组织)添加到表中。你知道吗</li>
</ol>
<p>第一部分可以使用union\u all来完成。有关详细信息,请访问:</p>
<ul>
<li><a href="https://stackoverflow.com/questions/44910975/how-to-concatenate-unrelated-queries-in-sqlalchemy/44916786#44916786">How to concatenate unrelated queries in SqlAlchemy</a></li>
<li><a href="http://docs.sqlalchemy.org/en/latest/orm/query.html" rel="nofollow noreferrer">http://docs.sqlalchemy.org/en/latest/orm/query.html</a></li>
</ul>
<p>第二部分,添加实体类型(个人/组织)可以使用literal\列实现。有关此项的详细信息:</p>
<ul>
<li><a href="https://stackoverflow.com/questions/7533146/how-do-i-select-literal-values-in-an-sqlalchemy-query">How do I select literal values in an sqlalchemy query?</a>(请参阅有关在使用postgresql时引用文本的注释)</li>
</ul>
<p>下面是结合这些元素的简短示例:</p>
<pre><code>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)
'''
</code></pre>