Query Tools

Query.py consists out of several useful classes to work with the User, Script, Variable, Commit and CommitVar classes.

class query.CommitTool(sess)

CommitTool is a Commit helper. It can add a commit; return the top commits (latest) and return commit specific information.

add(user, script, time, vars)

Add a commit to the system.

info(cid)

Return commit object of the commit with id cid.

top(_offset=0, _limit=10)

Return the newest commits (ordered by the time they were added, or rather; by their id; descending.

class query.ScriptTool(sess)

ScriptTool is a Script helper. It can return the top scripts and return script specific information; such as vars and time. It can also list the commits made to a script.

info(sid, cache=False)
Return script information:
  • Script Object
  • Variables committed to the script
  • Time committed to the script (the amount of commits and their total time)
listc(script, _offset=0, _limit=10)

List the commits of the script.

top(_offset=0, _limit=10, cache=False)

Return the top scripts; based on the time committed to the script.

class query.UserTool(sess)

UserTool is a User helper class. It doesn’t have to be OOP; and OOP may be dropped later on depending whether I still plan to make a caching mechanism or not.

info(uid, cache=False)

Returns the user object with id uid and the time and commit count of the user.

info_script(uid, sid, cache=False)

Returns the user object, script object, time statistics and variables commits by the user with their amount.

listc(user, _offset=0, _limit=10)

Return the commits made by user.

listc_script(uid, sid, _offset=0, _limit=10)

Return the commits made by user to a specific script. Returns the user object as user; the script as script and the commits as commits.

top(_offset=0, _limit=10, cache=False)

Return the top limit users based on time added.

class query.VariableTool(sess)

VariableTool is the Variable helper. It can return the top variables based on the amount the variable has been committed; it can also return variable specific information.

info(variableid, cache=False)

Return information about the variable: The total commit amount plus the Variable object.

top(_offset=0, _limit=10, only_vars=False, cache=False)

Return the top Variables.

query.and_(*clauses)

Produce a conjunction of expressions joined by AND.

E.g.:

from sqlalchemy import and_

stmt = select([users_table]).where(
                and_(
                    users_table.c.name == 'wendy',
                    users_table.c.enrolled == True
                )
            )

The and_() conjunction is also available using the Python & operator (though note that compound expressions need to be parenthesized in order to function with Python operator precedence behavior):

stmt = select([users_table]).where(
                (users_table.c.name == 'wendy') &
                (users_table.c.enrolled == True)
            )

The and_() operation is also implicit in some cases; the Select.where() method for example can be invoked multiple times against a statement, which will have the effect of each clause being combined using and_():

stmt = select([users_table]).\
            where(users_table.c.name == 'wendy').\
            where(users_table.c.enrolled == True)

See also

or_()

query.asc(column)

Produce an ascending ORDER BY clause element.

e.g.:

from sqlalchemy import asc
stmt = select([users_table]).order_by(asc(users_table.c.name))

will produce SQL as:

SELECT id, name FROM user ORDER BY name ASC

The asc() function is a standalone version of the ColumnElement.asc() method available on all SQL expressions, e.g.:

stmt = select([users_table]).order_by(users_table.c.name.asc())
Parameters:column – A ColumnElement (e.g. scalar SQL expression) with which to apply the asc() operation.

See also

desc()

nullsfirst()

nullslast()

Select.order_by()

query.bindparam(key, value=symbol('NO_ARG'), type_=None, unique=False, required=symbol('NO_ARG'), quote=None, callable_=None, isoutparam=False, _compared_to_operator=None, _compared_to_type=None)

Produce a “bound expression”.

The return value is an instance of BindParameter; this is a ColumnElement subclass which represents a so-called “placeholder” value in a SQL expression, the value of which is supplied at the point at which the statement in executed against a database connection.

In SQLAlchemy, the bindparam() construct has the ability to carry along the actual value that will be ultimately used at expression time. In this way, it serves not just as a “placeholder” for eventual population, but also as a means of representing so-called “unsafe” values which should not be rendered directly in a SQL statement, but rather should be passed along to the DBAPI as values which need to be correctly escaped and potentially handled for type-safety.

When using bindparam() explicitly, the use case is typically one of traditional deferment of parameters; the bindparam() construct accepts a name which can then be referred to at execution time:

from sqlalchemy import bindparam

stmt = select([users_table]).\
            where(users_table.c.name == bindparam('username'))

The above statement, when rendered, will produce SQL similar to:

SELECT id, name FROM user WHERE name = :username

In order to populate the value of :username above, the value would typically be applied at execution time to a method like Connection.execute():

result = connection.execute(stmt, username='wendy')

Explicit use of bindparam() is also common when producing UPDATE or DELETE statements that are to be invoked multiple times, where the WHERE criterion of the statement is to change on each invocation, such as:

stmt = (users_table.update().
        where(user_table.c.name == bindparam('username')).
        values(fullname=bindparam('fullname'))
        )

connection.execute(
    stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
           {"username": "jack", "fullname": "Jack Jones"},
           ]
)

