python - 使用唯一键返回多行？

Question

鉴于这 3 个简单的 SQLAlchemy 模型:

class Customer(ModelBase):
    __tablename__ = 'customers'

    id = sa.Column(sa.Integer, primary_key=True)
    uid = sa.Column(sa.Unicode, nullable=False, unique=True)


class Handset(ModelBase):
    __tablename__ = 'handsets'

    id = sa.Column(sa.Integer, primary_key=True)
    imei = sa.Column(sa.Unicode(15), nullable=False, unique=True)


class Channel(ModelBase):
    __tablename__ = 'channels'

    id = sa.Column(sa.Integer, primary_key=True)

    customer_id = sa.Column(sa.ForeignKey(Customer.id), nullable=False)
    handset_id = sa.Column(sa.ForeignKey(Handset.id), nullable=False)

    customer = relationship(Customer, backref='channels', lazy='joined')
    handset = relationship(Handset, backref='channels', lazy='joined')


    __table_args__ = (
        sa.Index('uk_channels_customer_handset',
                 customer_id, handset_id,
                 unique=True),
    )

我不明白为什么这个查询:

session.query(Channel).filter(Handset.imei == '1234', Customer.uid == 'test').one_or_none()

抛出 multiple rows found执行，当有多个 Channel与 handset_id 相关联, 但有不同的 customer_id而这个查询，工作正常:

c = aliased(Customer)
h = aliased(Handset)
session.query(Channel).enable_eagerloads(False).join(c).join(h).filter(
    c.uid == 'test', h.imei == '1234',
).one_or_none()

如何通过预先加载客户和手机来加载 channel ？

Answer 1

第一个查询在手机、客户和 channel 之间产生隐式交叉连接(这是一个多行的来源)+ 2 个左连接(来自客户和手机以进行预加载)，这可能不是您想要的:

In [7]: print(session.query(Channel).filter(Handset.imei == '1234', Customer.uid == 'test'))
SELECT channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id, customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei 
FROM handsets, customers, channels LEFT OUTER JOIN customers AS customers_1 ON customers_1.id = channels.customer_id LEFT OUTER JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE handsets.imei = ? AND customers.uid = ?

第二个在您显式定义连接并禁用预加载左连接时起作用。第一个查询将使用 has() 工作:

In [17]: print(session.query(Channel).filter(Channel.handset.has(imei='1234'), 
                                             Channel.customer.has(uid='test')))
SELECT channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id, customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei 
FROM channels LEFT OUTER JOIN customers AS customers_1 ON customers_1.id = channels.customer_id LEFT OUTER JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE (EXISTS (SELECT 1 
FROM handsets 
WHERE handsets.id = channels.handset_id AND handsets.imei = ?)) AND (EXISTS (SELECT 1 
FROM customers 
WHERE customers.id = channels.customer_id AND customers.uid = ?))

本质上，这会使用给定的谓词分别检查此 channel 是否存在手机和客户，然后将客户和手机加入此 channel 。

您还可以指示 SQLAlchemy 您正在使用 an explicit join in the second query and to eager load using that与 contains_eager() :

In [28]: print(session.query(Channel).\
    join(c).join(h).\
    options(contains_eager(Channel.handset, alias=h),
            contains_eager(Channel.customer, alias=c)).\
    filter(
        c.uid == 'test', h.imei == '1234',
    ))
SELECT customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei, channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id 
FROM channels JOIN customers AS customers_1 ON customers_1.id = channels.customer_id JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE customers_1.uid = ? AND handsets_1.imei = ?

在我看来这是更清洁的解决方案。