It defines a new CalculatedClause() class similar to that of Function(), and refactors Function() to derive from this new class.
Also adds new visit_calculatedclause() function to handle the compile-time part.
It requires the use of label() on the CalculatedClause when it's used in a subselect. I was going to auto-generate labels when they're not supplied in the fashion that bind params do, but the patch was beginning to get rather invasive, which I don't want to do right before a major release and, it's kind of a corner case anyway -- just use label() for now.
I've also attached a modifed version of the testcase, so you can see how the label() works. It also changes the table alias from 'inner' to 'q_inner'. I tested with databases other than Sqlite, and the 'inner' name causes problems.
Copying Mike as well
Rick
On 5/14/06, Gambit <[EMAIL PROTECTED]> wrote:
Hey Rick,
Basically the issue as I see it is that whatever decides that something is a
'column', and thus part of the result set, doesn't (for the obvious reasons)
know what to do with the CompoundClause. Now this might be rectified as
simply as implementing a new class, say, arbitrarily, 'CalculatedClause' that
implements or derives from ColumnClause providing interfaces like _get_label,
visit_column for the callback, and the ability to specify a type for the
result (column.type). I'm not very familiar with the internals, but just
glancing through the code this seems like the right direction to go, if you're
willing :) If you're busy, let me know and I'll see about cornering mike into
giving me his opinion :)
Cheers!
-G
On Sunday, May 14, 2006, 4:50:41 AM, you wrote:
> Hey Gambit,
> Yeah, we'll need a way to alias the column created by the subquery. Have you
> tried using the "use_labels" switch on the inner query?
> Rick
> On 5/13/06, Gambit <[EMAIL PROTECTED]> wrote:
>>
>> Hey Rick,
>>
>> I was playing with this patch a bit and noticed that while it seems pretty
>> good for select()'s, it falls apart in sub-selects. It doesn't propagate
>> as
>> a column, thus doesn't get an alias for the outer select to work off of,
>> and
>> it all falls apart :)
>>
>> Hopefully there's an easy way to solve this :D Test case enclosed! Patch
>> as
>> you included it in your mail was applied against rev 1453.
>>
>> Cheers!
>> -G
>>
>> On Friday, May 12, 2006, 12:17:18 AM, you wrote:
>> > Hey Mike:
>>
>> > Attached please find a patch against trunk r#1441 that implements the
>> SQL
>> > CASE construct.
>>
>> > I know you've argued in the past for an implementation in some kind of
>> > .ext-land, but let me at least present a few arguments for inclusion in
>> the
>> > core library. I'll do this in the form of responses to some points in
>> your
>> > latest post on this subject way back on St. Patrick's day (begora):
>>
>> > On 3/17/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> >>
>> >> I love the CASE statement, but yah I think its a cleaner in a view
>> >>
>>
>> > Well, only if the condition and result expressions used are invariant.
>> If
>> > that's the case, you may as well use a simple lookup table in a join. In
>> my
>> > particular use case, the condition and result expressions used in the
>> WHEN
>> > clauses change on almost every single query.
>>
>> > (also what DB's support this one ? let me guess, PG and Oracle and
>> thats
>> >> it....oh and MSSQL-9.nobodyhasit :)
>> >>
>>
>> > ...actually CASE is pretty well supported across the board, PG, Oracle,
>> > SQLite, MySQL all support it, and MS-SQL has had it since like version
>> > 6.5back in 1996.
>>
>> > ). You can use literal strings too for this kind of thing.
>>
>> > Sure but that's effective only if the expressions are rather trivial.
>> Not
>> > having to build out string representations of SQL is one of the reasons
>> I
>> > use SA in the first place! :-)
>>
>>
>> > if you have some brilliant notion of how this would even look as Python
>> >> expressions, that would be interesting to see. It would definitely
>> live as
>> >> a plugged-in extension. I should make an effort to formally define the
>> >> "extensions" idea I have so that people can contribute whatever plugins
>> they
>> >> want.
>> >>
>>
>>
>> > Well, I am certainly not claiming brilliance, but I will stand up for
>> > "stupid simple". The meat of the patch is only about 10 lines long.
>>
>> > Here's a short doc:
>>
>> > case(whenlist, [value=value-expr], [else_=else-expr])
>>
>> > whenlist: A list of pairs.
>> > Each pair is itself a sequence or iterator yielding a
>> > sequence
>> > of two elements.
>>
>> > Each two-element pair represents a "WHEN" block of an SQL
>> > CASE _expression_. The first element of the pair is an
>> > _expression_
>> > that represents the WHEN condition, and the second is an
>> > _expression_ that represents the THEN result.
>>
>> > The optional [value] _expression_, if present, sets up a
>> > "simple case" SQL clause. If present, then each condition
>> > in the whenlist must evaluate to a constant value to
>> which
>> > the
>> > result of the value-expr is compared.
>>
>> > The optional [else_] _expression_ represents the optional
>> > "ELSE"
>> > clause in the SQL CASE construct.
>>
>>
>> > Some examples:
>>
>> > case([[MyTable.c.MyColumn < 2, 'First Bucket'], [
>> MyTable.c.MyColumn >>= 2, 'Second Bucket']])
>>
>> > yields:
>> > CASE WHEN MyTable.MyColumn < 2 THEN 'First Bucket'
>> > WHEN MyTable.MyColumn >= 2 THEN 'Second Bucket'
>> > END
>>
>> > -------------------------------------
>>
>> > case([(100, 'one hundred'), (5, 'five'), (82,
>> > 'quatre-vingts deux')],
>> > value=MyTable.c.MyColumn,
>> > else_='some other number')
>>
>> > yields:
>> > CASE MyTable.MyColumn
>> > WHEN 100 THEN 'one hundred'
>> > WHEN 5 THEN 'five'
>> > WHEN 82 THEN 'quatre-vingts deux'
>> > ELSE 'some other number'
>> > END
>>
>> > OK, this doc isn't really correct, as proper literal string quoting
>> requires
>> > the literal() function, but you get the idea.
>>
>> > CASE is a pretty basic construct, and I think it belongs right up there
>> with
>> > our friends DISTINCT and OUTER JOIN in the main library. Can ya spare a
>> guy
>> > space for 10 lines of code?
>>
>> > Thanks,
>> > Rick
>>
>>
SQL-CASE-2.patch
Description: Binary data
import sys
from sqlalchemy import *
engine = create_engine('sqlite', {'filename':':memory:'}, echo=True, echo_uow=True)
objectstore.LOG = True
info_table = Table('infos', engine,
Column('pk', Integer, primary_key=True),
Column('info', String))
info_table.create()
info_table.insert().execute(
{'pk':1, 'info':'pk_1_data'},
{'pk':2, 'info':'pk_2_data'},
{'pk':3, 'info':'pk_3_data'},
{'pk':4, 'info':'pk_4_data'},
{'pk':5, 'info':'pk_5_data'})
inner = select([case([[info_table.c.pk < 3, literal('lessthan3', type=String)],
[info_table.c.pk >= 3, literal('gt3', type=String)]]).label('x'),
info_table.c.pk, info_table.c.info], from_obj=[info_table]).alias('q_inner')
inner_result = inner.execute().fetchall()
# Outputs:
# lessthan3 1 pk_1_data
# lessthan3 2 pk_2_data
# gt3 3 pk_3_data
# gt3 4 pk_4_data
# gt3 5 pk_5_data
for i in inner_result:
print i[0], i[1], i[2]
outer = select([inner])
outer_result = outer.execute().fetchall()
# Fails on KeyError (there aren't enough columns)
for i in outer_result:
print i[0], i[1], i[2]
