An Alias represents any FromClause
with an alternate name assigned within SQL, typically using the AS
clause when generated, e.g. SELECT*FROMtableASaliasname.

Similar functionality is available via the
alias() method
available on all FromClause subclasses.

When an Alias is created from a Table object,
this has the effect of the table being rendered
as tablenameASaliasname in a SELECT statement.

For select() objects, the effect is that of creating a named
subquery, i.e. (select...)ASaliasname.

The name parameter is optional, and provides the name
to use in the rendered SQL. If blank, an “anonymous” name
will be deterministically generated at compile time.
Deterministic means the name is guaranteed to be unique against
other constructs used in the same statement, and will also be the
same name for each successive compilation of the same statement
object.

Parameters:

selectable – any FromClause subclass,
such as a table, select statement, etc.

name – string name to be assigned as the alias.
If None, a name will be deterministically generated
at compile time.

key – the key 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 _BindParamClause objects exist with the same
key, or if its length is too long and truncation is
required.

value – Initial value for this bind param. This value may be
overridden by the dictionary of parameters sent to statement
compilation/execution.

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 object that will be used to pre-process the
value corresponding to this _BindParamClause at
execution time.

unique – if True, the key name of this BindParamClause will be
modified if another _BindParamClause of the same name
already has been located within the containing
ClauseElement.

A sequence of pairs, or alternatively a dict,
to be translated into “WHEN / THEN” clauses.

value

Optional for simple case statements, produces
a column expression as in “CASE <expr> WHEN ...”

else_

Optional as well, for case defaults produces
the “ELSE” portion of the “CASE” statement.

The expressions used for THEN and ELSE,
when specified as strings, will be interpreted
as bound values. To specify textual SQL expressions
for these, use the literal_column()
construct.

The expressions used for the WHEN criterion
may only be literal strings when “value” is
present, i.e. CASE table.somecol WHEN “x” THEN “y”.
Otherwise, literal strings are not accepted
in this position, and either the text(<string>)
or literal(<string>) constructs must be used to
interpret raw string values.

Return a textual column clause, as would be in the columns clause of a
SELECT statement.

The object returned is an instance of ColumnClause, which
represents the “syntactical” portion of the schema-level
Column object. It is often used directly
within select() constructs or with lightweight table()
constructs.

Note that the column() function is not part of
the sqlalchemy namespace. It must be imported from the sql package:

fromsqlalchemy.sqlimporttable,column

Parameters:

text – the name of the column. Quoting rules will be applied
to the clause like any other column name. For textual column constructs
that are not to be quoted, use the literal_column() function.

type_ – an optional TypeEngine object
which will provide result-set translation for this column.

# use on an existing select()s=select([table.c.col1]).where(table.c.col2==5)s=exists(s)# construct a select() at onceexists(['*'],**select_arguments).where(criterion)# columns argument is optional, generates "EXISTS (SELECT *)"# by default.exists().where(table.c.col2==5)

Any name can be given to func. If the function name is unknown to
SQLAlchemy, it will be rendered exactly as is. For common SQL functions
which SQLAlchemy is aware of, the name may be interpreted as a generic
function which will be compiled appropriately to the target database:

>>> printfunc.current_timestamp()CURRENT_TIMESTAMP

To call functions which are present in dot-separated packages, specify them in the same manner:

SQLAlchemy can be made aware of the return type of functions to enable
type-specific lexical and result-based behavior. For example, to ensure
that a string-based function returns a Unicode value and is similarly
treated as a string in expressions, specify
Unicode as the type:

The object returned by a func call is an instance of Function.
This object meets the “column” interface, including comparison and labeling
functions. The object can also be passed the execute()
method of a Connection or Engine, where it will be
wrapped inside of a SELECT statement first:

printconnection.execute(func.current_timestamp()).scalar()

A function can also be “bound” to a Engine or Connection
using the bind keyword argument, providing an execute() as well
as a scalar() method:

myfunc=func.current_timestamp(bind=some_engine)printmyfunc.scalar()

Functions which are interpreted as “generic” functions know how to
calculate their return type automatically. For a listing of known generic
functions, see Generic Functions.

values – A dictionary which specifies the column specifications of
the INSERT, and is optional. If left as None, the column
specifications are determined from the bind parameters used during the
compile phase of the INSERT statement. If the bind parameters also
are None during the compile phase, then the column specifications will be
generated from the full list of table columns. Note that the
values() generative method may also be used for this.

