What I was addicted to when combining class inheritance and Joint Table Inheritance in SQLAlchemy

@declared_attr has an effect up to the derived table

Premise

--There are two tables (= models) that you want to configure with Joint Table Inheritance (Employee, Manager, Engineer). --Omoto (Employee) also has columns in common with other tables, and I want to define them in DRY including indexes (Human). --Therefore, the configuration is Human-> Employee-> (Manager, Engineer).

I was addicted to

If you define an index on the Human class with the @declared_attr decorator, the index will go to the bottom class.

joint_ng.py


from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Human(object):
    age = Column(Integer)
    name = Column(String(50))

    @declared_attr
    def __table_args__(cls):
        return(Index('index_name', 'name'),)

class Employee(Human, Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }
    __table_args__ = {
        
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }
    __table_args__ = {
        
    }

engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')

if database_exists(engine.url):
    drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

m = Manager(age=10, name = 'foo', manager_name='hoge')
session.add(m)
session.commit()

An error occurs when trying to index the name column in the Manager table that does not define the name column.

Traceback (most recent call last):
  File "joint.py", line 27, in <module>
    class Engineer(Employee):
(abridgement…)
  File "/home/satosi/.pyenv/versions/3.6.1/lib/python3.5/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
    return self._data[key]
KeyError: 'name'

Speaking of course, it seems that the situation cannot be swallowed from the error message, and it took time to narrow down the cause, so I will publish it.

Countermeasures

Simply redefine table_args in the Manager class.

joint_ok.py


class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }
    __table_args__ = {
        
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }
    __table_args__ = {
        
    }

Inheritance of polymorphic_identity is unexpected

Premise

I want to write the table definition and model logic separately.

I was addicted to

I tried to inherit in the order of declarative_base-> class that specifies Column member (Manager)-> class of application layer (SubManager), but the type that is the key to polymorphism is not specified.

joint_ng2.py


import sys
from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))
    
    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

    __table_args__ = {        
    }

    def shout(self):
        print('Oh')

class SubManager(Manager):
    def shout(self):
        print('Wah')

engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')

if database_exists(engine.url):
    drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

m = SubManager(manager_name='hoge')
m.shout()
session.add(m)
session.commit()
query = session.query(Manager)
print(query)
manager = query.first()
assert manager.type is None
manager.shout()

output


Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name 
FROM employee INNER JOIN manager ON employee.id = manager.id
Oh

So try specifying @declared_attr.

joint_ng2.py


class Manager(Employee):
    (snip...)
    @declared_attr
    def __mapper_args__(cls):
       return {
        'polymorphic_identity':'manager',
       }

It worked, but I was warned that it was a double definition.

output


/home/satosi/.pyenv/versions/3.6.1-mtxweb/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py:1034: SAWarning: Reassigning polymorphic association for identity 'manager' from <Mapper at 0x7fb0202ed978; Manager> to <Mapper at 0x7fb0202edbe0; SubManager>: Check for duplicate use of 'manager' as value for polymorphic_identity.
  self, self.polymorphic_identity)
Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name 
FROM employee INNER JOIN manager ON employee.id = manager.id
Wah

Countermeasures

Simply change the order of inheritance. As long as there is a one-to-one correspondence between the app layer and the derived type of Joint Table Inheritance, there should be no difference in functionality.

joint_ok.py


from sqlalchemy.ext.declarative import *
from sqlalchemy import *
from sqlalchemy_utils import database_exists, drop_database, create_database
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class _Manager():
    def shout(self):
        print('Oh')

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

class Manager(Employee, _Manager):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))
    
    __mapper_args__ ={
    'polymorphic_identity':'manager',
    }

    __table_args__ = {        
    }

    def shout(self):
        print('Wah')

engine = create_engine('mysql://user:pass@localhost/dummy?charset=utf8')

if database_exists(engine.url):
    drop_database(engine.url)
create_database(engine.url)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

m = Manager(manager_name='hoge')
m.shout()
session.add(m)
session.commit()
query = session.query(Manager)
print(query)
manager = query.first()
assert manager.type is not None
manager.shout()

output


Wah
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name 
FROM employee INNER JOIN manager ON employee.id = manager.id
Wah

It was poor. It would be an unexpected joy if it helps someone.

Recommended Posts

What I was addicted to when combining class inheritance and Joint Table Inheritance in SQLAlchemy
A note I was addicted to when creating a table with SQLAlchemy
I was addicted to confusing class variables and instance variables in Python
What I was addicted to when using Python tornado
What I was addicted to when creating a web application in a windows environment
What I was addicted to when migrating Processing users to Python
When I tried to install PIL and matplotlib in a virtualenv environment, I was addicted to it.
What I was addicted to when dealing with huge files in a Linux 32bit environment
What I was addicted to when introducing ALE to Vim for Python
What I was addicted to Python autorun
The file name was bad in Python and I was addicted to import
Three things I was addicted to when using Python and MySQL with Docker
I was addicted to scraping with Selenium (+ Python) in 2020
I was addicted to trying logging.getLogger in Flask 1.1.x
What I did when I was angry to put it in with the enable-shared option
What I was asked when using Random Forest in practice
The story I was addicted to when I specified nil as a function argument in Go
When I tried to scrape using requests in python, I was addicted to SSLError, so a workaround memo
The record I was addicted to when putting MeCab on Heroku
A note I was addicted to when making a beep on Linux
What I was addicted to when I built my own neural network using the weights and biases I got with scikit-learn's MLP Classifier.
I was addicted to multiprocessing + psycopg2
When I put Django in my home directory, I was addicted to static files with permission errors
Numpy's intellisense (input completion) is incomplete in VS Code and I was lightly addicted to the solution
What I referred to when studying tkinter
I was addicted to pip install mysqlclient
I was addicted to Flask on dotCloud
Docker x visualization didn't work and I was addicted to it, so I summarized it!
A note I was addicted to when running Python with Visual Studio Code
A story that I was addicted to when I made SFTP communication with python
I set up TensowFlow and was addicted to it, so make a note
[Introduction to json] No, I was addicted to it. .. .. ♬
I wrote a class in Python3 and Java
What I did when updating from Python 2.6 to 2.7
[Question] What happens when I use% in python?
Note that I was addicted to npm script not passing in the verification environment
What to do when only the window is displayed and nothing is displayed in pygame Note
After implementing Watson IoT Platform application in Flask, I was addicted to MQTT connection
Precautions when adding setter and deleter in derived class to getter property of base class
Convenient Linux keyboard operation that I want to teach myself when I was in school
[Go language] Be careful when creating a server with mux + cors + alice. Especially about what I was addicted to around CORS.
What I do when imitating embedded go in python
[openpyxl] What to do when IllegalCharacterError appears in pandas.DataFrame.to_excel
pickle To read what was made in 2 series with 3 series
[Spark] I'm addicted to trapping "", null and [] in DataFrame
A story that I was addicted to at np.where
I was able to repeat it in Python: lambda
I want to do something in Python when I finish
I was surprised to receive a nice review when I wrote Python to CheckIO and its explanation
When I tried to install Ubuntu 18.04, "Initramfs unpacking failed: Decoding failed" was displayed and the startup failed.
Memo (March 2020) that I was addicted to when installing Arch Linux on MacBook Air 11'Early 2015
What I was addicted to in Collective Intelligence Chaprter 3. It's not a typo, so I think something is wrong with my code.