SQLAlchemy’s Core expression system makes wide use of bindparam() in an implicit sense. It is typical that Python literal values passed to virtually all SQL expression functions are coerced into fixed bindparam() constructs. For example, given a comparison operation such as:

expr = users_table.c.name == 'Wendy'

The above expression will produce a BinaryExpression construct, where the left side is the Column object representing the name column, and the right side is a BindParameter representing the literal value:

print(repr(expr.right))
BindParameter('%(4327771088 name)s', 'Wendy', type_=String())

The expression above will render SQL such as:

user.name = :name_1

Where the :name_1 parameter name is an anonymous name. The actual string Wendy is not in the rendered string, but is carried along where it is later used within statement execution. If we invoke a statement like the following:

stmt = select([users_table]).where(users_table.c.name == 'Wendy')
result = connection.execute(stmt)

We would see SQL logging output as:

SELECT "user".id, "user".name
FROM "user"
WHERE "user".name = %(name_1)s
{'name_1': 'Wendy'}

Above, we see that Wendy is passed as a parameter to the database, while the placeholder :name_1 is rendered in the appropriate form for the target database, in this case the Postgresql database.

Similarly, bindparam() is invoked automatically when working with CRUD statements as far as the “VALUES” portion is concerned. The insert() construct produces an INSERT expression which will, at statement execution time, generate bound placeholders based on the arguments passed, as in:

stmt = users_table.insert()
result = connection.execute(stmt, name='Wendy')

The above will produce SQL output as:

INSERT INTO "user" (name) VALUES (%(name)s)
{'name': 'Wendy'}

The Insert construct, at compilation/execution time, rendered a single bindparam() mirroring the column name name as a result of the single name parameter we passed to the Connection.execute() method.

Parameters:
  • key – the key (e.g. the name) for this bind param. Will be used in the generated SQL statement for dialects that use named parameters. This value may be modified when part of a compilation operation, if other BindParameter objects exist with the same key, or if its length is too long and truncation is required.
  • value – Initial value for this bind param. Will be used at statement execution time as the value for this parameter passed to the DBAPI, if no other value is indicated to the statement execution method for this particular parameter name. Defaults to None.
  • callable_ – A callable function that takes the place of “value”. The function will be called at statement execution time to determine the ultimate value. Used for scenarios where the actual bind value cannot be determined at the point at which the clause construct is created, but embedded bind values are still desirable.
  • type_

    A TypeEngine class or instance representing an optional datatype for this bindparam(). If not passed, a type may be determined automatically for the bind, based on the given value; for example, trivial Python types such as str, int, bool may result in the String, Integer or Boolean types being autoamtically selected.

    The type of a bindparam() is significant especially in that the type will apply pre-processing to the value before it is passed to the database. For example, a bindparam() which refers to a datetime value, and is specified as holding the DateTime type, may apply conversion needed to the value (such as stringification on SQLite) before passing the value to the database.

  • unique – if True, the key name of this BindParameter will be modified if another BindParameter of the same name already has been located within the containing expression. This flag is used generally by the internals when producing so-called “anonymous” bound expressions, it isn’t generally applicable to explicitly-named bindparam() constructs.
  • required

    If True, a value is required at execution time. If not passed, it defaults to True if neither :paramref:`.bindparam.value` or :paramref:`.bindparam.callable` were passed. If either of these parameters are present, then :paramref:`.bindparam.required` defaults to False.

    Changed in version 0.8: If the required flag is not specified, it will be set automatically to True or False depending on whether or not the value or callable parameters were specified.

  • quote – True if this parameter name requires quoting and is not currently known as a SQLAlchemy reserved word; this currently only applies to the Oracle backend, where bound names must sometimes be quoted.
  • isoutparam – if True, the parameter should be treated like a stored procedure “OUT” parameter. This applies to backends such as Oracle which support OUT parameters.

See also

coretutorial_bind_param

coretutorial_insert_expressions

outparam()

query.case(whens, value=None, else_=None)

Produce a CASE expression.

The CASE construct in SQL is a conditional object that acts somewhat analogously to an “if/then” construct in other languages. It returns an instance of Case.

case() in its usual form is passed a list of “when” constructs, that is, a list of conditions and results as tuples:

from sqlalchemy import case

stmt = select([users_table]).\
            where(
                case(
                    [
                        (users_table.c.name == 'wendy', 'W'),
                        (users_table.c.name == 'jack', 'J')
                    ],
                    else_='E'
                )
            )

The above statement will produce SQL resembling:

SELECT id, name FROM user
WHERE CASE
    WHEN (name = :name_1) THEN :param_1
    WHEN (name = :name_2) THEN :param_2
    ELSE :param_3
END

When simple equality expressions of several values against a single parent column are needed, case() also has a “shorthand” format used via the :paramref:`.case.value` parameter, which is passed a column expression to be compared. In this form, the :paramref:`.case.whens` parameter is passed as a dictionary containing expressions to be compared against keyed to result expressions. The statement below is equivalent to the preceding statement:

stmt = select([users_table]).\
            where(
                case(
                    {"wendy": "W", "jack": "J"},
                    value=users_table.c.name,
                    else_='E'
                )
            )

The values which are accepted as result values in :paramref:`.case.whens` as well as with :paramref:`.case.else_` are coerced from Python literals into bindparam() constructs. SQL expressions, e.g. ColumnElement constructs, are accepted as well. To coerce a literal string expression into a constant expression rendered inline, use the literal_column() construct, as in:

from sqlalchemy import case, literal_column

case(
    [
        (
            orderline.c.qty > 100,
            literal_column("'greaterthan100'")
        ),
        (
            orderline.c.qty > 10,
            literal_column("'greaterthan10'")
        )
    ],
    else_=literal_column("'lessthan10'")
)

The above will render the given constants without using bound parameters for the result values (but still for the comparison values), as in:

CASE
    WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
    WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
    ELSE 'lessthan10'
END
Parameters:
  • whens

    The criteria to be compared against, :paramref:`.case.whens` accepts two different forms, based on whether or not :paramref:`.case.value` is used.

    In the first form, it accepts a list of 2-tuples; each 2-tuple consists of (<sql expression>, <value>), where the SQL expression is a boolean expression and “value” is a resulting value, e.g.:

    case([
        (users_table.c.name == 'wendy', 'W'),
        (users_table.c.name == 'jack', 'J')
    ])
    

    In the second form, it accepts a Python dictionary of comparison values mapped to a resulting value; this form requires :paramref:`.case.value` to be present, and values will be compared using the == operator, e.g.:

    case(
        {"wendy": "W", "jack": "J"},
        value=users_table.c.name
    )
    
  • value – An optional SQL expression which will be used as a fixed “comparison point” for candidate values within a dictionary passed to :paramref:`.case.whens`.
  • else_ – An optional SQL expression which will be the evaluated result of the CASE construct if all expressions within :paramref:`.case.whens` evaluate to false. When omitted, most databases will produce a result of NULL if none of the “when” expressions evaulate to true.
query.cast(expression, type_)

Produce a CAST expression.

cast() returns an instance of Cast.

E.g.:

from sqlalchemy import cast, Numeric

stmt = select([
            cast(product_table.c.unit_price, Numeric(10, 4))
        ])

