最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【整理】Flask中SQLAlchemy中复杂的多对多的关系,relationship和ForeignKey,backref的作用

Flask crifan 9588浏览 0评论

之前其实已经对于Flask中的SQLAlchemy中的多对多的关系,

其中涉及到:

relationship

ForeignKey

backref

但是其实不是真正深入的了解了。

现在遇到一个数据库建表时,又遇到类似情况

有个复杂的关系

一个User

一个Task表

Task中有两列:

创建者creator

跑腿人errandPerson

分别可能是两个不同的User

一个User本身,存在

tasks:是个作为creator的Task的列表

orders:是个作为errandPerson的Task的列表

想要实现这种关系

flask sqlalchemy relationship

flask sqlalchemy relationship 关系 如何定义

python – flask-sqlalchemy的db.relationship函数做了什么 – SegmentFault

class Writer(db.Model):
    __tablename__ = "writers"
    id      = db.Column(db.Integer, primary_key = True)
    articls = db.relationship("Article",backref = ‘writer’)
class Article(db.Model):
    __tablename__ = "articls"
    id        = db.Column(db.Integer, primary_key = True)
    writer_id = db.Column(db.Integer, db.ForeignKey("writers.id"))

-》

如你给的例子里, relationship 给 Writer 新增了一个 articles 属性,内容是以 writer_id 为外键关联的 一组 Article,backref 则指定给 Article 类增加了一个 writer 属性,内容是以 writer_id 为外键关联的 Writer。

 

-》

看起来:

此处表A1中列C1的relationship能够起效果的前提是:

另外的表B中,有列C2中有对应的ForeignKey

-》

否则C1,无法找到,是哪个表,和自己对应。

-》

感觉此处的:

relationship + ForeignKey

就可以起到一个简单的,关联作用了。

就不需要另外的单独弄一个Table存此映射关系了。

python – Flask SQLAlchemy relationship – Stack Overflow

-》

好像这么写:

    local_id =db.Column(db.Integer, db.ForeignKey(‘companies.id’))    
    guest_id = db.Column(db.Integer, db.ForeignKey(‘companies.id’))
    local = db.relationship(‘Company’, foreign_keys=local_id)
    guest = db.relationship(‘Company’, foreign_keys=guest_id)

也是可以的。

不过没看到过:

foreign_keys

-》

SQLAlchemy Documentation — SQLAlchemy 1.1 Documentation

-》

Relationship Configuration — SQLAlchemy 1.1 Documentation

去看看官网文档

Building a Relationship

Building a Many To Many Relationship

-》

注:

之前的backref,新版本换成了,信息更全面,更容易操作的:back_populates

不过backref,同样会保留

Linking Relationships with Backref — SQLAlchemy 1.1 Documentation

Basic Relationship Patterns — SQLAlchemy 1.1 Documentation

一A对多B:A中使用relationship,B中使用ForeignKey

多A对1B:A中使用ForeignKey,

-》

如果是1(而不是多)的情况,则应该有ForeignKey

Basic Relationship Patterns — SQLAlchemy 1.1 Documentation

->

看的晕死了。。。

暂时使用:

class User(db.Model):
    __tablename__ = ‘users’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("user-"), nullable=False)
 
    # tasks = db.relationship(‘Task’, backref=’initiator’, lazy=’subquery’)
    # orders = db.relationship(‘Task’, backref=’errandPerson’, lazy=’subquery’)
    # tasks = db.relationship(‘Task’, back_populates=’initiator’, lazy=’subquery’)
    # orders = db.relationship(‘Task’, back_populates=’errandPerson’, lazy=’subquery’)
    tasks = db.relationship(‘Task’, back_populates=’initiator’)
    orders = db.relationship(‘Task’, back_populates=’errandPerson’)
class Task(db.Model):
    __tablename__ = ‘tasks’
    id = db.Column(db.String(64), primary_key=True, default = generateUUID("task-"), nullable=False)
    initiatorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    errandPersonId = db.Column(db.String(64), db.ForeignKey("users.id"))
    # initiator = db.relationship(‘User’, foreign_keys=initiatorId)
    # errandPerson = db.relationship(‘User’, foreign_keys=errandPersonId)
    initiator = db.relationship(‘User’, back_populates="tasks")
    errandPerson = db.relationship(‘User’, back_populates="orders")

SQLAlchemy外键和关系-codexiu.cn

flask sqlalchemy 一对多 不同