prefixes – A list of modifier keywords to be inserted between INSERT
and INTO. Alternatively, the prefix_with() generative
method may be used.

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.

Literal clauses are created automatically when non- ClauseElement
objects (such as strings, ints, dates, etc.) are used in a comparison
operation with a _CompareMixin
subclass, such as a Column object. Use this function to force the
generation of a literal clause, which will be created as a
_BindParamClause with a bound value.

Parameters:

value – the value to be bound. Can be any Python object supported by
the underlying DB-API, or is translatable via the given type argument.

type_ – an optional TypeEngine which
will provide bind-parameter translation for this literal.

Return a textual column expression, as would be in the columns
clause of a SELECT statement.

The object returned supports further expressions in the same way as any
other column object, including comparison, math and string operations.
The type_ parameter is important to determine proper expression behavior
(such as, ‘+’ means string concatenation or numerical addition based on
the type).

Parameters:

text – the text of the expression; can be any SQL expression.
Quoting rules will not be applied. To specify a column-name expression
which should be subject to quoting rules, use the column()
function.

type_ – an optional TypeEngine object which will
provide result-set translation and additional expression semantics for
this column. If left as None the type will be NullType.

Create an ‘OUT’ parameter for usage in functions (stored procedures),
for databases which support them.

The outparam can be used like a regular function parameter.
The “output” value will be available from the
ResultProxy object via its out_parameters
attribute, which returns a dictionary containing the values.

A list of ClauseElement objects, typically
ColumnElement objects or subclasses, which will form the
columns clause of the resulting statement. For all members which are
instances of Selectable, the individual ColumnElement
members of the Selectable will be added individually to the
columns clause. For example, specifying a
Table instance will result in all the
contained Column objects within to be added
to the columns clause.

This argument is not present on the form of select()
available on Table.

whereclause – A ClauseElement expression which will be used to form the
WHERE clause.

from_obj – A list of ClauseElement objects which will be added to the
FROM clause of the resulting statement. Note that “from” objects are
automatically located within the columns and whereclause ClauseElements.
Use this parameter to explicitly specify “from” objects which are not
automatically locatable. This could include
Table objects that aren’t otherwise present,
or Join objects whose presence will supercede that of the
Table objects already located in the other
clauses.

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

correlate=True – indicates that this Select object should have its
contained FromClause elements “correlated” to an enclosing
Select object. This means that any ClauseElement
instance within the “froms” collection of this Select
which is also present in the “froms” collection of an
enclosing select will not be rendered in the FROM clause
of this select statement.

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
DISTINCTON(<columns>) syntax.

The distinct keyword’s acceptance of a string
argument for usage with MySQL is deprecated. Use
the prefixes argument or prefix_with().

for_update=False –

when True, applies FORUPDATE to the end of the
resulting statement.

Certain database dialects also support
alternate values for this parameter:

With the MySQL dialect, the value "read" translates to
LOCKINSHAREMODE.

With the Oracle and Postgresql dialects, the value "nowait"
translates to FORUPDATENOWAIT.

With the Postgresql dialect, the values “read” and "read_nowait"
translate to FORSHARE and FORSHARENOWAIT, respectively.

New in version 0.7.7.

group_by – a list of ClauseElement objects which will comprise the
GROUPBY clause of the resulting select.

having – a ClauseElement that will comprise the HAVING clause
of the resulting select when GROUPBY is used.

limit=None – a numerical value which usually compiles to a LIMIT
expression in the resulting select. Databases that don’t
support LIMIT will attempt to provide similar
functionality.

offset=None – a numeric value which usually compiles to an OFFSET
expression in the resulting select. Databases that don’t
support OFFSET will attempt to provide similar
functionality.

order_by – a scalar or list of ClauseElement objects which will
comprise the ORDERBY clause of the resulting select.

prefixes – a list of strings or ClauseElement objects to include
directly after the SELECT keyword in the generated statement,
for dialect-specific query features. prefixes is
also available via the prefix_with()
generative method.

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.

use_labels is also available via the apply_labels()
generative method.

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.

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)

To invoke SQLAlchemy typing logic for bind parameters, the
bindparams list allows specification of bindparam()
constructs which specify the type for a given name:

t=text("SELECT id FROM users WHERE updated_at>:updated",bindparams=[bindparam('updated',DateTime())])

