时间戳

首页 > 解决方案 > SQLAlchemy AmbiguousForeignKeysError

python - SQLAlchemy AmbiguousForeignKeysError

问题描述

我已经搜索了这个错误，但什么也不懂。我收到以下错误：

sqlalchemy.exc.AmbiguousForeignKeysError：无法确定关系 Sale.payments 上的父/子表之间的连接条件 - 有多个外键路径链接表。指定“foreign_keys”参数，提供那些列的列表，这些列应该被视为包含对父表的外键引用。

这是我的代码：

# -*- coding: utf-8 -*-

import sqlalchemy as sa
import bcrypt as bc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship, backref
from sqlalchemy_utils import database_exists, create_database

engine = sa.create_engine('sqlite:///data/db/nestopol.db')
if not database_exists(engine.url):
    create_database(engine.url)

session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()


class Staff(Base):

    __tablename__ = 'staffs'

    staff_id = sa.Column(sa.Integer, primary_key=True)
    username = sa.Column(sa.String(64), unique=True, index=True, nullable=False)

    # Needs a length if MySQL is used
    # password is 93 in length
    password = sa.Column(sa.String(124), nullable=False)
    admin = sa.Column(sa.Boolean, default=False, nullable=False)
    first_name = sa.Column(sa.String(26), nullable=False)
    last_name = sa.Column(sa.String(26), nullable=False)
    gender = sa.Column(sa.String(6), nullable=False)
    birthday = sa.Column(sa.String(11), nullable=False)
    mobile_number = sa.Column(sa.String(14), nullable=False)
    city = sa.Column(sa.String(26), nullable=False)
    state = sa.Column(sa.String(26), nullable=False)
    country = sa.Column(sa.String(26), nullable=False)
    address = sa.Column(sa.String(128), nullable=False)
    added_on = sa.Column(sa.DateTime)
    modified_on = sa.Column(sa.DateTime)
    customers = relationship('Customer', backref='staffs', lazy='select')
    products = relationship('Product', backref='staffs', lazy='select')
    categories = relationship('Category', backref='staffs', lazy='select')
    suppliers = relationship('Supplier', backref='staffs', lazy='select')
    supply = relationship('Supply', backref='staffs', lazy='select')
    sales = relationship('Sale', backref='staffs', lazy='select')
    items = relationship('Item', backref='staffs', lazy='select')
    payments = relationship('Payment', backref='staffs', lazy='select')

    def generate_password_hash(self, password):
        return bc.hashpw(self.password, bc.gensalt())

    def check_password_hash(self, plain_text_password, password):
        return bc.checkpw(plain_text_password, self.hashed_password)