flask-sqlalchemy 简单笔记 – 简书

Python的Flask框架中使用Flask-SQLAlchemy管理数据库的教程 – Lai18.com IT技术文章收藏夹

【总结】

然后去创建了表

后续经过优化,表结构是:

############################################################
# Current Project
############################################################
from runningfast.app import db, gLog
from runningfast.common.utils import *
############################################################
# Python
############################################################
from datetime import datetime, timedelta
import enum
def genPromotionCode(codeLen=PROMOTION_CODE_LEN):
    promotionCode = genRandomAlphanum(codeLen)
    gLog.debug("promotionCode=%s", promotionCode)
    return promotionCode
def calcPromotionExpiredDate():
    curDatetime = datetime.now()
    expiredDatetime = curDatetime + timedelta(seconds=PROMOTION_CODE_EXPIRED_SECONDS)
    gLog.debug("curDatetime=%s, expiredDatetime=%s", curDatetime, expiredDatetime)
    return expiredDatetime
class PromotionType(enum.Enum):
    Cash = "Cash" # reduce cash
    DiscountPercent = "DiscountPercent" # give some discount
class PromotionSourceType(enum.Enum):
    ByShareTask = "FromShareTask" # from share current doing task to get promotion
    ByUseCode = "FromUseCode" # from use promotion code, which is got from other’s Sms or facebook
class BillType(enum.Enum):
    Initiator = "Initiator"
    Errandor = "Errandor"
class ItemType(enum.Enum):
    Unknown = "Unknown" # not set yet
    Small = "Small" # like file
    Medium = "Medium" # use bag
    Large = "Large" # use box
    VeryLarge = "VeryLarge" # use car
class RatingType(enum.Enum):
    NoStar = "NoStar"
    OneStar = "OneStar"
    TwoStar = "TwoStar"
    ThreeStar = "ThreeStar"
    FourStar = "FourStar"
    FiveStar = "FiveStar"
class TipType(enum.Enum):
    NoTip = "NoTip"
    TenPercent = "TenPercent"
    FifthPercent = "FifthPercent"
    TwentyPercent = "TwentyPercent"
class UserRole(enum.Enum):
    IdleNoRole = "IdleNoRole" # idle status, no any role
    Initiator = "Initiator"
    Errandor = "Errandor"
class TaskStatus(enum.Enum):
    Created = "Created"
    Started = "Started" # == task grabbed -> consider started
    ToBeDetermine = "ToBeDetermine" # after upload info, need initiator determine
    Delivering = "Delivering" # after upload info, no need initiator determine
    Completed = "Completed"
    Canceled = "Canceled"
class InitiatorCancelTaskReasonType(enum.Enum):
    OtherReasons = "OtherReasons"
    WaitTooLong = "WaitTooLong"
    UnableContactRunner = "UnableContactRunner"
    LeaveForUrgentThings = "LeaveForUrgentThings"
class ErrandorCancelTaskReasonType(enum.Enum):
    OtherReasons = "OtherReasons"
    UnableContactCustomer = "UnableContactCustomer"
    UnableFinishDeliveryOrHurt = "UnableFinishDeliveryOrHurt"
    LeaveForUrgentThings = “LeaveForUrgentThings"
tasks_promotions = db.Table(‘tasks_promotions’, db.metadata,
                        db.Column(‘task_id’, db.String, db.ForeignKey(‘tasks.id’)),
                        db.Column(‘promotion_id’, db.String, db.ForeignKey(‘promotions.id’))
)
class Promotion(db.Model):
    __tablename__ = ‘promotions’
    id = db.Column(db.String(64), primary_key=True, default = generatePromotionId, nullable=False)
    createdAt = db.Column(db.DateTime, nullable=False, default = datetime.now)
    updatedAt = db.Column(db.DateTime, nullable=False, default = datetime.now, onupdate = datetime.now)
    sourceType = db.Column(db.Enum(PromotionSourceType), default=PromotionSourceType.ByShareTask)
    type = db.Column(db.Enum(PromotionType), default=PromotionType.Cash)
    creatorId = db.Column(db.String(64), nullable=False, default="")
    code = db.Column(db.String(PROMOTION_CODE_LEN), nullable=False, default = "")
    shareByTaskId = db.Column(db.String(64), nullable=False, default="")
    reduceCash = db.Column(db.Float, nullable=False, default=0.0)
    discountPercent = db.Column(db.Float, nullable=False, default=0.0)
    isUsed = db.Column(db.Boolean, nullable=False, default=False)
    usedOnTaskId = db.Column(db.String(64), nullable=False, default="")
    isExpired = db.Column(db.Boolean, nullable=False, default=False)
    expiredAt = db.Column(db.DateTime, nullable=False, default = calcPromotionExpiredDate)
    useOnTasks = db.relationship("Task", secondary=tasks_promotions, back_populates="initiatorPromotions")