Typing during result row processing is also an important concern.
Result column types
are specified using the typemap dictionary, where the keys
match the names of columns. These names are taken from what
the DBAPI returns as cursor.description:

t=text("SELECT id, name FROM users",typemap={'id':Integer,'name':Unicode})

The text() construct is used internally for most cases when
a literal string is specified for part of a larger query, such as
within select(), update(),
insert() or delete(). In those cases, the same
bind parameter syntax is applied:

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 autocommit option:

Note that SQLAlchemy’s usual “autocommit” behavior applies to
text() constructs - 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 – a list of bindparam() instances which can be used to define
the types and/or initial values for the bind parameters within
the textual statement; the keynames of the bindparams must match
those within the text of the statement. The types will be used
for pre-processing on bind values.

typemap – 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 argument applies to any expression
that returns result sets.

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.

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 UPDATEFROM 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:

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, SQL defaults present on Column objects via
the default keyword will be compiled ‘inline’ into the statement
and not pre-executed. This means that their values will not
be available in the dictionary returned from
ResultProxy.last_updated_params().

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:

key – the key 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 _BindParamClause objects exist with the same
key, or if its length is too long and truncation is
required.

value – Initial value for this bind param. This value may be
overridden by the dictionary of parameters sent to statement
compilation/execution.

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 object that will be used to pre-process the
value corresponding to this _BindParamClause at
execution time.

unique – if True, the key name of this BindParamClause will be
modified if another _BindParamClause of the same name
already has been located within the containing
ClauseElement.

required – a value is required at execution time.

isoutparam – if True, the parameter should be treated like a stored procedure
“OUT” parameter.

The return value is a Compiled object.
Calling str() or unicode() on the returned value will yield a
string representation of the result. The
Compiled object also can return a
dictionary of bind parameter names and values
using the params accessor.

Parameters:

bind – An Engine or Connection from which a
Compiled will be acquired. This argument takes precedence over
this ClauseElement‘s bound engine, if any.

column_keys – Used for INSERT and UPDATE statements, a list of
column names which should be present in the VALUES clause of the
compiled statement. If None, all columns from the target table
object are rendered.

dialect – A Dialect instance from which a Compiled
will be acquired. This argument takes precedence over the bind
argument as well as this ClauseElement‘s bound engine, if
any.

inline – Used for INSERT statements, for a dialect which does
not support inline retrieval of newly generated primary key
columns, will force the expression used to create the new primary
key value to be rendered inline within the INSERT statement’s
VALUES clause. This typically refers to Sequence execution but may
also refer to any server-side default generation function
associated with a primary key Column.

