column expressions with bound parameters in views created by SQLAlchemy

[richard reitmeyer]

Hello, all.

I'm trying to create a view that uses CASE statements in the column expression to categorize, so that all the logic is in setting up the view. Then nothing else needs to know all the rules, and if / when the rules change, I can simply drop and re-create the view without changing any other queries --- in SA or outside it.

EG

CREATE TABLE people ( name VARCHAR(10), income INTEGER );

INSERT INTO people (name, income) VALUES ( 'bob', 123456);

INSERT INTO people (name, income) VALUES ( 'barry', 35467);

INSERT INTO people (name, income) VALUES ( 'betty', 95672);

CREATE VIEW people_income_class AS SELECT name, income, CASE WHEN income < 50054 THEN 'below median' ELSE 'above median' END AS income_class FROM people;

In this case I'd like to have the people_income_class view come from a SA select, with the median income, 50054, coming from a bound parameter.

The recipe I've found for view creation, http://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy, is great at defining views with a bound parameter in the WHERE clause, but literal_binds=TRUE isn't picking up the parameter in the CASE statement. Before I do much more tracing of the paths through the compiler, I thought I'd ask.

Anyone have suggestions?

Richard

[Michael Bayer]

I hit the same thing the other day so http://www.sqlalchemy.org/trac/ticket/2593 will fix it in 0.8.

for now use literal_column().

literal_column("'below median'")

etc

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/WI-FkyMymsgJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.