文章内容
2019/5/23 10:40:20,作 者: 黄兵
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多对多的建表方式
评论列表