SqlAlchemy: converts the type inherited from one to another
source link: https://www.codesd.com/item/sqlalchemy-converts-the-type-inherited-from-one-to-another.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
SqlAlchemy: converts the type inherited from one to another
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?
Sorry if this has been asked before but I couldn't find it from the Googles. Please note, this is a contrived example.
It's kludgy, and it causes a warning, but you can brute-force modify the discriminator column by setting the property:
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.
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.
I tried the other obvious options as well. Neither worked, but both are illustrative of what SQLAlchemy's Session
object stores and how:
session.refresh(john_smith)
fails withInvalidRequestError: Could not refresh instance '<Worker at 0xdeadbeef>'
That's because SQLAlchemy queries the database for a
Worker
(not anEmployee
) 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 histype
column.session.expire(john_smith)
succeeds but fails to update John as a new class, and any subsequent access to him will result inObjectDeletedError: 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 aWorker
. That's because he's still persisted insession.identity_map
, which looks like this:{(saexample2.Employee, (1,)): <saexample2.Worker at 0xdeadbeef>}
So there's John, listed explicitly as a
Worker
object. When youexpunge()
John from the session, this entry in the dictionary gets purged. When youexpire()
him, all of his mapped properties get marked as stale, but he still exists in the dictionary.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK