sqlalchemy.sql.elements.TextClause

Undocumented

Undocumented

Establish the values and/or types of bound parameters within this :class:`_expression.TextClause` construct. Given a text construct such as:: from sqlalchemy import text stmt = text("SELECT id, name FROM user WHERE name=:name " "AND timestamp=:timestamp") the :meth:`_expression.TextClause.bindparams` method can be used to establish the initial value of ``:name`` and ``:timestamp``, using simple keyword arguments:: stmt = stmt.bindparams(name='jack', timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) Where above, new :class:`.BindParameter` objects will be generated with the names ``name`` and ``timestamp``, and values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``, respectively. The types will be inferred from the values given, in this case :class:`.String` and :class:`.DateTime`. When specific typing behavior is needed, the positional ``*binds`` argument can be used in which to specify :func:`.bindparam` constructs directly. These constructs must include at least the ``key`` argument, then an optional value and type:: from sqlalchemy import bindparam stmt = stmt.bindparams( bindparam('name', value='jack', type_=String), bindparam('timestamp', type_=DateTime) ) Above, we specified the type of :class:`.DateTime` for the ``timestamp`` bind, and the type of :class:`.String` for the ``name`` bind. In the case of ``name`` we also set the default value of ``"jack"``. Additional bound parameters can be supplied at statement execution time, e.g.:: result = connection.execute(stmt, timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)) The :meth:`_expression.TextClause.bindparams` method can be called repeatedly, where it will re-use existing :class:`.BindParameter` objects to add new information. For example, we can call :meth:`_expression.TextClause.bindparams` first with typing information, and a second time with value information, and it will be combined:: stmt = text("SELECT id, name FROM user WHERE name=:name " "AND timestamp=:timestamp") stmt = stmt.bindparams( bindparam('name', type_=String), bindparam('timestamp', type_=DateTime) ) stmt = stmt.bindparams( name='jack', timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5) ) The :meth:`_expression.TextClause.bindparams` method also supports the concept of **unique** bound parameters. These are parameters that are "uniquified" on name at statement compilation time, so that multiple :func:`_expression.text` constructs may be combined together without the names conflicting. To use this feature, specify the :paramref:`.BindParameter.unique` flag on each :func:`.bindparam` object:: stmt1 = text("select id from table where name=:name").bindparams( bindparam("name", value='name1', unique=True) ) stmt2 = text("select id from table where name=:name").bindparams( bindparam("name", value='name2', unique=True) ) union = union_all( stmt1.columns(column("id")), stmt2.columns(column("id")) ) The above statement will render as:: select id from table where name=:name_1 UNION ALL select id from table where name=:name_2 .. versionadded:: 1.3.11 Added support for the :paramref:`.BindParameter.unique` flag to work with :func:`_expression.text` constructs.

Turn this :class:`_expression.TextClause` object into a :class:`_expression.TextualSelect` object that serves the same role as a SELECT statement. The :class:`_expression.TextualSelect` is part of the :class:`_expression.SelectBase` hierarchy and can be embedded into another statement by using the :meth:`_expression.TextualSelect.subquery` method to produce a :class:`.Subquery` object, which can then be SELECTed from. This function essentially bridges the gap between an entirely textual SELECT statement and the SQL expression language concept of a "selectable":: from sqlalchemy.sql import column, text stmt = text("SELECT id, name FROM some_table") stmt = stmt.columns(column('id'), column('name')).subquery('st') stmt = select(mytable).\ select_from( mytable.join(stmt, mytable.c.name == stmt.c.name) ).where(stmt.c.id > 5) Above, we pass a series of :func:`_expression.column` elements to the :meth:`_expression.TextClause.columns` method positionally. These :func:`_expression.column` elements now become first class elements upon the :attr:`_expression.TextualSelect.selected_columns` column collection, which then become part of the :attr:`.Subquery.c` collection after :meth:`_expression.TextualSelect.subquery` is invoked. The column expressions we pass to :meth:`_expression.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( column('id', Integer), column('name', Unicode), column('timestamp', DateTime) ) for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) 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( id=Integer, name=Unicode, timestamp=DateTime ) for id, name, timestamp in connection.execute(stmt): print(id, name, timestamp) The positional form of :meth:`_expression.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:`_expression.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:`_expression.TextClause.columns` method now offers positional column targeting in the result set when the column expressions are passed purely positionally. The :meth:`_expression.TextClause.columns` method provides a direct route to calling :meth:`_expression.FromClause.subquery` as well as :meth:`_expression.SelectBase.cte` against a textual SELECT statement:: stmt = stmt.columns(id=Integer, name=String).cte('st') stmt = select(sometable).where(sometable.c.id == stmt.c.id) :param \*cols: A series of :class:`_expression.ColumnElement` objects, typically :class:`_schema.Column` objects from a :class:`_schema.Table` or ORM level column-mapped attributes, representing a set of columns that this textual string will SELECT from. :param \**types: A mapping of string names to :class:`.TypeEngine` type objects indicating the datatypes to use for names that are SELECTed from the textual string. Prefer to use the ``*cols`` argument as it also indicates positional ordering.

Apply a 'grouping' to this :class:`_expression.ClauseElement`. This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by :func:`_expression.select` constructs when placed into the FROM clause of another :func:`_expression.select`. (Note that subqueries should be normally created using the :meth:`_expression.Select.alias` method, as many platforms require nested SELECT statements to be named). As expressions are composed together, the application of :meth:`self_group` is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like ``x OR (y AND z)`` - AND takes precedence over OR. The base :meth:`self_group` method of :class:`_expression.ClauseElement` just returns self.