The above statement will produce SQL resembling:

SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product

The cast() function performs two distinct functions when used. The first is that it renders the CAST expression within the resulting SQL string. The second is that it associates the given type (e.g. TypeEngine class or instance) with the column expression on the Python side, which means the expression will take on the expression operator behavior associated with that type, as well as the bound-value handling and result-row-handling behavior of the type.

Changed in version 0.9.0: cast() now applies the given type to the expression such that it takes effect on the bound-value, e.g. the Python-to-database direction, in addition to the result handling, e.g. database-to-Python, direction.

An alternative to cast() is the type_coerce() function. This function performs the second task of associating an expression with a specific type, but does not render the CAST expression in SQL.

Parameters:
  • expression – A SQL expression, such as a ColumnElement expression or a Python string which will be coerced into a bound literal value.
  • type – A TypeEngine class or instance indicating the type to which the CAST should apply.

See also

type_coerce() - Python-side type coercion without emitting CAST.

query.delete(table, whereclause=None, bind=None, returning=None, prefixes=None, **dialect_kw)

Construct Delete object.

Similar functionality is available via the delete() method on Table.

Parameters:
  • table – The table to delete rows from.
  • whereclause – A ClauseElement describing the WHERE condition of the DELETE statement. Note that the where() generative method may be used instead.

See also

deletes - SQL Expression Tutorial

query.desc(column)

Produce a descending ORDER BY clause element.

e.g.:

from sqlalchemy import desc

stmt = select([users_table]).order_by(desc(users_table.c.name))

will produce SQL as:

SELECT id, name FROM user ORDER BY name DESC

The desc() function is a standalone version of the ColumnElement.desc() method available on all SQL expressions, e.g.:

stmt = select([users_table]).order_by(users_table.c.name.desc())
Parameters:column – A ColumnElement (e.g. scalar SQL expression) with which to apply the desc() operation.

See also

asc()

nullsfirst()

nullslast()

Select.order_by()

query.distinct(expr)

Produce an column-expression-level unary DISTINCT clause.

This applies the DISTINCT keyword to an individual column expression, and is typically contained within an aggregate function, as in:

from sqlalchemy import distinct, func
stmt = select([func.count(distinct(users_table.c.name))])

The above would produce an expression resembling:

SELECT COUNT(DISTINCT name) FROM user

The distinct() function is also available as a column-level method, e.g. ColumnElement.distinct(), as in:

stmt = select([func.count(users_table.c.name.distinct())])

The distinct() operator is different from the Select.distinct() method of Select, which produces a SELECT statement with DISTINCT applied to the result set as a whole, e.g. a SELECT DISTINCT expression. See that method for further information.

See also

ColumnElement.distinct()

Select.distinct()

func

query.except_(*selects, **kwargs)

Return an EXCEPT of multiple selectables.

The returned object is an instance of CompoundSelect.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.except_all(*selects, **kwargs)

Return an EXCEPT ALL of multiple selectables.

The returned object is an instance of CompoundSelect.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.exists(*args, **kwargs)

Construct a new Exists against an existing Select object.

Calling styles are of the following forms:

# use on an existing select()
s = select([table.c.col1]).where(table.c.col2==5)
s = exists(s)

# construct a select() at once
exists(['*'], **select_arguments).where(criterion)

# columns argument is optional, generates "EXISTS (SELECT *)"
# by default.
exists().where(table.c.col2==5)
query.extract(field, expr, **kwargs)

Return a Extract construct.

This is typically available as extract() as well as func.extract from the func namespace.

query.false()

Return a constant False_ construct.

E.g.:

>>> from sqlalchemy import false
>>> print select([t.c.x]).where(false())
SELECT x FROM t WHERE false

A backend which does not support true/false constants will render as an expression against 1 or 0:

>>> print select([t.c.x]).where(false())
SELECT x FROM t WHERE 0 = 1

The true() and false() constants also feature “short circuit” operation within an and_() or or_() conjunction:

>>> print select([t.c.x]).where(or_(t.c.x > 5, true()))
SELECT x FROM t WHERE true

