如何防止 sqlalchemy 在 CTE 的列名前加上前缀? [英] How can I prevent sqlalchemy from prefixing the column names of a CTE?
问题描述
考虑以下通过 SQLAlchemy 编码的查询.
Consider the following query codified via SQLAlchemy.
# Create a CTE that performs a join and gets some values
x_cte = session.query(SomeTable.col1
,OtherTable.col5
) \
.select_from(SomeTable) \
.join(OtherTable, SomeTable.col2 == OtherTable.col3)
.filter(OtherTable.col6 == 34)
.cte(name='x')
# Create a subquery that splits the CTE based on the value of col1
# and computes the quartile for positive col1 and assigns a dummy
# "quartile" for negative and zero col1
subquery = session.query(x_cte
,literal('-1', sqlalchemy.INTEGER).label('quartile')
) \
.filter(x_cte.col1 <= 0)
.union_all(session.query(x_cte
,sqlalchemy.func.ntile(4).over(order_by=x_cte.col1).label('quartile')
)
.filter(x_cte.col1 > 0)
) \
.subquery()
# Compute some aggregate values for each quartile
result = session.query(sqlalchemy.func.avg(subquery.columns.x_col1)
,sqlalchemy.func.avg(subquery.columns.x_col5)
,subquery.columns.x_quartile
) \
.group_by(subquery.columns.x_quartile) \
.all()
抱歉篇幅过长，但这与我的真实查询相似.在我的实际代码中，我为我的 CTE 提供了一个更具描述性的名称，我的 CTE 有更多的列，我必须计算平均值.(它实际上也是 CTE 中某列加权的加权平均值.)
Sorry for the length, but this is similar to my real query. In my real code, I've given a more descriptive name to my CTE, and my CTE has far more columns for which I must compute the average. (It's also actually a weighted average weighted by a column in the CTE.)
真正的问题"纯粹是试图让我的代码更清晰、更简短.(是的，我知道.这个查询已经是一个怪物并且难以阅读，但客户端坚持要求这些数据可用.)请注意，在最终查询中，我必须将我的列称为 subquery.columns.x_[列名];这是因为 SQLAlchemy 在我的列名前加上 CTE 名称.我只想让 SQLAlchemy 在生成列名时省略我的 CTE 名称，但由于我有很多列，我不想在我的子查询中单独列出它们.不使用 CTE 名称会使我的列名称(它们本身足够长)更短且更易读；我可以保证列是唯一的.我该怎么做?
The real "problem" is purely one of trying to keep my code more clear and shorter. (Yes, I know. This query is already a monster and hard to read, but the client insists on this data being available.) Notice that in the final query, I must refer to my columns as subquery.columns.x_[column name]; this is because SQLAlchemy is prefixing my column name with the CTE name. I would just like for SQLAlchemy to leave off my CTE's name when generating column names, but since I have many columns, I would prefer not to list them individually in my subquery. Leaving off the CTE name would make my column names (which are long enough on their own) shorter and slightly more readable; I can guarantee that the columns are unique. How can I do this?
使用 Python 2.7.3 和 SQLAlchemy 0.7.10.
Using Python 2.7.3 with SQLAlchemy 0.7.10.
推荐答案
你并没有太具体在这里x_"是什么，但如果这是最终结果，请使用 label() 为结果列指定任何名称想要:
you're not being too specific what "x_" is here, but if that's the final result, use label() to give the result columns whatever name you want:
row = session.query(func.avg(foo).label('foo_avg'), func.avg(bar).label('bar_avg')).first()
foo_avg = row['foo_avg'] # indexed access
bar_avg = row.bar_avg # attribute access
我无法在此处重现x_".这是一个测试:
I'm not able to reproduce the "x_" here. Here's a test:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = "a"
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)
s = Session()
subq = s.query(A).cte(name='x')
subq2 = s.query(subq, (subq.c.x + subq.c.y)).filter(A.x == subq.c.x).subquery()
print s.query(A).join(subq2, A.id == subq2.c.id).\
filter(subq2.c.x == A.x, subq2.c.y == A.y)
上面，你可以看到我可以参考 subq2.c. 没有问题，没有前缀x".如果您可以指定 SQLAlchemy 版本信息并完整填写您的示例，我可以按原样运行它以重现您的问题.
above, you can see I can refer to subq2.c.<colname> without issue, there is no "x" prepended. If you can please specify SQLAlchemy version information and fill out your example fully, I can run it as is in order to reproduce your issue.
这篇关于如何防止 sqlalchemy 在 CTE 的列名前加上前缀?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
