SQLAlchemy 使用 load_only 和不同的 [英] SQLAlchemy using load_only with distinct

问题描述

我正在尝试在 SQLAlchemy 中使用 distinct 函数，但它似乎不起作用.我准备了一个小例子，你可以看到我的问题:

I'm trying to use the function distinct with SQLAlchemy but it doesn’t seem to work. I prepared a little example where you can see my problem:

#-*- coding: utf-8 -*-
from sqlalchemy import create_engine,Column, Integer
from sqlalchemy.orm import sessionmaker,load_only
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

class my_class(Base):
    __tablename__ = 'my_table'
    id= Column(Integer, primary_key=True)
    data= Column(Integer)

Base.metadata.create_all(engine)

for i in range(10):
    p=my_class()
    p.id=i
    p.data=55
    session.add(p)
session.commit()

s=session.query(my_class).distinct(my_class.data).options(load_only(my_class.data))

print (s)
for a in s.all():
    print (a.id,a.data)

执行这个我希望输出是这样的:

Executing this I would expect an output like this:

SELECT my_table.data AS my_table_data 
FROM my_table
None 55

但我得到了这个:

SELECT DISTINCT my_table.id AS my_table_id, my_table.data AS my_table_data 
FROM my_table
0 55
1 55
2 55
3 55
4 55
5 55
6 55
7 55
8 55
9 55

我做错了什么?

推荐答案

如果查询完整的 ORM 实体，推迟主键是没有意义的，因为 一个实体必须有一个身份，这样一个唯一的行才能被在数据库表中标识.因此，尽管您拥有 load_only()，但查询仍包含主键.如果你只想要数据，你应该专门查询:

Deferring the primary key would not make sense, if querying complete ORM entities, because an entity must have an identity so that a unique row can be identified in the database table. So the query includes the primary key though you have your load_only(). If you want the data only, you should query for that specifically:

In [12]: session.query(my_class.data).distinct().all()
2017-06-30 12:31:49,200 INFO sqlalchemy.engine.base.Engine SELECT DISTINCT my_table.data AS my_table_data 
FROM my_table
2017-06-30 12:31:49,200 INFO sqlalchemy.engine.base.Engine ()
Out[12]: [(55)]

实际上存在一个问题load_only() 确实从选择列表中删除了主键，它是 已在 0.9.5 中修复:

There actually was an issue where having load_only() did remove the primary key from the select list, and it was fixed in 0.9.5:

[orm] [bug] 修改了 orm.load_only() 这样主键列总是被添加到要取消延迟"的列列表中；否则，ORM 无法加载行的标识.显然，可以推迟映射的主键，ORM 将失败，这一点没有改变.但由于 load_only 本质上是在说推迟除 X 之外的所有内容"，更重要的是 PK cols 不是这种推迟的一部分.

[orm] [bug] Modified the behavior of orm.load_only() such that primary key columns are always added to the list of columns to be "undeferred"; otherwise, the ORM can’t load the row’s identity. Apparently, one can defer the mapped primary keys and the ORM will fail, that hasn’t been changed. But as load_only is essentially saying "defer all but X", it’s more critical that PK cols not be part of this deferral.

这篇关于SQLAlchemy 使用 load_only 和不同的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！