class Bill(db.Model):
    __tablename__ = ‘bills’
    id = db.Column(db.String(64), primary_key=True, default = generateBillId, nullable=False)
    createdAt = db.Column(db.DateTime, nullable=False, default = datetime.now)
    updatedAt = db.Column(db.DateTime, nullable=False, default = datetime.now, onupdate = datetime.now)
    type = db.Column(db.Enum(BillType), default=BillType.Initiator)
    totalFee = db.Column(db.Float, nullable=False, default=0.0)
    errandFee = db.Column(db.Float, nullable=False, default = 0.0)
    disbursementFee = db.Column(db.Float, nullable=False, default = 0.0)
    extraFee = db.Column(db.Float, nullable=False, default=0.0)
    tipFee = db.Column(db.Float, nullable=False, default=0.0)
    ### for Initiator
    initiatorTaxFee = db.Column(db.Float, nullable=False, default=0.0)
    initiatorPromotionFee = db.Column(db.Float, nullable=False, default=0.0)
    initiatorBillTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    initiatorBillTask = db.relationship("Task", back_populates="initiatorBill", foreign_keys=[initiatorBillTaskId])
    # only for platform
    platformFee = db.Column(db.Float, nullable=False, default=0.0)
    ### for Errandor
    errandorBillTaskId = db.Column(db.String(64), db.ForeignKey("tasks.id"))
    errandorBillTask = db.relationship("Task", back_populates="errandorBill", foreign_keys=[errandorBillTaskId])
class Resource(db.Model):
    __tablename__ = ‘resources’
    id = db.Column(db.String(64), primary_key=True, default = generateResourceId, nullable=False)
    initiatorTaskId = db.Column(db.String(64), db.ForeignKey(‘tasks.id’))
    initiatorTask = db.relationship("Task", back_populates="initiatorDescImgList", foreign_keys=[initiatorTaskId])
    errandorTaskId = db.Column(db.String(64), db.ForeignKey(‘tasks.id’))
    errandorTask = db.relationship("Task", back_populates="errandorDescImgList", foreign_keys=[errandorTaskId])
class Location(db.Model):
    __tablename__ = ‘locations’
    id = db.Column(db.String(64), primary_key=True, default = generateLocationId, nullable=False)
    createdAt = db.Column(db.DateTime, nullable=False, default = datetime.now)
    updatedAt = db.Column(db.DateTime, nullable=False, default = datetime.now, onupdate = datetime.now)
    longitude = db.Column(db.Float, nullable=False, default = 0.0)
    latitude = db.Column(db.Float, nullable=False, default=0.0)
    shortStr = db.Column(db.String(64), nullable=False, default = "")
    fullStr = db.Column(db.String(256), nullable=False, default="")
