在sqlite数据库的flask中有一个表使用2个外键的问题

Question

这里我想在我的产品表中首先使用User(table) 2 foreignKeys作为我的客户，然后作为我的卖家，但是我遇到了一些问题

from app import db, login
from werkzeug.security import check_password_hash,generate_password_hash
from flask_login import UserMixin

@login.user_loader
def load_user(id):
return User.query.get(int(id))
#This is the user class of seller and customer 
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String, index=True, nullable = False)
last_name = db.Column(db.String, index=True, nullable = False)
username = db.Column(db.String(64), index=True, unique=True, nullable = False)
email = db.Column(db.String(120), index=True, unique=True, nullable = False)
password_hash = db.Column(db.String(128), nullable = False)
gender = db.Column(db.String(30), nullable = False)
user_type = db.Column(db.String(30), nullable = False)
phone_no = db.Column(db.String(15),index=True, unique=True, nullable = False)
wallet_amount = db.Column(db.Float)
address = db.Column(db.Text)
products = db.relationship('Product', backref = 'author', lazy = 'dynamic')
def __repr__(self):
return '<User {}>'.format(self.username)
def set_password(self,password):
self.password_hash = generate_password_hash(password)
def check_password(self,password):
return check_password_hash(self.password_hash,password)

#Product类包含User类的2个外键，但是得到一些错误

class Product(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(70), index = True,nullable = False)
description = db.Column(db.Text, nullable = False)
customer_id = db.Column(db.Integer, db.ForeignKey('user.id'))
seller_id = db.Column(db.Integer, db.ForeignKey('user.id'))
images = db.Column(db.Text, nullable = False)

def __repr__(self):
return '<Product {}>'.format(self.name)

Answer 1

"问题是在用户类中只有一列，但我们使用2个foreign_keys，这样我们就必须创建2列，首先是products_seller，其次是products_customer，并添加一个额外的参数(即外)键和产品表字段的合适列，所以现在它已经完成，我认为只有这一个可能的解决方案在我的脑海中，如果有人知道更好，那么请建议我。""从app导入db，登录从werkzeug。安全导入check_password_hash,generate_password_hash从flask_login导入UserMixin

@login.user_loader
def load_user(id):
return User.query.get(int(id))
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String, index=True, nullable = False)
last_name = db.Column(db.String, index=True, nullable = False)
username = db.Column(db.String(64), index=True, unique=True, nullable = False)
email = db.Column(db.String(120), index=True, unique=True, nullable = False)
password_hash = db.Column(db.String(128), nullable = False)
gender = db.Column(db.String(30), nullable = False)
user_type = db.Column(db.String(30), nullable = False)
phone_no = db.Column(db.String(15),index=True, unique=True, nullable = False)
wallet_amount = db.Column(db.Float)
address = db.Column(db.Text)
products_seller = db.relationship('Product', backref = 'author_seller', lazy = 'dynamic', foreign_keys = 'Product.customer_id')
products_customer = db.relationship('Product', backref = 'author_customer', lazy = 'dynamic', foreign_keys = 'Product.seller_id' )

def __repr__(self):
return '<User {}>'.format(self.username)
def set_password(self,password):
self.password_hash = generate_password_hash(password)
def check_password(self,password):
return check_password_hash(self.password_hash,password)
class Product(db.Model):
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(70), index = True,nullable = False)
description = db.Column(db.Text, nullable = False)
customer_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable = True)
seller_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable = True)
images = db.Column(db.Text, nullable = False)

def __repr__(self):
return '<Prodect {}>'.format(self.name)