SQLacodegen生成SQLAlchemy多对多父子关系不符合预期

问题描述 #

使用SQLacodegen工具自动生成SQLAlchemy ORM模型代码时，无法按照数据库设计生成正确的父子表多对多关系定义。

业务场景 #

根据ER图设计，product为父表，feature、related为子表；其中product与feature为多对多关系，product与related也为多对多关系，分别通过中间表product_features、product_related关联。

预期生成的Product类代码片段 #

class Product():
    relateds = relationship('Related', secondary='product_related')
    features = relationship('Feature', secondary='product_featured')

SQLacodegen实际生成的异常代码片段 #

class Product():
    relateds = relationship('Related', secondary='product_related')
    # features属性缺失

class Feature():
   # products属性被错误生成在此处，导致Feature被识别为Product的父表，与实际父子关系方向完全相反
   products = relationship('Product', secondary='product_features')

数据库原始建表SQL #

-- Table: product
CREATE TABLE product (
    id bigserial  NOT NULL,
    mpn text  NULL,
    title text  NULL,
    price real  NULL,
    msrp real  NULL,
    stock_availability boolean  NULL,
    country_id int8  NULL,
    description text  NULL,
    weight_packaging real  NULL,
    item_type varchar(20)  NULL,
    upc varchar(40)  NULL,
    model varchar(60)  NULL,
    sku varchar(40)  NULL,
    badges text  NULL,
    url text  NULL,
    site_item_id text  NULL,
    brand_id int8  NULL,
    CONSTRAINT product_id PRIMARY KEY (id)
);

CREATE INDEX country_id on product (country_id ASC);

CREATE INDEX product_idx_2 on product (brand_id ASC);


-- Table: related
CREATE TABLE related (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT related_pk PRIMARY KEY (id)
);

-- Table: product_related
CREATE TABLE product_related (
    product_id int8  NOT NULL,
    related_id int8  NOT NULL,
    CONSTRAINT product_related_pk PRIMARY KEY (product_id,related_id)
);

CREATE INDEX product_related_idx_1 on product_related (product_id ASC);

CREATE INDEX product_related_idx_2 on product_related (related_id ASC);

-- Reference: product_related_related (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_related
    FOREIGN KEY (related_id)
    REFERENCES related (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_related_product (table: product_related)
ALTER TABLE product_related ADD CONSTRAINT product_related_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Table: feature
CREATE TABLE feature (
    id bigserial  NOT NULL,
    name text  NOT NULL,
    CONSTRAINT feature_pk PRIMARY KEY (id)
);

-- Table: product_features
CREATE TABLE product_features (
    product_id int8  NOT NULL,
    feature_id int8  NOT NULL,
    CONSTRAINT product_features_pk PRIMARY KEY (product_id,feature_id)
);

CREATE INDEX product_features_idx_1 on product_features (product_id ASC);

CREATE INDEX product_features_idx_2 on product_features (feature_id ASC);

-- Reference: product_features_feature (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_feature
    FOREIGN KEY (feature_id)
    REFERENCES feature (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

-- Reference: product_features_product (table: product_features)
ALTER TABLE product_features ADD CONSTRAINT product_features_product
    FOREIGN KEY (product_id)
    REFERENCES product (id)  
    NOT DEFERRABLE 
    INITIALLY IMMEDIATE
;

SQLacodegen实际生成的完整代码 #

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

    products = relationship('Product', secondary='product_features')


class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')

预期SQLacodegen生成的正确代码 #

t_product_features = Table(
    'product_features', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False),
    Column('feature_id', ForeignKey('feature.id'), primary_key=True, nullable=False)
)


t_product_related = Table(
    'product_related', metadata,
    Column('product_id', ForeignKey('product.id'), primary_key=True, nullable=False, index=True),
    Column('related_id', ForeignKey('related.id'), primary_key=True, nullable=False, index=True)
)

class Product(Base):
    __tablename__ = 'product'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('product_id_seq'::regclass)"))
    mpn = Column(Text)
    title = Column(Text)
    price = Column(Float)
    msrp = Column(Float)
    stock_availability = Column(Boolean)
    country_id = Column(ForeignKey('country.id'), index=True)
    description = Column(Text)
    weight_packaging = Column(Float)
    item_type = Column(String(20))
    upc = Column(String(40))
    model = Column(String(60))
    sku = Column(String(40))
    badges = Column(Text)
    url = Column(Text)
    site_item_id = Column(Text)
    brand_id = Column(ForeignKey('brand.id'), index=True)

    brand = relationship('Brand')
    country = relationship('Country')
    relateds = relationship('Related', secondary='product_related')
    features = relationship('Feature', secondary='product_feature')

class Feature(Base):
    __tablename__ = 'feature'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('feature_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

class Related(Base):
    __tablename__ = 'related'

    id = Column(BigInteger, primary_key=True, server_default=text("nextval('related_id_seq'::regclass)"))
    name = Column(Text, nullable=False)

内容的提问来源于stack exchange，提问作者vcm