SQL的方化关系多对多 x 2.

Question

我正在尝试将事务表连接到每个成员有很多事务但每个事务有两个成员（买方和卖方）的成员表。 如何创建一种关系，以便为每笔交易获取卖家的会员 ID 和买家的会员 ID？

类成员（db.模型，用户混合）：

__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.模型，用户混合）：

__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.模型，用户混合）：

__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}"

我得到了一个未定义的"关系"，但这很明显，因为我不明白如何如上所述连接表。

Answer 1

您在这里缺少的是一个关联表，它应该链接需要具有多对多关系的两个表。下面是一个非常简单的多对多关系示例，它应该让您了解如何从那里开始。

这就是您的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属性创建多个关联表，依此类推。

Answer 2

好的...这太愚蠢了。一旦我将db放在关系前面，我就停止了错误，所以db.relations。 我还没有建立交易表单来来回测试关系，但该网站至少启动并运行。 感谢。