sqlalchemy.sql.elements.TextClause

Undocumented

Undocumented

Establish the values and/or types of bound parameters within this _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 _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 .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 .String and .DateTime.

When specific typing behavior is needed, the positional *binds argument can be used in which to specify .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 .DateTime for the timestamp bind, and the type of .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 _expression.TextClause.bindparams method can be called repeatedly, where it will re-use existing .BindParameter objects to add new information. For example, we can call _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 _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 _expression.text constructs may be combined together without the names conflicting. To use this feature, specify the :paramref:`.BindParameter.unique` flag on each .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

Turn this _expression.TextClause object into a _expression.TextualSelect object that serves the same role as a SELECT statement.

The _expression.TextualSelect is part of the _expression.SelectBase hierarchy and can be embedded into another statement by using the _expression.TextualSelect.subquery method to produce a .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 _expression.column elements to the _expression.TextClause.columns method positionally. These _expression.column elements now become first class elements upon the _expression.TextualSelect.selected_columns column collection, which then become part of the .Subquery.c collection after _expression.TextualSelect.subquery is invoked.

The column expressions we pass to _expression.TextClause.columns may also be typed; when we do so, these .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 _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 _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))

The _expression.TextClause.columns method provides a direct route to calling _expression.FromClause.subquery as well as _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)

Apply a 'grouping' to this _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 _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.Select.alias method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of 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 self_group method of _expression.ClauseElement just returns self.