SqlAlchemy:将继承的类型从一个转换为另一个

Let's say I have two different types both on the same database table (single table inheritance):

假设我在同一个数据库表上有两个不同的类型(单表继承):

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String, nullable = False)
    discriminator = db.Column('type', String)
    __mapper_args__ = {'polymorphic_on': discriminator}

class Manager(Employee):
    __mapper_args__ = {'polymorphic_identity': 'manager'}
    division = db.Column(db.String, nullable = False)
    role = db.Column(db.String, nullable = False)

class Worker(Employee):
    __mapper_args__ = {'polymorphic_identity': 'worker'}
    title = db.Column(db.String, nullable = False)

(Yes, I'm using Flask-SqlAlchemy and not plain vanilla) Now how might I go about converting one declarative model to another. That is, what if a "Worker" was promoted to "Manager?" How do I do that? Do I have to write raw SQL to do that?

(是的，我使用Flask-SqlAlchemy，而不是普通的香草)现在我该如何将一个声明式模型转换为另一个呢?也就是说，如果一个“工人”被提升为“经理”呢?我该怎么做呢?我需要编写原始SQL来实现这一点吗?

Sorry if this has been asked before but I couldn't find it from the Googles. Please note, this is a contrived example.

对不起，如果之前有人问过这个问题，但我在谷歌上找不到。请注意，这是一个人为的例子。

2 个解决方案

#1

It's kludgy, and it causes a warning, but you can brute-force modify the discriminator column by setting the property:

它是kludgy，它会引起警告，但是您可以通过设置属性来对discriminator列进行蛮力修改:

john_smith = session.query(Employee).filter_by(name='john smith').one()
john_smith.discriminator = 'manager'
session.commit()

This will cause a warning like,

这会引起一个警告，

SAWarning: Flushing object <Worker at 0xdeadbeef> with incompatible polymorphic
identity 'manager'; the object may not refresh and/or load correctly
    mapper._validate_polymorphic_identity(mapper, state, dict_)

You can just ignore that, as long as you fix the issues it will cause. The safest thing is to close the session (session.close()) or expunge everything from it (session.expunge_all()) immediately after the commit.

只要你解决了问题，你就可以忽略它。最安全的方法是在提交之后立即关闭会话(session.close()))或从会话中删除所有内容(session.expunge_all())。

If you must, you can fix issues with John's object alone by just expunging John from the session (session.expunge(john_smith)). You have to be careful with that; any remaining references to john_smith will keep the object, although thankfully he will be detached from session and you won't be allowed to do anything with them.

如果必须的话，您可以通过将John从会话中删除(session.expunge(john_smith))来解决与John的对象相关的问题。你要小心;对john_smith的任何剩余引用都将保留该对象，不过值得庆幸的是，它将与会话分离，并且不允许您使用它们做任何事情。

I tried the other obvious options as well. Neither worked, but both are illustrative of what SQLAlchemy's Session object stores and how:

我也尝试了其他明显的选择。这两种方法都不起作用，但都说明了SQLAlchemy的会话对象存储的内容以及如何:

session.refresh(john_smith) fails with

session.refresh(john_smith)失败
```
InvalidRequestError: Could not refresh instance '<Worker at 0xdeadbeef>'
```
That's because SQLAlchemy queries the database for a Worker (not an Employee) and can't find one by the name of John Smith, because the database now knows that John got promoted due to the fancy new value in his type column.

这是因为SQLAlchemy查询数据库中的工作人员(而不是员工)，并且无法通过John Smith的名字找到工作人员，因为数据库现在知道，由于John的type列中有漂亮的新值，所以他得到了提升。
session.expire(john_smith) succeeds but fails to update John as a new class, and any subsequent access to him will result in

expire .expire(john_smith)成功但不能将John更新为一个新的类，随后对他的任何访问都将导致
```
ObjectDeletedError: Instance '<Worker at 0xdeadbeef>' has been deleted, or
its row is otherwise not present.
```
SQLAlchemy still thinks John is a Worker, and it tries to query for him as a Worker. That's because he's still persisted in session.identity_map, which looks like this:

SQLAlchemy仍然认为John是一个工作者，它试图为他作为一个工作者进行查询。那是因为他还在开会。identity_map，如下所示:
```
{(saexample2.Employee, (1,)): <saexample2.Worker at 0xdeadbeef>}
```
So there's John, listed explicitly as a Worker object. When you expunge() John from the session, this entry in the dictionary gets purged. When you expire() him, all of his mapped properties get marked as stale, but he still exists in the dictionary.

这是John，明确地列成Worker对象。当您将John从会话中删除时，字典中的这个条目将被清除。当您对其过期()时，所有映射属性都被标记为陈腐，但它仍然存在于字典中。

#2

I'd suggest reworking your object model. A sign that the object model would benefit from a rethink is, when one object works just as well as the attribute of another. In this case, Worker.title could equally well be "Manager".

我建议重新设计对象模型。对象模型将从重新考虑中获益的一个标志是，当一个对象与另一个对象的属性一样工作时。在这种情况下,工人。头衔也可以是“经理”。

Also, Manager.division works better as its own object Division. Not least because a Division would conceivably have a one to many relationship with Worker.

同时,经理。除法更适合作为自己的对象划分。最重要的是，一个部门与员工的关系可能是一对一的。

Something like perhaps a Division object with a ForeignKey of manager pointing to an Employee object. The Employee object would have a title attribute; in the Employee.__init__() you can manually check if the employee is manager of any divisions and then set the Employee.title to "Manager" from __init__().

类似于一个部门对象，一个管理者的外国人指向一个员工对象。Employee对象将具有title属性;在employee .__init__()中，您可以手动检查该员工是否是任何部门的经理，然后设置该员工。从__年__月__日起给“经理”的头衔。

#1