sqlalchemy - SQLAlchemy：根据列值有条件地在表中创建关系

Question

我是 SQLAlchemy 的新手。我一直在尝试创建一个数据库，其中包含一个包含核心信息的主表，例如：

Actor:
name   dob     fun_fact             extra_info
MD     7/4/75 "likes water skiing"  stuntman
DJ     7/4/76 "really strong"       charity
MBJ    7/4/76 "very friendly"       stuntman
.....

根据该列extra_info，我希望有单独的表，其中包含与 的值相关的数据extra_info。

与 MD 和 MBJ 相关的数据是与他们合作的特技演员：

Stuntmen:
id   stuntman   number_of_stunts  number_injuries
MD   JJ         49                3
MBJ  JA         20                1
....

我还有一个单独的表格，用于存储完全不同类型的数据——演员与之合作的慈善机构（MD 和 MBJ 不与慈善机构合作，所以这与他们无关）。

Charity_work
id   charity_name         funds_raised
DJ   "homes for puppies"  "$5000"
....

为了制作这种数据库，我正在尝试使用表类继承：

class Actor(Base):
    __tablename__ = 'actors'
    id = Column(Integer, primary_key=True)
    name = Column(Text, nullable=False)
    dob = Column(Integer, nullable=False)
    fun_fact = Column(Text, nullable=False)
    extra_info = Column(String(50))
    file_id = Column(ForeignKey('movies_industry.id'))
    __mapper_args__ = {
        'polymorphic_identity':'generic',
        'polymorphic_on':extra_info
    }
    def __init__(self,name,dob,fun_fact,extra_info):
        self.name = name
        self.dob = dob
        self.fun_fact = fun_fact
        self.extra_info = extra_info

class Stuntman(Actor):
    stuntman = Column(Text, nullable=False)
    number_of_stunts = Column(Integer, nullable=False)
    number_of_injuries = Column(Integer, nullable=False)
    __mapper_args__ = {
        'polymorphic_identity':'stuntman'
    }
    __tablename__ = 'stuntmen'
    id = Column(None, ForeignKey('actors.id'), primary_key=True)
    def __init__(self, stuntman,number_of_stunts,number_of_injuries):
        self.stuntman = stuntman
        self.number_of_stunts= number_of_stunts
        self.number_of_injuries=number_of_injuries

class Charity(Actor):
    charity_name = Column(Text, nullable=False)
    funds_raised = Column(Text, nullable=False)
    __mapper_args__ = {
        'polymorphic_identity':'charity'
    }
    __tablename__ = 'charities'
    id = Column(None, ForeignKey('actors.id'), primary_key=True)
    def __init__(self, charity_name, funds_raised):
        self.charity_name = charity_name
        self.funds_raised = funds_raised

完成后 - 我必须从不同来源读取数据并将数据添加到数据库中：

df=pd.read_csv('actors_stuntmen.csv')
name   dob     fun_fact             name   number_stunts   number_injuries 
MD     7/4/75 "likes water skiing"  ....
DJ     7/4/76 "really strong"       ....
MBJ    7/4/76 "very friendly"       ....
.....


movie_industry=Movie_Industry("America") #ignore this line
for row in df.itertuples():
    actor=Actor(row[1],row[2],row[3],'stuntman')
    stuntman=Stuntman(row[4],row[5],row[6])
    movies_industry.actors.append(actor)

但是，当我尝试将此数据提交到数据库时，会出现错误：

session.add(movie_industry)
session.add(actor)
session.add(stuntman)
session.commit()

    /path/lib/python3.7/site-packages/sqlalchemy/orm/mapper.py:1606: SAWarning: Flushing object <Loci at 0x2b523ea38750> with incompatible polymorphic identity 'state'; the object may not refresh and/or load correctly (this warning may be suppressed after 10 occurrences)
  (state_str(extra_info), dict_[polymorphic_key]),

[SQL: INSERT INTO loci (name, dob, fun_fact, extra_info) VALUES (?, ?, ?, ?)]
[parameters: (None, None, None, 'stuntman')]

总的来说，我想要一个数据库，我可以在其中添加数据并基于列的值extra_info- 将必要的信息添加到适当的表中，并在这些表的行之间建立适当的链接 - 见下文：

希望得到一些关于我哪里出错的指导 - 或者是否有一种我不知道的方法？

标签： sqlalchemysingle-table-inheritance