On 9/8/15 12:03 PM, Mike Bayer wrote:
On 9/8/15 4:13 AM, Jacob Magnusson wrote:
Michael,
Is there any chance we might see support for /with_labels/ in this
use case? Even though my "solution" works, it's somewhat of an
annoyance with all these warnings being spit out:
/SAWarning: Column 'id' on table
<sqlalchemy.sql.selectable.Select at 0x110cd5d30; Select object>
being replaced by Column('id', Integer(), table=<Select object>,
primary_key=True, nullable=False), which has the same key.
Consider use_labels for select() statements./
It's not clear how with_labels() can be made to work with this without
a lot of re-thinking of many of the internal aspects of the Query and
related objects. OTOH, the Bundle object is fully customizable and
as I recommended, a custom approach that applies labels to Column
objects as they are passed and translates back as needed on the result
side is feasible right now; I'd rather add a labeling/nesting feature
to Bundle itself based on the discoveries gained by working with that
approach as a recipe.
I'm looking at this again, with_labels() doesn't even apply here because
that doesn't override a call to label(). the Query already invokes
with_labels on all select() statements anyway:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)
s = Session()
print s.query(Bundle('q', A.x, A.y))
produces:
SELECT a.x AS a_x, a.y AS a_y
FROM a
the original request referred to the use of label() explicitly:
print s.query(Bundle('q', A.x, A.y.label('y')))
which of course, has to render *exactly* that label(), anything else
would be a super surprise:
SELECT a.x AS a_x, a.y AS y
FROM a
Like i said originally, you just need a "key" argument on label(), so
you can say:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
x = Column(Integer)
y = Column(Integer)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([A(x=1, y=2), B(x=3, y=4)])
s.commit()
for row in s.query(
Bundle('root', Bundle('a', A.x, A.y.label(None, key='y')),
Bundle('b', B.x, B.y.label(None, key='y')))
):
print row.root.a.y, row.root.b.y
where you get a query like:
SELECT a.x AS a_x, a.y AS y_1, b.x AS b_x, b.y AS y_2
FROM a, b
and the result:
2 4
the patch is below. This is an easy feature add, if desired.
diff --git a/lib/sqlalchemy/orm/attributes.py
b/lib/sqlalchemy/orm/attributes.py
index 5440d6b..ef44d71 100644
--- a/lib/sqlalchemy/orm/attributes.py
+++ b/lib/sqlalchemy/orm/attributes.py
@@ -168,8 +168,8 @@ class QueryableAttribute(interfaces._MappedAttribute,
self._parententity,
of_type=cls)
- def label(self, name):
- return self._query_clause_element().label(name)
+ def label(self, name, key=None):
+ return self._query_clause_element().label(name, key=key)
def operate(self, op, *other, **kwargs):
return op(self.comparator, *other, **kwargs)
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 618b987..4bcb897 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -856,7 +856,7 @@ class ColumnElement(operators.ColumnOperators,
ClauseElement):
"""
return Cast(self, type_)
- def label(self, name):
+ def label(self, name, key=None):
"""Produce a column label, i.e. ``<columnname> AS <name>``.
This is a shortcut to the :func:`~.expression.label` function.
@@ -864,7 +864,7 @@ class ColumnElement(operators.ColumnOperators,
ClauseElement):
if 'name' is None, an anonymous label name will be generated.
"""
- return Label(name, self, self.type)
+ return Label(name, self, self.type, key=key)
@util.memoized_property
def anon_label(self):
@@ -3274,7 +3274,7 @@ class Label(ColumnElement):
__visit_name__ = 'label'
- def __init__(self, name, element, type_=None):
+ def __init__(self, name, element, type_=None, key=None):
"""Return a :class:`Label` object for the
given :class:`.ColumnElement`.
@@ -3304,7 +3304,11 @@ class Label(ColumnElement):
'%%(%d %s)s' % (id(self), getattr(element, 'name',
'anon'))
)
- self.key = self._label = self._key_label = self.name
+ self._label = self.name
+ if key is not None:
+ self.key = self._key_label = key
+ else:
+ self.key = self._key_label = self.name
self._element = element
self._type = type_
self._proxies = [element]
diff --git a/test/orm/test_bulk.py b/test/orm/test_bulk.py
index e2a1464..c209096 100644
--- a/test/orm/test_bulk.py
+++ b/test/orm/test_bulk.py
@@ -60,7 +60,7 @@ class BulkInsertUpdateTest(BulkTest,
_fixtures.FixtureTest):
objects = [
User(name="u1"),
User(name="u2"),
- User(name="u3")
+ User(name="u3"),
]
assert 'id' not in objects[0].__dict__
On Friday, April 10, 2015 at 12:19:31 AM UTC+2, Michael Bayer wrote:
On 4/9/15 1:50 PM, Jacob Magnusson wrote:
I have this case with a bundle that looks something like this:
|primate_bundle = Bundle( 'primate', Primate.name,
Bundle('wooden_tool', *[ WoodenTool.id, WoodenTool.name,
WoodenToolCategory.name.label('category'), ]),
Bundle('solid_tool', *[ SolidTool.id, SolidTool.name,
SolidToolCategory.name.label('category'), ]) ) |
Then I query it like this:
|session.query(primate_bundle) .select_from(Primate)
.join(WoodenTool, Primate.main_tool) .join(WoodenToolCategory,
WoodenTool.category_id == WoodenToolCategory.id)
.join(SolidTool, Primate.secondary_tool)
.join(SolidToolCategory, SolidTool.category_id ==
SolidToolCategory.id) .all() |
However, since the label for category name is the same within
both sub-bundles it will throw |Ambiguous column name| (because
the compiled SQL labels will be exactly the same). Adding
|.with_labels()| doesn’t fix it. Full traceback can be seen by
running the included examples. Commenting out one of the
|.label()| lines in the example makes it runnable. Do you guys
have a clean solution to support this use case? I really like
this feature of creating your own custom made results so it
would be a shame to not be able to do this.
OK well here's one hacky way, that we could better accommodate by
adding a "key" parameter to label(), this is sort of like your
other approach: ay = A.y.label(None) ay.key = 'foobar' bp =
B.p.label(None) bp.key = 'foobar' ab = Bundle( 'ab',
Bundle('a', *[ A.id, A.x, ay, ]),
Bundle('b', *[ B.id, B.q, bp, ]) )
The other way is to provide create_row_processor() as we describe
here:
http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_columns.html?highlight=bundle#column-bundles
<http://docs.sqlalchemy.org/en/rel_0_9/orm/loading_columns.html?highlight=bundle#column-bundles>.
That way you could just make this work any way you wanted: from
sqlalchemy import util class LabelBundle(Bundle): def
__init__(self, *arg, **kw): self.extra_labels =
kw.pop('extra_labels', {}) super(LabelBundle,
self).__init__(*arg, **kw) def create_row_processor(self,
query, procs, labels): # or use a NamedTuple here
keyed_tuple = util.lightweight_named_tuple( 'result',
[self.extra_labels.get(l, l) for l in labels]) def
proc(row): return keyed_tuple([proc(row) for proc in
procs]) return proc ab = LabelBundle( 'ab',
LabelBundle( 'a', extra_labels={'y': 'foobar'},
*[A.id, A.x, A.y]), LabelBundle( 'b',
extra_labels={'p': 'foobar'}, *[B.id, B.q, B.p]) )
Tested on SQLAlchemy 1.0.0b5 and 0.9.9. Python 3.
Thank you so much for any potential help you can give me on
this. I’ve followed the source code for |Bundle| but I can’t
think of a clean way to this…
-- You received this message because you are subscribed to the
Google Groups "sqlalchemy" group. To unsubscribe from this group
and stop receiving emails from it, send an email to
[email protected] <javascript:>. To post to this
group, send email to [email protected] <javascript:>.
Visit this group at http://groups.google.com/group/sqlalchemy
<http://groups.google.com/group/sqlalchemy>. For more options,
visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
-- You received this message because you are subscribed to the Google
Groups "sqlalchemy" group. To unsubscribe from this group and stop
receiving emails from it, send an email to
[email protected]
<mailto:[email protected]>. To post to this
group, send email to [email protected]
<mailto:[email protected]>. Visit this group at
http://groups.google.com/group/sqlalchemy. For more options, visit
https://groups.google.com/d/optout.
-- You received this message because you are subscribed to the Google
Groups "sqlalchemy" group. To unsubscribe from this group and stop
receiving emails from it, send an email to
[email protected]
<mailto:[email protected]>. To post to this
group, send email to [email protected]
<mailto:[email protected]>. Visit this group at
http://groups.google.com/group/sqlalchemy. For more options, visit
https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