**kwargs may contain flags that change the collection that is
returned, for example to return a subset of items in order to
cut down on larger traversals, or to return child items from a
different context (such as schema-level collections instead of
clause-level).

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 select() constructs when placed into
the FROM clause of another select(). (Note that
subqueries should be normally created using the
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 xOR(yANDz) - AND takes precedence
over OR.

There is also a variant on column() known
as literal_column() - the difference is that
in the latter case, the string value is assumed to be an exact
expression, rather than a column name, so that no quoting rules
or similar are applied:

is_literal – if True, the ColumnClause is assumed to
be an exact expression that will be delivered to the output with no
quoting rules applied regardless of case sensitive settings. the
literal_column() function is usually used to create such a
ColumnClause.

Represent an element that is usable within the “column clause” portion
of a SELECT statement.

This includes columns associated with tables, aliases, and
subqueries, expressions, function calls, SQL keywords such as
NULL, literals, etc. ColumnElement is the ultimate base
class for all such elements.

This is a label() expression which will be named at compile time.
The same label() is returned each time anon_label is called so
that expressions can reference anon_label multiple times, producing
the same label name at compile time.

the compiler uses this function automatically at compile time
for expressions that are known to be ‘unnamed’ like binary
expressions and function calls.

use_proxies – when True, consider two columns that
share a common base column as equivalent (i.e. shares_lineage())

equivalents – a dictionary of columns as keys mapped to sets
of columns. If the given “other” column is present in this
dictionary, if any of the columns in the corresponding set() pass the
comparison test, the result is True. This is used to expand the
comparison to other columns that may be known to be equivalent to
this one via foreign key or other criterion.

By default all methods call down to
Operators.operate() or Operators.reverse_operate()
passing in the appropriate operator function from the
Python builtin operator module or
a SQLAlchemy-specific operator function from
sqlalchemy.expression.operators. For example
the __eq__ function:

Normally, ISNOT is generated automatically when comparing to a
value of None, which resolves to NULL. However, explicit
usage of ISNOT may be desirable if comparing to boolean values
on certain platforms.

Set non-SQL options for the statement which take effect during
execution.

Execution options can be set on a per-statement or
per Connection basis. Additionally, the
Engine and ORM Query objects provide access
to execution options which they in turn configure upon connections.

The execution_options() method is generative. A new
instance of this statement is returned that contains the options:

Note that only a subset of possible execution options can be applied
to a statement - these include “autocommit” and “stream_results”,
but not “isolation_level” or “compiled_cache”.
See Connection.execution_options() for a full list of
possible options.

the given ColumnElement, if the given
ColumnElement is actually present within a sub-element
of this FromClause. Normally the column will match if
it merely shares a common ancestor with one of the exported
columns of this FromClause.

The given list of columns represent columns within the table that is
the target of the INSERT, UPDATE, or DELETE. Each element can be any
column expression. Table objects will be
expanded into their individual columns.

Upon compilation, a RETURNING clause, or database equivalent,
will be rendered within the statement. For INSERT and UPDATE,
the values are the newly inserted/updated values. For DELETE,
the values are those of the rows which were deleted.

Upon execution, the values of the columns to be returned
are made available via the result set and can be iterated
using fetchone() and similar. For DBAPIs which do not
natively support returning values (i.e. cx_oracle),
SQLAlchemy will approximate this behavior at the result level
so that a reasonable amount of behavioral neutrality is
provided.

Note that not all databases/DBAPIs
support RETURNING. For those backends with no support,
an exception is raised upon compilation and/or execution.
For those who do support it, the functionality across backends
varies greatly, including restrictions on executemany()
and other statements which return multiple rows. Please
read the documentation notes for the database in use in
order to determine the availability of RETURNING.

Used against a Join object,
alias() calls the select()
method first so that a subquery against a
select() construct is generated.
the select() construct also has the
correlate flag set to False and will not
auto-correlate inside an enclosing select()
construct.

whereclause – the WHERE criterion that will be sent to
the select() function

fold_equivalents – based on the join criterion of this
Join, do not include
repeat column names in the column list of the resulting
select, for columns that are calculated to be “equivalent”
based on the join criterion of this Join. This will
recursively apply to any joins directly nested by this one
as well.

**kwargs – all other kwargs are sent to the
underlying select() function.

The “from” list is a unique set on the identity of each element,
so adding an already present Table or other selectable
will have no effect. Passing a Join that refers
to an already present Table or other selectable will have
the effect of concealing the presence of that selectable as
an individual element in the rendered FROM list, instead rendering it into a
JOIN clause.

While the typical purpose of Select.select_from() is to replace
the default, derived FROM clause with a join, it can also be called with
individual table elements, multiple times if desired, in the case that the
FROM clause cannot be fully derived from the columns clause:

The text of the hint is rendered in the appropriate
location for the database backend in use, relative
to the given Table or Alias passed as the
selectable argument. The dialect implementation
typically uses Python string substitution syntax
with the token %(name)s to render the name of
the table or alias. E.g. when using Oracle, the
following:

select([mytable]).\
with_hint(mytable,"+ index(%(name)s ix_mytable)")

Would render SQL as:

select /*+ index(mytable ix_mytable) */ ... from mytable

The dialect_name option will limit the rendering of a particular
hint to a particular backend. Such as, to add hints for both Oracle
and Sybase simultaneously:

Return a new select() construct with its columns
clause replaced with the given columns.

Changed in version 0.7.3: Due to a bug fix, this method has a slight
behavioral change as of version 0.7.3.
Prior to version 0.7.3, the FROM clause of
a select() was calculated upfront and as new columns
were added; in 0.7.3 and later it’s calculated
at compile time, fixing an issue regarding late binding
of columns to parent tables. This changes the behavior of
Select.with_only_columns() in that FROM clauses no
longer represented in the new list are dropped,
but this behavior is more consistent in
that the FROM clauses are consistently derived from the
current columns clause. The original intent of this method
is to allow trimming of the existing columns list to be fewer
columns than originally present; the use case of replacing
the columns list with an entirely different one hadn’t
been anticipated until 0.7.3 was released; the usage
guidelines below illustrate how this should be done.

This method is exactly equivalent to as if the original
select() had been called with the given columns
clause. I.e. a statement:

s=select([table1.c.a,table1.c.b])s=s.with_only_columns([table1.c.b])

should be exactly equivalent to:

s=select([table1.c.b])

This means that FROM clauses which are only derived
from the column list will be discarded if the new column
list no longer contains that FROM:

Care should also be taken to use the correct
set of column objects passed to Select.with_only_columns().
Since the method is essentially equivalent to calling the
select() construct in the first place with the given
columns, the columns passed to Select.with_only_columns()
should usually be a subset of those which were passed
to the select() construct, not those which are available
from the .c collection of that select(). That
is:

This will result in column expressions being generated using labels
against their table name, such as “SELECT somecolumn AS
tablename_somecolumn”. This allows selectables which contain multiple
FROM clauses to produce a unique set of column names regardless of
name conflicts among the individual FROM clauses.

Common table expressions are a SQL standard whereby SELECT
statements can draw upon secondary statements specified along
with the primary statement, using a clause called “WITH”.
Special semantics regarding UNION can also be employed to
allow “recursive” queries, where a SELECT statement can draw
upon the set of rows that have previously been selected.

SQLAlchemy detects CTE objects, which are treated
similarly to Alias objects, as special elements
to be delivered to the FROM clause of the statement as well
as to a WITH clause at the top of the statement.

New in version 0.7.6.

Parameters:

name – name given to the common table expression. Like
_FromClause.alias(), the name can be left as None
in which case an anonymous symbol will be used at query
compile time.

recursive – if True, will render WITHRECURSIVE.
A recursive common table expression is intended to be used in
conjunction with UNION ALL in order to derive rows
from those already selected.

The constructor for TableClause is the
table() function. This produces
a lightweight table object that has only a name and a
collection of columns, which are typically produced
by the column() function:

The TableClause construct serves as the base for
the more commonly used Table object, providing
the usual set of FromClause services including
the .c. collection and statement generation methods.

It does not provide all the additional schema-level services
of Table, including constraints, references to other
tables, or support for MetaData-level services. It’s useful
on its own as an ad-hoc construct used to generate quick SQL
statements when a more fully fledged Table is not on hand.

The given list of columns represent columns within the table that is
the target of the INSERT, UPDATE, or DELETE. Each element can be any
column expression. Table objects will be
expanded into their individual columns.

Upon compilation, a RETURNING clause, or database equivalent,
will be rendered within the statement. For INSERT and UPDATE,
the values are the newly inserted/updated values. For DELETE,
the values are those of the rows which were deleted.

Upon execution, the values of the columns to be returned
are made available via the result set and can be iterated
using fetchone() and similar. For DBAPIs which do not
natively support returning values (i.e. cx_oracle),
SQLAlchemy will approximate this behavior at the result level
so that a reasonable amount of behavioral neutrality is
provided.

Note that not all databases/DBAPIs
support RETURNING. For those backends with no support,
an exception is raised upon compilation and/or execution.
For those who do support it, the functionality across backends
varies greatly, including restrictions on executemany()
and other statements which return multiple rows. Please
read the documentation notes for the database in use in
order to determine the availability of RETURNING.

The text of the hint is rendered in the appropriate
location for the database backend in use, relative
to the Table that is the subject of this
statement, or optionally to that of the given
Table passed as the selectable argument.

The dialect_name option will limit the rendering of a particular
hint to a particular backend. Such as, to add a hint
that only takes effect for SQL Server:

mytable.insert().with_hint("WITH (PAGLOCK)",dialect_name="mssql")

New in version 0.7.6.

Parameters:

text – Text of the hint.

selectable – optional Table that specifies
an element of the FROM clause within an UPDATE or DELETE
to be the subject of the hint - applies only to certain backends.

dialect_name – defaults to *, if specified as the name
of a particular dialect, will apply these hints only when
that dialect is in use.

SQL functions which are known to SQLAlchemy with regards to database-specific
rendering, return types and argument behavior. Generic functions are invoked
like all SQL functions, using the func attribute:

select([func.count()]).select_from(sometable)

Note that any name not known to func generates the function name as is
- there is no restriction on what SQL functions can be called, known or
unknown to SQLAlchemy, built-in or user defined. The section here only
describes those functions where SQLAlchemy already knows what argument and
return types are in use.