python - SQLAlchemy 关系多对多 X 2
问题描述
我正在尝试将事务表连接到成员表,其中每个成员有许多事务,但每个事务有两个成员(买方和卖方)。如何创建一个关系,允许我为每笔交易获取卖家的 memberID 和买家的 memberID?
类成员(db.Model,UserMixin):
__tablename__ = 'members'
id = db.Column(db.Integer, primary_key = True)
# exchangeID = db.Column(db.Integer(6),db.ForeignKey('exchanges.exchangeID'),nullable=False)
company_name = db.Column(db.String(64), nullable=False)
category = db.Column(db.String(64), nullable=False, default='Other')
description = db.Column(db.Text)
profile_image = db.Column(db.String(20), nullable=False, default='default_profile.png')
email = db.Column(db.String(64), unique=True, index=True)
# username = db.Column(db.String(64), unique=True, index=True, nullable=False)
# password_hash = db.Column(db.String(128), nullable=False)
address = db.Column(db.String(128))
phone = db.Column(db.Integer, nullable=False)
fein = db.Column(db.Integer)
webaddress = db.Column(db.String(64))
twitter = db.Column(db.String(24))
exchange_approved = db.Column(db.Boolean, default=False)
users = db.relationship('User', backref='company',lazy=True)
transactions = db.relationship('Transaction', backref='company',lazy=True)
listings = db.relationship('Listing', backref='company',lazy=True)
credit = db.relationship('Credit', backref='company',lazy=True)
def __init__(self,exchange_name,company_name,category,mail,address,phone,fein):
# self.exchange_name = exchange_name
self.company_name = company_name
self.category = category
self.email = email
self.address = address
self.phone = phone
self.fein = fein
def __repr__(self):
return f"Company Name: {self.company_name}"
类用户(db.Model,UserMixin):
__tablename__ = 'users'
members = db.relationship(Member)
id = db.Column(db.Integer, primary_key = True)
companyID = db.Column(db.Integer, db.ForeignKey('members.id'),nullable=False)
buy_transactions = db.relationship('Transaction', backref='buyer',lazy=True)
sell_transactions = db.relationship('Transaction', backref='seller',lazy=True)
# exchangeID = db.Column(db.Integer(6), db.ForeignKey('exchanges.exchangeID'),nullable=False)
email = db.Column(db.String(64), unique=True, index=True, nullable=False)
username = db.Column(db.String(64), unique=True, index=True, nullable=False)
password_hash = db.Column(db.String(128), nullable=False)
phone_number = db.Column(db.Integer, nullable=False)
user_type = db.Column(db.String(14))
member_approved = db.Column(db.Boolean, default=False)
limited_trade = db.Column(db.Boolean, default=True)
member_imposed_limit = db.Column(db.Integer, default=0)
def __init__(self,username,password,company_name,email,phone):
self.username = username
self.password_hash = generate_password_hash(password)
self.companyID = companyID
self.email = email
self.phone = phone
def check_password(self,password):
# https://stackoverflow.com/questions/23432478/flask-generate-password-hash-not-constant-output
return check_password_hash(self.password_hash,password)
def __repr__(self):
return f"UserName: {self.username}"
类事务(db.Model,UserMixin):
__tablename__ = 'transactions'
members = db.relationship(Member)
transactionID = db.Column(db.Integer, primary_key=True)
date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
companyID = db.Column(db.Integer, db.ForeignKey('members.id'),nullable=False)
sellerID = db.Column(db.Integer, db.ForeignKey('users.id'),nullable=False)
buyerID = db.Column(db.Integer, db.ForeignKey('users.id'),nullable=False)
seller = relationship("User", foreign_keys='Transaction.sellerID')
buyer = relationship("User", foreign_keys='Transaction.buyerID')
amount = db.Column(db.Numeric(5,2), nullable=False)
commission = db.Column(db.Numeric(5,2), nullable=False)
transactionDate = db.Column(db.DateTime, server_default=db.func.now())
approved = db.Column(db.Boolean, default=False)
commission_paid = db.Column(db.Boolean, default=False)
posted = db.Column(db.Boolean, default=False)
def __init__(self,sellerID,buyerID,amount):
self.sellerID = sellerID
self.buyerID = buyerID
self.amount = amount
def __repr__(self):
return f"Trasaction Id: {self.transactionID} --- Date: {self.date} --- Amount: {self.amount}"
我得到了一个没有定义的“关系”,但这很明显,因为我不明白如何连接如上所述的表。
解决方案
您在这里缺少的是一个关联表,它应该链接需要具有多对多关系的两个表。下面是一个非常简单的多对多关系示例,它应该让您了解如何从那里着手。
这就是你的models.py
样子
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + 'testdb.sql'
db = SQLAlchemy(app)
# Please note the below association table needs to be actual database table and not a model class.
stu_subs = db.Table('stu_subs', db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
db.Column('subject_id', db.Integer, db.ForeignKey('subjects.id')))
class Student(db.Model):
__tablename__ = 'students'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
# Note the secondary attribute below, that actually sets up a many-to-many relationship
subj = db.relationship('Subjects', secondary=stu_subs, backref=db.backref('student', lazy='dynamic'))
class Subjects(db.Model):
__tablename__ = 'subjects'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
最后,这就是您将如何访问每个表中的表字段的方式。
>>> db.create_all()
>>> from dummy import Student, Subjects
>>> student1 = Student(name="Student1")
>>> student2 = Student(name="Student2")
>>> subj1 = Subjects(name='subject1')
>>> subj2 = Subjects(name='subject2')
>>> subj3 = Subjects(name='subject3')
>>> subj4 = Subjects(name='subject4')
>>> db.session.add_all([student1, student2, subj1, subj2,subj3,subj4])
>>> db.session.commit()
>>> stu1 = Student.query.filter_by(id=1)
>>> stu2 = Student.query.filter_by(id=2).first()
>>> sub1 = Student.query.filter_by(name='subject1').first()
>>> sub2 = Subjects.query.filter_by(name='subject2').first()
>>> sub3 = Subjects.query.filter_by(name='subject3').first()
>>> sub4 = Subjects.query.filter_by(name='subject4').first()
>>> stu1.subj.extend([sub1,sub2,sub4])
>>> stu2.subj.extend([sub2,sub4])
>>> stu1.subj
[<Subjects 1>, <Subjects 2>, <Subjects 4>]
>>> subj2.student.all()
[<Student 2>, <Student 1>]
>>> >>> stu1.subj[0].name
'subject1'
>>> for subj in stu1.subj:
... print(subj.name)
...
subject1
subject2
subject4
>>> for stu in subj2.student:
... print(stu.name)
...
Student2
Student1
>>>
这只是一个非常基本的示例,当然您需要根据自己的情况对其进行扩展。您必须为每个db.realtionship
属性创建多个关联表,依此类推。
推荐阅读
- python - 通过 azure 自动化 Runbook 连接 azure blob 存储的 Python 代码
- mysql - MySQL - 编写一个 sql 查询来转置获取的记录?
- r - 获取 json 时如何接受 gdpr cookie?
- python - Scipy锯齿波非恒定峰值
- c# - 当没有聚合时,域逻辑应该去哪里?
- c# - 使用linq c#在datagridview中显示特定记录
- c - 如何在 C 中使用 OpenSSL 通过证书的 keyid 获取 EVP_PKEY?
- swift - 为什么在尝试传递数据时 init() 会影响导航链接?
- java - 在 Android 中保存地图的最佳和最快方法是什么?
- html - 如何删除输入范围后面的白色背景?