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

之前其实已经对于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的作用

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

与本文相关的文章

Hi，您需要填写昵称和邮箱！