>>> print select([t.c.x]).where(and_(t.c.x > 5, false()))
SELECT x FROM t WHERE false

Changed in version 0.9: true() and false() feature better integrated behavior within conjunctions and on dialects that don’t support true/false constants.

See also

true()

query.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Construct an Insert object.

Similar functionality is available via the insert() method on Table.

Parameters:
  • tableTableClause which is the subject of the insert.
  • values – collection of values to be inserted; see Insert.values() for a description of allowed formats here. Can be omitted entirely; a Insert construct will also dynamically render the VALUES clause at execution time based on the parameters passed to Connection.execute().
  • inline – if True, SQL defaults will be compiled ‘inline’ into the statement and not pre-executed.

If both values and compile-time bind parameters are present, the compile-time bind parameters override the information specified within values on a per-key basis.

The keys within values can be either Column objects or their string identifiers. Each key may reference one of:

  • a literal data value (i.e. string, number, etc.);
  • a Column object;
  • a SELECT statement.

If a SELECT statement is specified which references this INSERT statement’s table, the statement will be correlated against the INSERT statement.

See also

coretutorial_insert_expressions - SQL Expression Tutorial

inserts_and_updates - SQL Expression Tutorial

query.intersect(*selects, **kwargs)

Return an INTERSECT of multiple selectables.

The returned object is an instance of CompoundSelect.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.intersect_all(*selects, **kwargs)

Return an INTERSECT ALL of multiple selectables.

The returned object is an instance of CompoundSelect.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.join(left, right, onclause=None, isouter=False)

Produce a Join object, given two FromClause expressions.

E.g.:

j = join(user_table, address_table,
         user_table.c.id == address_table.c.user_id)
stmt = select([user_table]).select_from(j)

would emit SQL along the lines of:

SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id

Similar functionality is available given any FromClause object (e.g. such as a Table) using the FromClause.join() method.

Parameters:
  • left – The left side of the join.
  • right – the right side of the join; this is any FromClause object such as a Table object, and may also be a selectable-compatible object such as an ORM-mapped class.
  • onclause – a SQL expression representing the ON clause of the join. If left at None, FromClause.join() will attempt to join the two tables based on a foreign key relationship.
  • isouter – if True, render a LEFT OUTER JOIN, instead of JOIN.

See also

FromClause.join() - method form, based on a given left side

Join - the type of object produced

query.null()

Return a constant Null construct.

query.or_(*clauses)

Produce a conjunction of expressions joined by OR.

E.g.:

from sqlalchemy import or_

stmt = select([users_table]).where(
                or_(
                    users_table.c.name == 'wendy',
                    users_table.c.name == 'jack'
                )
            )

The or_() conjunction is also available using the Python | operator (though note that compound expressions need to be parenthesized in order to function with Python operator precedence behavior):

stmt = select([users_table]).where(
                (users_table.c.name == 'wendy') |
                (users_table.c.name == 'jack')
            )

See also

and_()

query.outerjoin(left, right, onclause=None)

Return an OUTER JOIN clause element.

The returned object is an instance of Join.

Similar functionality is also available via the outerjoin() method on any FromClause.

Parameters:
  • left – The left side of the join.
  • right – The right side of the join.
  • onclause – Optional criterion for the ON clause, is derived from foreign key relationships established between left and right otherwise.

To chain joins together, use the FromClause.join() or FromClause.outerjoin() methods on the resulting Join object.

query.over(func, partition_by=None, order_by=None)

Produce an Over object against a function.

Used against aggregate or so-called “window” functions, for database backends that support window functions.

E.g.:

from sqlalchemy import over
over(func.row_number(), order_by='x')

Would produce “ROW_NUMBER() OVER(ORDER BY x)”.

Parameters:
  • func – a FunctionElement construct, typically generated by func.
  • partition_by – a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct.
  • order_by – a column element or string, or a list of such, that will be used as the ORDER BY clause of the OVER construct.

This function is also available from the func construct itself via the FunctionElement.over() method.

New in version 0.7.

query.select(columns=None, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, **kwargs)

Construct a new Select.

