黄兵的个人博客

SQLAlchemy多对多的建表方式

首先定义两个表的相关属性：

# 私有号码特性
class PrivateNumberProperty(db.Model):
    __tablename__ = 'private_number_property'
    id = db.Column(db.Integer, primary_key=True)
    property_name_id = db.Column(db.Integer, db.ForeignKey('property_name.id'))
    subtitle = db.Column(db.String(64))
    icon = db.Column(db.String(128))
    uri = db.Column(db.String(128))
    uri_text = db.Column(db.String(8))
    private_number_catalog_id = db.Column(db.Integer, db.ForeignKey('private_number_catalog.id'))
    PrivateNumberPrice = db.relationship('PrivateNumberPrice', backref='private_number_property', lazy='joined')

另外一张表：

# 套餐优势
class PackageAdvantage(db.Model):
    __tablename_ = 'package_advantage'
    id = db.Column(db.Integer, primary_key=True)
    advantage = db.Column(db.String(32))
    icon = db.Column(db.String(128))

之后设置两张表之间的关联（第三张关联表）：

# 优势和号码套餐之间多对多中间表
ap = db.Table('ap',
              db.Column('properties_id', db.Integer, db.ForeignKey('private_number_property.id'), primary_key=True),
              db.Column('package_id', db.Integer, db.ForeignKey('package_advantage.id'), primary_key=True))

之后设置相关的链接方式，以及相关操作（级联删除等）：

PackageAdvantage = db.relationship('PackageAdvantage', secondary=ap,
                                       backref=db.backref('PrivateNumberProperty', lazy='dynamic'), lazy='dynamic',
                                       single_parent=True,
                                       cascade='all, delete-orphan')

至于为何要设置single_parent=True可以参考这篇文章：  Set single_parent=True on the relationship().

最后完整代码：

# 私有号码特性
class PrivateNumberProperty(db.Model):
    __tablename__ = 'private_number_property'
    id = db.Column(db.Integer, primary_key=True)
    property_name_id = db.Column(db.Integer, db.ForeignKey('property_name.id'))
    subtitle = db.Column(db.String(64))
    icon = db.Column(db.String(128))
    uri = db.Column(db.String(128))
    uri_text = db.Column(db.String(8))
    private_number_catalog_id = db.Column(db.Integer, db.ForeignKey('private_number_catalog.id'))
    PrivateNumberPrice = db.relationship('PrivateNumberPrice', backref='private_number_property', lazy='joined')

    PackageAdvantage = db.relationship('PackageAdvantage', secondary=ap,
                                       backref=db.backref('PrivateNumberProperty', lazy='dynamic'), lazy='dynamic',
                                       single_parent=True,
                                       cascade='all, delete-orphan')

最后查看一下模型 -> 数据库的相关代码：

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('package_advantage',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('advantage', sa.String(length=32), nullable=True),
    sa.Column('icon', sa.String(length=128), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('ap',
    sa.Column('properties_id', sa.Integer(), nullable=False),
    sa.Column('package_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['package_id'], ['package_advantage.id'], ),
    sa.ForeignKeyConstraint(['properties_id'], ['private_number_property.id'], ),
    sa.PrimaryKeyConstraint('properties_id', 'package_id')
    )
    # ### end Alembic commands ###

也就是通过外键引用，产生多对多的关系。

同时多对多的查询SQLAlchemy能自动处理表与表之间的关系。

黄兵个人博客原创。

转载请注明出处：黄兵个人博客 - SQLAlchemy多对多的建表方式