Better support for raw SQL select statements

[Andrey Popp]

Sometimes I want to write select statement as raw SQL and then be able to do subselects from it or join it to other sqlalchemy constructs. I wrote basic sqlalchemy.sql construct to do that and wonder if it will be useful to have it in sqlalchemy (of course it's quick and dirty, but could be refactored into something more clean). See https://github.com/andreypopp/saext/blob/master/saext/tests/test_textual.txt for test cases.

[Mike Bayer]

how about:

text("select a, b, c from t").as_select(["b", "c"]("a",))

text("select a, b, c from t").as_select([Integer), column("b", Integer), column("c", Integer)](column("a",))

remember, typing information is very important here. once you're giving yourself a .c collection you enter the big leagues. the whole expression package assumes that .c collections are predetermined by their parent so I'd be uncomfortable switching that paradigm in just this one case (I'd be entirely unsurprised if it led to unintended problems, though without experimenting I can't say what).

this also kind of points to adding chained methods for the "typemap" and "bindparam" parameters.

the above version, that we're just adding a little more information to text(), makes me more comfortable here as opposed to introducing a whole new subsystem - anytime we have a totally new way to do something we have to justify why this extra complexity is needed.

I certainly have no time to work on/support a feature like this for an indeterminate amount of time, the 0.7.7 and 0.8 roadmaps are quite full.

that said, this can be implemented as a quick recipe using @compiles and creating a subclass of Select with custom compilation.

[Andrey Popp]

remember, typing information is very important here. once you're giving yourself a .c collection you enter the big leagues. the whole expression package assumes that .c collections are predetermined by their parent so I'd be uncomfortable switching that paradigm in just this one case (I'd be entirely unsurprised if it led to unintended problems, though without experimenting I can't say what).

I don't quite get the very usefulness of typing information in dynamical language, I mean I will be upset both by hitting error in runtime in SQL database or in Python code. But the whole point of this is to wrap entire and just SQL SELECT string in executable/joinable/selectable statement which can be reused later. I think this fits perfectly with SQLAlchemy which is known to scale very well in complexity from basic usage patterns to much more sophisticated use cases.

For example I use such feature for specifying declarative mappings in YAML between relational schemas — sometimes I just describe data source as a table name, but sometimes I need to do some select, then I just enter it as a raw SQL and don't want to repeat column names separately.

[Mike Bayer]

I totally could not find this ticket a few weeks ago when I instead implemented this as #2877. See http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#new-text-capabilities which implements these and more features. The types are optional, but overall the typing information in SQLAlchemy is important for the cases of both expression behavior (e.g. an ARRAY type has [] for example, others don't, the "+" operator in a string does concatenation, etc.) as well as Python-level coersion (e.g. you might want to receive Decimal or floating point numbers, unicode, booleans for ints etc).

anyway, resolved as #2877.