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
select_case.log
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)]]),
info_table.c.pk, info_table.c.info],
from_obj=[info_table]).alias('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]
