diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 22 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 63 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 3 |
3 files changed, 71 insertions, 17 deletions
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index c4e73a1e3..c5f87cc33 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -345,6 +345,18 @@ class SQLCompiler(Compiled): driver/DB enforces this """ + _textual_ordered_columns = False + """tell the result object that the column names as rendered are important, + but they are also "ordered" vs. what is in the compiled object here. + """ + + _ordered_columns = True + """ + if False, means we can't be sure the list of entries + in _result_columns is actually the rendered order. Usually + True unless using an unordered TextAsFrom. + """ + def __init__(self, dialect, statement, column_keys=None, inline=False, **kwargs): """Construct a new :class:`.SQLCompiler` object. @@ -386,11 +398,6 @@ class SQLCompiler(Compiled): # column targeting self._result_columns = [] - # if False, means we can't be sure the list of entries - # in _result_columns is actually the rendered order. This - # gets flipped when we use TextAsFrom, for example. - self._ordered_columns = True - # true if the paramstyle is positional self.positional = dialect.positional if self.positional: @@ -733,7 +740,8 @@ class SQLCompiler(Compiled): ) or entry.get('need_result_map_for_nested', False) if populate_result_map: - self._ordered_columns = False + self._ordered_columns = \ + self._textual_ordered_columns = taf.positional for c in taf.column_args: self.process(c, within_columns_clause=True, add_to_result_map=self._add_to_result_map) @@ -1326,7 +1334,7 @@ class SQLCompiler(Compiled): add_to_result_map = lambda keyname, name, objects, type_: \ self._add_to_result_map( keyname, name, - objects + (column,), type_) + (column,) + objects, type_) else: col_expr = column if populate_result_map: 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): diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 73341053d..1955fc934 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -3420,9 +3420,10 @@ class TextAsFrom(SelectBase): _textual = True - def __init__(self, text, columns): + def __init__(self, text, columns, positional=False): self.element = text self.column_args = columns + self.positional = positional @property def _bind(self): |