diff options
Diffstat (limited to 'lib/sqlalchemy/sql/elements.py')
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 63 |
1 files changed, 54 insertions, 9 deletions
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 7c16f9785..de17aabb3 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -1360,6 +1360,12 @@ class TextClause(Executable, ClauseElement): .. deprecated:: 0.9.0 the :meth:`.TextClause.columns` method supersedes the ``typemap`` argument to :func:`.text`. + .. seealso:: + + :ref:`sqlexpression_text` - in the Core tutorial + + :ref:`orm_tutorial_literal_sql` - in the ORM tutorial + """ stmt = TextClause(text, bind=bind) if bindparams: @@ -1485,9 +1491,17 @@ class TextClause(Executable, ClauseElement): mytable.join(stmt, mytable.c.name == stmt.c.name) ).where(stmt.c.id > 5) - Above, we used untyped :func:`.column` elements. These can also have - types specified, which will impact how the column behaves in - expressions as well as determining result set behavior:: + Above, we pass a series of :func:`.column` elements to the + :meth:`.TextClause.columns` method positionally. These :func:`.column` + elements now become first class elements upon the :attr:`.TextAsFrom.c` + column collection, just like any other selectable. + + The column expressions we pass to :meth:`.TextClause.columns` may + also be typed; when we do so, these :class:`.TypeEngine` objects become + the effective return type of the column, so that SQLAlchemy's + result-set-processing systems may be used on the return values. + This is often needed for types such as date or boolean types, as well + as for unicode processing on some dialect configurations:: stmt = text("SELECT id, name, timestamp FROM some_table") stmt = stmt.columns( @@ -1499,9 +1513,8 @@ class TextClause(Executable, ClauseElement): for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) - Keyword arguments allow just the names and types of columns to be - specified, where the :func:`.column` elements will be generated - automatically:: + As a shortcut to the above syntax, keyword arguments referring to + types alone may be used, if only type conversion is needed:: stmt = text("SELECT id, name, timestamp FROM some_table") stmt = stmt.columns( @@ -1513,6 +1526,31 @@ class TextClause(Executable, ClauseElement): for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) + The positional form of :meth:`.TextClause.columns` also provides + the unique feature of **positional column targeting**, which is + particularly useful when using the ORM with complex textual queries. + If we specify the columns from our model to :meth:`.TextClause.columns`, + the result set will match to those columns positionally, meaning the + name or origin of the column in the textual SQL doesn't matter:: + + stmt = text("SELECT users.id, addresses.id, users.id, " + "users.name, addresses.email_address AS email " + "FROM users JOIN addresses ON users.id=addresses.user_id " + "WHERE users.id = 1").columns( + User.id, + Address.id, + Address.user_id, + User.name, + Address.email_address + ) + + query = session.query(User).from_statement(stmt).options( + contains_eager(User.addresses)) + + .. versionadded:: 1.1 the :meth:`.TextClause.columns` method now + offers positional column targeting in the result set when + the column expressions are passed purely positionally. + The :meth:`.TextClause.columns` method provides a direct route to calling :meth:`.FromClause.alias` as well as :meth:`.SelectBase.cte` against a textual SELECT statement:: @@ -1526,15 +1564,22 @@ class TextClause(Executable, ClauseElement): :meth:`.TextClause.columns` method. This method supersedes the ``typemap`` argument to :func:`.text`. + """ - input_cols = [ + positional_input_cols = [ ColumnClause(col.key, types.pop(col.key)) if col.key in types else col for col in cols - ] + [ColumnClause(key, type_) for key, type_ in types.items()] - return selectable.TextAsFrom(self, input_cols) + ] + keyed_input_cols = [ + ColumnClause(key, type_) for key, type_ in types.items()] + + return selectable.TextAsFrom( + self, + positional_input_cols + keyed_input_cols, + positional=bool(positional_input_cols) and not keyed_input_cols) @property def type(self): |