Similar functionality is also available via the FromClause.select() method on any FromClause.

All arguments which accept ClauseElement arguments also accept string arguments, which will be converted as appropriate into either text() or literal_column() constructs.

See also

coretutorial_selecting - Core Tutorial description of select().

Parameters:
  • columns

    A list of ColumnElement or FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of FromClause (typically Table or Alias objects), the FromClause.c collection is extracted to form a collection of ColumnElement objects.

    This parameter will also accept Text constructs as given, as well as ORM-mapped classes.

    Note

    The :paramref:`.select.columns` parameter is not available in the method form of select(), e.g. FromClause.select().

    See also

    Select.column()

    Select.with_only_columns()

  • whereclause

    A ClauseElement expression which will be used to form the WHERE clause. It is typically preferable to add WHERE criterion to an existing Select using method chaining with Select.where().

    See also

    Select.where()

  • from_obj

    A list of ClauseElement objects which will be added to the FROM clause of the resulting statement. This is equivalent to calling Select.select_from() using method chaining on an existing Select object.

    See also

    Select.select_from() - full description of explicit FROM clause specification.

  • autocommit

    Deprecated. Use .execution_options(autocommit=<True|False>) to set the autocommit option.

    See also

    Executable.execution_options()

  • bind=None – an Engine or Connection instance to which the resulting Select object will be bound. The Select object will otherwise automatically bind to whatever Connectable instances can be located within its contained ClauseElement members.
  • correlate=True

    indicates that this Select object should have its contained FromClause elements “correlated” to an enclosing Select object. It is typically preferable to specify correlations on an existing Select construct using Select.correlate().

    See also

    Select.correlate() - full description of correlation.

  • distinct=False

    when True, applies a DISTINCT qualifier to the columns clause of the resulting statement.

    The boolean argument may also be a column expression or list of column expressions - this is a special calling form which is understood by the Postgresql dialect to render the DISTINCT ON (<columns>) syntax.

    distinct is also available on an existing Select object via the distinct() method.

    See also

    Select.distinct()

  • for_update=False
    when True, applies FOR UPDATE to the end of the resulting statement.

    Deprecated since version 0.9.0: - use Select.with_for_update() to specify the structure of the FOR UPDATE clause.

    for_update accepts various string values interpreted by specific backends, including:

    • "read" - on MySQL, translates to LOCK IN SHARE MODE; on Postgresql, translates to FOR SHARE.
    • "nowait" - on Postgresql and Oracle, translates to FOR UPDATE NOWAIT.
    • "read_nowait" - on Postgresql, translates to FOR SHARE NOWAIT.

    See also

    Select.with_for_update() - improved API for specifying the FOR UPDATE clause.

  • group_by

    a list of ClauseElement objects which will comprise the GROUP BY clause of the resulting select. This parameter is typically specified more naturally using the Select.group_by() method on an existing Select.

    See also

    Select.group_by()

  • having

    a ClauseElement that will comprise the HAVING clause of the resulting select when GROUP BY is used. This parameter is typically specified more naturally using the Select.having() method on an existing Select.

    See also

    Select.having()

  • limit=None

    a numerical value which usually renders as a LIMIT expression in the resulting select. Backends that don’t support LIMIT will attempt to provide similar functionality. This parameter is typically specified more naturally using the Select.limit() method on an existing Select.

    See also

    Select.limit()

  • offset=None

    a numeric value which usually renders as an OFFSET expression in the resulting select. Backends that don’t support OFFSET will attempt to provide similar functionality. This parameter is typically specified more naturally using the Select.offset() method on an existing Select.

    See also

    Select.offset()

  • order_by

    a scalar or list of ClauseElement objects which will comprise the ORDER BY clause of the resulting select. This parameter is typically specified more naturally using the Select.order_by() method on an existing Select.

    See also

    Select.order_by()

  • use_labels=False

    when True, the statement will be generated using labels for each column in the columns clause, which qualify each column with its parent table’s (or aliases) name so that name conflicts between columns in different tables don’t occur. The format of the label is <tablename>_<column>. The “c” collection of the resulting Select object will use these names as well for targeting column members.

    This parameter can also be specified on an existing Select object using the Select.apply_labels() method.

    See also

    Select.apply_labels()