class Customer(Base):

    __tablename__ = 'customers'

    customer_id = sa.Column(sa.Integer, primary_key=True)
    first_name = sa.Column(sa.String(26), nullable=False)
    last_name = sa.Column(sa.String(26), nullable=False)
    email = sa.Column(sa.String(64), unique=True, index=True, nullable=False)
    gender = sa.Column(sa.String(7), nullable=False)
    birthday = sa.Column(sa.String(11), nullable=False)
    phone = sa.Column(sa.String(14), nullable=False)
    city = sa.Column(sa.String(26), nullable=False)
    state = sa.Column(sa.String(26), nullable=False)
    country = sa.Column(sa.String(26), nullable=False)
    address = sa.Column(sa.String(128), nullable=False)
    description = sa.Column(sa.String(128), nullable=False)
    purchases = sa.Column(sa.Integer, nullable=False)
    expenditure = sa.Column(sa.Float, nullable=False)
    reward = sa.Column(sa.Integer, nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    modified_on = sa.Column(sa.DateTime)
    sales = relationship('Sale', backref='customers', lazy='select')
    items = relationship('Item', backref='customers', lazy='select')
    payments = relationship('Payment', backref='customers', lazy='select')



class Product(Base):

    __tablename__ = 'products'

    product_id = sa.Column(sa.Integer, primary_key=True)
    code = sa.Column(sa.String(128), unique=True, index=True, nullable=False)
    name = sa.Column(sa.String(26), nullable=False)
    category = sa.Column(sa.Integer, sa.ForeignKey('categories.product_category_id'))
    cost_price = sa.Column(sa.Float, nullable=False)
    selling_price = sa.Column(sa.Float, nullable=False)
    weight = sa.Column(sa.Float, nullable=False)
    stock = sa.Column(sa.Integer, nullable=False)
    discount = sa.Column(sa.Float, nullable=False)
    expires = sa.Column(sa.DateTime)
    supplier = sa.Column(sa.Integer, sa.ForeignKey('suppliers.supplier_id'))
    description = sa.Column(sa.String(128), nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    items = relationship('Item', backref='products', lazy='select')


class Category(Base):

    __tablename__ = 'categories'

    product_category_id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(26), nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    products = relationship('Product', backref='categories', lazy='select')


class Supplier(Base):

    __tablename__ = 'suppliers'

    supplier_id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(26), nullable=False)
    debt = sa.Column(sa.Float, nullable=False)
    email = sa.Column(sa.String(64), unique = True, index=True, nullable=False)
    phone = sa.Column(sa.String(14), nullable=False)
    city = sa.Column(sa.String(26), nullable=False)
    state = sa.Column(sa.String(26), nullable=False)
    country = sa.Column(sa.String(26), nullable=False)
    address = sa.Column(sa.Float, nullable=False)
    description = sa.Column(sa.String(200), nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    modified_on = sa.Column(sa.DateTime)
    products = relationship('Product', backref='suppliers', lazy='select')
    supplies = relationship('Supply', backref='suppliers', lazy='select')


class Supply(Base):

    __tablename__ = 'supplies'

    supply_id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.String(26), nullable=False)
    supplier = sa.Column(sa.Integer, sa.ForeignKey('suppliers.supplier_id'))
    items = sa.Column(sa.Integer, nullable=False)
    value = sa.Column(sa.Float, nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)

class Sale(Base):

    __tablename__ = 'sales'

    sale_id = sa.Column(sa.Integer, primary_key=True)
    code = sa.Column(sa.String(8), unique=True, index=True, nullable=False)
    title = sa.Column(sa.String(26), nullable=False)
    customer = sa.Column(sa.Integer, sa.ForeignKey('customers.customer_id'))
    total = sa.Column(sa.Float, nullable=False)
    discount = sa.Column(sa.Float, nullable=False)
    vat = sa.Column(sa.Float, nullable=False)
    payment_method = sa.Column(sa.String(26), nullable=False)
    payment = sa.Column(sa.Integer, sa.ForeignKey('payments.payment_id'))
    state = sa.Column(sa.String(26), nullable=False)
    item = sa.Column(sa.Integer, sa.ForeignKey('items.item_id'))
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    payments = relationship('Payment', backref='sales', lazy='select')
    items = relationship('Item', backref='sales', lazy='select')

class Item(Base):

    __tablename__ = 'items'

    item_id = sa.Column(sa.Integer, primary_key=True)
    sale = sa.Column(sa.Integer, sa.ForeignKey('sales.sale_id'))
    customer = sa.Column(sa.Integer, sa.ForeignKey('customers.customer_id'))
    item = sa.Column(sa.Integer, sa.ForeignKey('products.product_id'))
    quantity = sa.Column(sa.Integer, nullable=False)
    total = sa.Column(sa.Float, nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    sales = relationship('Sale', backref='items', lazy='select')


class Payment(Base):

    __tablename__ = 'payments'

    payment_id = sa.Column(sa.Integer, primary_key=True)
    sale = sa.Column(sa.Integer, sa.ForeignKey('sales.sale_id'))
    customer = sa.Column(sa.Integer, sa.ForeignKey('customers.customer_id'))
    status = sa.Column(sa.String(26), nullable=False)
    amount_paid = sa.Column(sa.Float, nullable=False)
    amount_due = sa.Column(sa.Float, nullable=False)
    comment = sa.Column(sa.String(128), nullable=False)
    author = sa.Column(sa.Integer, sa.ForeignKey('staffs.staff_id'))
    added_on = sa.Column(sa.DateTime)
    modified_on = sa.Column(sa.DateTime)
    sales = relationship('Sale', backref='payments', lazy='select')



Base.metadata.create_all(engine)

请问我该如何解决这个问题

标签： pythonsqlalchemy

解决方案

以下代码重现了该问题（另请注意，您使用过的地方backref，我已修改为back_populates，您需要探索两者之间的区别）：

class Sale(Base):
    __tablename__ = 'sales'
    sale_id = sa.Column(sa.Integer, primary_key=True)
    payment = sa.Column(sa.Integer, sa.ForeignKey('payments.payment_id'))
    payments = relationship('Payment', back_populates='sales', lazy='select')

class Payment(Base):
    __tablename__ = 'payments'
    payment_id = sa.Column(sa.Integer, primary_key=True)
    sale = sa.Column(sa.Integer, sa.ForeignKey('sales.sale_id'))
    sales = relationship('Sale', back_populates='payments', lazy='select')

Sale()

当我运行该代码时，我收到错误：

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between
parent/child tables on relationship Sale.payments - there are multiple foreign key 
paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of 
those columns which should be counted as containing a foreign key reference to the 
parent table.

让我们分解异常消息：

无法确定关系 Sale.payments 上的父/子表之间的连接条件 - 有多个外键路径链接这些表。

这是告诉你什么是错的。Sqlalchemy 不知道如何创建关系Sale.payments。这是因为，如果没有提供显式连接条件，sqlalchemy 会查看表之间的外键以获取指导。在这种情况下，有两个外键链接表，Sale.payment是一个 FKPayment.payment_id和Payment.sale一个 FK 到Sale.sale_id。这就是连接条件“不明确”的原因——因为两个表之间有两个潜在的连接路径。

指定“foreign_keys”参数，提供那些列的列表，这些列应该被视为包含对父表的外键引用。

这是告诉你如何解决问题。我们可以这样做，它会起作用：

class Sale(Base):
    __tablename__ = 'sales'
    sale_id = sa.Column(sa.Integer, primary_key=True)
    payment = sa.Column(sa.Integer, sa.ForeignKey('payments.payment_id'))
    payments = relationship('Payment', back_populates='sales', lazy='select',
                            foreign_keys=[payment])

class Payment(Base):
    __tablename__ = 'payments'
    payment_id = sa.Column(sa.Integer, primary_key=True)
    sale = sa.Column(sa.Integer, sa.ForeignKey('sales.sale_id'))
    sales = relationship('Sale', back_populates='payments', lazy='select',
                         foreign_keys=[Sale.payment])

但我认为这里真正的问题是两个外键不是必需的。此代码在没有循环 FK 的情况下实现了完全相同的结果：

class Sale(Base):
    __tablename__ = 'sales'
    sale_id = sa.Column(sa.Integer, primary_key=True)
    payments = relationship('Payment', back_populates='sales', lazy='select')

class Payment(Base):
    __tablename__ = 'payments'
    payment_id = sa.Column(sa.Integer, primary_key=True)
    sale = sa.Column(sa.Integer, sa.ForeignKey('sales.sale_id'))
    sales = relationship('Sale', back_populates='payments', lazy='select')

现在循环 FK refs 消失了，两个表之间只有一个 FK 路径，sqlalchemy 可以很容易地推断出关系的正确连接路径，我们不需要指定foreign_keys参数。

推荐阅读