class Task(db.Model):
    __tablename__ = ‘tasks’
    id = db.Column(db.String(64), primary_key=True, default = generateTaskId, nullable=False)
    statusType = db.Column(db.Enum(TaskStatus), default=TaskStatus.Created)
    itemType = db.Column(db.Enum(ItemType), default=ItemType.Unknown)
    promotionCode = db.Column(db.String(PROMOTION_CODE_LEN), nullable=False, default = "")
    hasEnded = db.Column(db.Boolean, nullable=False, default=False)
    endedTime = db.Column(db.DateTime, nullable=True, default = None)
    createdAt = db.Column(db.DateTime, nullable=False, default = datetime.now)
    updatedAt = db.Column(db.DateTime, nullable=False, default = datetime.now, onupdate = datetime.now)
    initiatorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    errandorId = db.Column(db.String(64), db.ForeignKey("users.id"))
    initiator = db.relationship(‘User’, back_populates="tasks", foreign_keys=[initiatorId])
    errandor = db.relationship(‘User’, back_populates="orders", foreign_keys=[errandorId])
    cancelByUserType = db.Column(db.Enum(UserRole), default=UserRole.IdleNoRole)
    ### initiator
    initiatorPromotions = db.relationship(‘Promotion’, secondary=tasks_promotions, back_populates=’useOnTasks’)
    initiatorTipType = db.Column(db.Enum(TipType), default=TipType.NoTip)
    initiatorRatingType = db.Column(db.Enum(RatingType), default=RatingType.NoStar)
    initiatorStartLocationId = db.Column(db.String(64), db.ForeignKey("locations.id"))
    initiatorStartLocation = db.relationship("Location", foreign_keys=[initiatorStartLocationId])
    initiatorEndLocationId = db.Column(db.String(64), db.ForeignKey("locations.id"))
    initiatorEndLocation = db.relationship("Location", foreign_keys=[initiatorEndLocationId])
    initiatorDescription = db.Column(db.String(512), nullable=False, default="")
    initiatorDescImgList = db.relationship(‘Resource’, back_populates=’initiatorTask’,
                                           foreign_keys="Resource.initiatorTaskId")
    initiatorBill = db.relationship(‘Bill’, uselist=False, back_populates="initiatorBillTask",
                                    foreign_keys="Bill.initiatorBillTaskId")
    initiatorCancelReasonType = db.Column(db.Enum(InitiatorCancelTaskReasonType),
                                          default=InitiatorCancelTaskReasonType.OtherReasons)
    ### errand Peron, here call it: errandor
    errandorStartLocationId = db.Column(db.String(64), db.ForeignKey("locations.id"))
    errandorStartLocation = db.relationship("Location", foreign_keys=[errandorStartLocationId])
    errandorEndLocationId = db.Column(db.String(64), db.ForeignKey("locations.id"))
    errandorEndLocation = db.relationship("Location", foreign_keys=[errandorEndLocationId])
    errandorDescription = db.Column(db.String(512), nullable=False, default="")
    errandorDescImgList = db.relationship(‘Resource’, back_populates=’errandorTask’,
                                          foreign_keys="Resource.errandorTaskId")
    errandorBill = db.relationship(‘Bill’, uselist=False, back_populates="errandorBillTask",
                                   foreign_keys="Bill.errandorBillTaskId")
    errandorCancelReasonType = db.Column(db.Enum(ErrandorCancelTaskReasonType),
                                         default=ErrandorCancelTaskReasonType.OtherReasons)
    errandorStartTime = db.Column(db.DateTime, nullable=True, default = None)
class User(db.Model):
    __tablename__ = ‘users’
    id = db.Column(db.String(64), primary_key=True, default = generateUserId, nullable=False)
    phone = db.Column(db.String(32), nullable=False, default = "")
    email = db.Column(db.String(64), nullable=False, default = "")
    password = db.Column(db.String(20), nullable=False, default = "")
    firstName = db.Column(db.String(64), nullable=False, default = "")
    lastName = db.Column(db.String(64), nullable=False, default = "")
    avatarUrl = db.Column(db.String(256), nullable=False, default="")
    facebookUserId = db.Column(db.String(64), nullable=False, default = "")
    createdAt = db.Column(db.DateTime, nullable=False, default = datetime.now)
    updatedAt = db.Column(db.DateTime, nullable=False, default = datetime.now, onupdate = datetime.now)
    locationId = db.Column(db.String(64), db.ForeignKey("locations.id"))
    location = db.relationship("Location", foreign_keys=[locationId])
    isOnline = db.Column(db.Boolean, nullable=False, default=False)
    curRole = db.Column(db.Enum(UserRole), default=UserRole.IdleNoRole)
    ### initiator
    # within one week, for task, the count of share promotion code
    shareCodeCount =db.Column(db.Integer, nullable=False, default=0)
    tasks = db.relationship(‘Task’, back_populates=’initiator’, foreign_keys=[Task.initiatorId])
    ### errandor
    orders = db.relationship(‘Task’, back_populates=’errandor’, foreign_keys=[Task.errandorId])
    errandorRating = db.Column(db.Float, nullable=False, default=0.0)
    errandorIsAuthenticated = db.Column(db.Boolean, nullable=False, default=False)
    errandorIsAvailable = db.Column(db.Boolean, nullable=False, default=False)

供参考。

对应的,对应的生成的数据库结构为:

转载请注明:在路上 » 【整理】Flask中SQLAlchemy中复杂的多对多的关系,relationship和ForeignKey,backref的作用

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
82 queries in 0.178 seconds, using 22.37MB memory