SQL炼金术中独立ORM映射类的列继承

Question

我正在尝试使用SQLalchemy中的声明性ORM Mapper类复制以下SQLITE 创建表语句。

CREATE TABLE IF NOT EXISTS question (
    id INTEGER NOT NULL PRIMARY KEY,
    title             TEXT NOT NULL,
    body              TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS solution (
    id INTEGER NOT NULL PRIMARY KEY,
    title               TEXT NOT NULL,
    body                TEXT NOT NULL,
    source_code         TEXT NOT NULL,
    runtime_complexity  TEXT NOT NULL,
    storage_complexity  TEXT NOT NULL,
    understood TEXT NOT NULL
);

是否有一种方法可以在ORM映射器类中指定继承，从而使问题和解决方案表保持独立（即对层次结构中的子类查询不应渲染作为SQL在其继承路径中的所有表中加入），同时还减少了下面显示的重复代码的数量？如下注释所示，这些映射类类具有一些具有相同名称和数据类型的相同列，并且具有相同的__ epr __方法。

from sqlalchemy import Column, Sequence, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Question(Base):
    __tablename__ = 'question'

    # vvv Both Solution and Question classes have these columns 
    id = Column(Integer, Sequence('id_seq'), primary_key=True)
    title = Column(String, nullable=False)
    body = Column(Integer, nullable=False)
    # ^^^

    # vvv Both Solution and Question classes have this method
    def __repr__(self):
        class_name = self.__class__.__name__
        column_names = tuple(col.name for col in self.__table__.columns)
        return f"<{class_name}{column_names}>"
    # ^^^

    
    
   
class Solution(Base):
    __tablename__ = 'solution'

    # vvv same in Question class
    id = Column(Integer, Sequence('id_seq'), primary_key=True)
    title = Column(String, nullable=False)
    body = Column(Integer, nullable=False)
    # ^^^

    source_code = Column(Integer, nullable=False)
    runtime_complexity = Column(Integer, nullable=False)
    storage_complexity = Column(Integer, nullable=False)
    understood = Column(Integer, nullable=False)

    # vvv same in Question class
    def __repr__(self):
        class_name = self.__class__.__name__
        column_names = tuple(col.name for col in self.__table__.columns)
        return f"<{class_name}{column_names}>"
    # ^^^

从我在

sqlalchemy支持三种形式的继承形式：

1. ，其中几种类型的类由单个表表示。
2. ，其中每种类型的类都由独立表表示。
3. ，类别层次结构在依赖表中分解，每个类别由其自己的表表示，该表仅包含该类的本地属性。

该部分将导致我假设混凝土表继承是我需要的。但是，在“具体表继承”部分下，它读取：

混凝土继承将每个子类映射到其自己独特的表格，每个表都包含产生该类实例所需的所有列。

。

这是我试图完成的工作。 Sqlalchemy是否可以这样的事情？

from sqlalchemy import Column, Sequence, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class QS(Base): 
    # an abstract class that does NOT map to any table in the database. 
    # just holds column names and column data types,
    # along with methods common to question and solution mapper classes
    __abstract__ = True
    id = Column(Integer, Sequence('id_seq'), primary_key=True)
    title = Column(String, nullable=False)
    body = Column(Integer, nullable=False)

    def __repr__(self):
        class_name = self.__class__.__name__
        column_names = tuple(col.name for col in self.__table__.columns)
        return f"<{class_name}{column_names}>"

class Question(QS): 
    __tablename__ = 'question'
    # actually maps to table called question
    # which has columns id, title, body
 
class Solution(QS):
    __tablename__ = 'solution'
    # actually maps to table called solution,
    # which has columns id, title, body, source_code, runtime_complexity, and storage_complexity
    # (has a few more columns than the base class)

    source_code = Column(Integer, nullable=False)
    runtime_complexity = Column(Integer, nullable=False)
    storage_complexity = Column(Integer, nullable=False)
    understood = Column(Integer, nullable=False)

Answer 1

感谢 @rfkortekaas '评论，建议使用 mixin类，我能够声明问题 document code 和解决方案继承。

from sqlalchemy import Column, String, Sequence, Integer

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_mixin
from sqlalchemy.orm import declared_attr

Base = declarative_base()

@declarative_mixin
class Document:

    def __repr__(self):
        return f"<{self.__class__.__name__}{self.__dict__}>"

    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()

    id    = Column(Integer, Sequence('id_seq'), primary_key=True)
    title = Column(String, nullable=False)
    body  = Column(String, nullable=False)

class Question(Base, Document):
    pass

class Solution(Base, Document):
    source_code        = Column(String, nullable=False)
    runtime_complexity = Column(String, nullable=False)
    storage_complexity = Column(String, nullable=False)
    understood         = Column(String, nullable=False)