query.text(text, bind=None, bindparams=None, typemap=None, autocommit=None)

Construct a new TextClause clause, representing a textual SQL string directly.

E.g.:

fom sqlalchemy import text

t = text("SELECT * FROM users")
result = connection.execute(t)

The advantages text() provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally. The construct can also be provided with a .c collection of column elements, allowing it to be embedded in other SQL expression constructs as a subquery.

Bind parameters are specified by name, using the format :name. E.g.:

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

For SQL statements where a colon is required verbatim, as within an inline string, use a backslash to escape:

t = text("SELECT * FROM users WHERE name='\:username'")

The TextClause construct includes methods which can provide information about the bound parameters as well as the column values which would be returned from the textual statement, assuming it’s an executable SELECT type of statement. The TextClause.bindparams() method is used to provide bound parameter detail, and TextClause.columns() method allows specification of return columns including names and types:

t = text("SELECT * FROM users WHERE id=:user_id").\
        bindparams(user_id=7).\
        columns(id=Integer, name=String)

for id, name in connection.execute(t):
    print(id, name)

The text() construct is used internally in cases when a literal string is specified for part of a larger query, such as when a string is specified to the Select.where() method of Select. In those cases, the same bind parameter syntax is applied:

s = select([users.c.id, users.c.name]).where("id=:user_id")
result = connection.execute(s, user_id=12)

Using text() explicitly usually implies the construction of a full, standalone statement. As such, SQLAlchemy refers to it as an Executable object, and it supports the Executable.execution_options() method. For example, a text() construct that should be subject to “autocommit” can be set explicitly so using the :paramref:`.Connection.execution_options.autocommit` option:

t = text("EXEC my_procedural_thing()").\
        execution_options(autocommit=True)

Note that SQLAlchemy’s usual “autocommit” behavior applies to text() constructs implicitly - that is, statements which begin with a phrase such as INSERT, UPDATE, DELETE, or a variety of other phrases specific to certain backends, will be eligible for autocommit if no transaction is in progress.

Parameters:
  • text – the text of the SQL statement to be created. use :<param> to specify bind parameters; they will be compiled to their engine-specific format.
  • autocommit – Deprecated. Use .execution_options(autocommit=<True|False>) to set the autocommit option.
  • bind – an optional connection or engine to be used for this text query.
  • bindparams

    Deprecated. A list of bindparam() instances used to provide information about parameters embedded in the statement. This argument now invokes the TextClause.bindparams() method on the construct before returning it. E.g.:

    stmt = text("SELECT * FROM table WHERE id=:id",
              bindparams=[bindparam('id', value=5, type_=Integer)])
    

    Is equivalent to:

    stmt = text("SELECT * FROM table WHERE id=:id").\
              bindparams(bindparam('id', value=5, type_=Integer))
    

    Deprecated since version 0.9.0: the TextClause.bindparams() method supersedes the bindparams argument to text().

  • typemap

    Deprecated. A dictionary mapping the names of columns represented in the columns clause of a SELECT statement to type objects, which will be used to perform post-processing on columns within the result set. This parameter now invokes the TextClause.columns() method, which returns a TextAsFrom construct that gains a .c collection and can be embedded in other expressions. E.g.:

    stmt = text("SELECT * FROM table",
                  typemap={'id': Integer, 'name': String},
              )
    

    Is equivalent to:

    stmt = text("SELECT * FROM table").columns(id=Integer,
                                               name=String)
    

    Or alternatively:

    from sqlalchemy.sql import column
    stmt = text("SELECT * FROM table").columns(
                          column('id', Integer),
                          column('name', String)
                      )
    

    Deprecated since version 0.9.0: the TextClause.columns() method supersedes the typemap argument to text().

query.true()

Return a constant True_ construct.

E.g.:

>>> from sqlalchemy import true
>>> print select([t.c.x]).where(true())
SELECT x FROM t WHERE true

A backend which does not support true/false constants will render as an expression against 1 or 0:

>>> print select([t.c.x]).where(true())
SELECT x FROM t WHERE 1 = 1

The true() and false() constants also feature “short circuit” operation within an and_() or or_() conjunction:

>>> print select([t.c.x]).where(or_(t.c.x > 5, true()))
SELECT x FROM t WHERE true

>>> print select([t.c.x]).where(and_(t.c.x > 5, false()))
SELECT x FROM t WHERE false

Changed in version 0.9: true() and false() feature better integrated behavior within conjunctions and on dialects that don’t support true/false constants.

See also

false()

query.tuple_(*clauses, **kw)

Return a Tuple.

Main usage is to produce a composite IN construct:

from sqlalchemy import tuple_

tuple_(table.c.col1, table.c.col2).in_(
    [(1, 2), (5, 12), (10, 19)]
)

Warning

The composite IN construct is not supported by all backends, and is currently known to work on Postgresql and MySQL, but not SQLite. Unsupported backends will raise a subclass of DBAPIError when such an expression is invoked.

query.union(*selects, **kwargs)

Return a UNION of multiple selectables.

The returned object is an instance of CompoundSelect.

A similar union() method is available on all FromClause subclasses.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.union_all(*selects, **kwargs)

Return a UNION ALL of multiple selectables.

The returned object is an instance of CompoundSelect.

A similar union_all() method is available on all FromClause subclasses.

*selects
a list of Select instances.
**kwargs
available keyword arguments are the same as those of select().
query.update(table, whereclause=None, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)

Construct an Update object.

E.g.:

from sqlalchemy import update

stmt = update(users).where(users.c.id==5).\
        values(name='user #5')

Similar functionality is available via the update() method on Table:

stmt = users.update().\
            where(users.c.id==5).\
            values(name='user #5')
Parameters:
  • table – A Table object representing the database table to be updated.
  • whereclause

    Optional SQL expression describing the WHERE condition of the UPDATE statement. Modern applications may prefer to use the generative where() method to specify the WHERE clause.

    The WHERE clause can refer to multiple tables. For databases which support this, an UPDATE FROM clause will be generated, or on MySQL, a multi-table update. The statement will fail on databases that don’t have support for multi-table update statements. A SQL-standard method of referring to additional tables in the WHERE clause is to use a correlated subquery:

    users.update().values(name='ed').where(
            users.c.name==select([addresses.c.email_address]).\
                        where(addresses.c.user_id==users.c.id).\
                        as_scalar()
            )
    

    Changed in version 0.7.4: The WHERE clause can refer to multiple tables.

  • values

    Optional dictionary which specifies the SET conditions of the UPDATE. If left as None, the SET conditions are determined from those parameters passed to the statement during the execution and/or compilation of the statement. When compiled standalone without any parameters, the SET clause generates for all columns.

    Modern applications may prefer to use the generative Update.values() method to set the values of the UPDATE statement.

  • inline – if True, no attempt will be made to retrieve the SQL-generated default values to be provided within the statement; in particular, this allows SQL expressions to be rendered ‘inline’ within the statement without the need to pre-execute them beforehand; for backends that support “returning”, this turns off the “implicit returning” feature for the statement.

If both values and compile-time bind parameters are present, the compile-time bind parameters override the information specified within values on a per-key basis.

The keys within values can be either Column objects or their string identifiers (specifically the “key” of the Column, normally but not necessarily equivalent to its “name”). Normally, the Column objects used here are expected to be part of the target Table that is the table to be updated. However when using MySQL, a multiple-table UPDATE statement can refer to columns from any of the tables referred to in the WHERE clause.

The values referred to in values are typically:

  • a literal data value (i.e. string, number, etc.)
  • a SQL expression, such as a related Column, a scalar-returning select() construct, etc.

When combining select() constructs within the values clause of an update() construct, the subquery represented by the select() should be correlated to the parent table, that is, providing criterion which links the table inside the subquery to the outer table being updated:

users.update().values(
        name=select([addresses.c.email_address]).\
                where(addresses.c.user_id==users.c.id).\
                as_scalar()
    )

See also

inserts_and_updates - SQL Expression Language Tutorial