sqlalchemy.sql.expression

Produce a BETWEEN predicate clause.

E.g.:

from sqlalchemy import between
stmt = select(users_table).where(between(users_table.c.id, 5, 7))

Would produce SQL resembling:

SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2

The .between function is a standalone version of the _expression.ColumnElement.between method available on all SQL expressions, as in:

stmt = select(users_table).where(users_table.c.id.between(5, 7))

All arguments passed to .between, including the left side column expression, are coerced from Python scalar values if a the value is not a _expression.ColumnElement subclass. For example, three fixed values can be compared as in:

print(between(5, 3, 7))

Which would produce:

:param_1 BETWEEN :param_2 AND :param_3

Return the clause expression COLLATE collation.

e.g.:

collate(mycolumn, 'utf8_bin')

produces:

mycolumn COLLATE utf8_bin

The collation expression is also quoted if it is a case sensitive identifier, e.g. contains uppercase characters.

Produce a SQL statement that is cached as a lambda.

The Python code object within the lambda is scanned for both Python literals that will become bound parameters as well as closure variables that refer to Core or ORM constructs that may vary. The lambda itself will be invoked only once per particular set of constructs detected.

E.g.:

from sqlalchemy import lambda_stmt

stmt = lambda_stmt(lambda: table.select())
stmt += lambda s: s.where(table.c.id == 5)

result = connection.execute(stmt)

The object returned is an instance of _sql.StatementLambdaElement.

Return a literal clause, bound to a bind parameter.

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

Produce a .ColumnClause object that has the :paramref:`_expression.column.is_literal` flag set to True.

_expression.literal_column is similar to _expression.column, except that it is more often used as a "standalone" column expression that renders exactly as stated; while _expression.column stores a string name that will be assumed to be part of a table and may be quoted as such, _expression.literal_column can be that, or any other arbitrary column-oriented expression.

Return a negation of the given clause, i.e. NOT(clause).

The ~ operator is also overloaded on all _expression.ColumnElement subclasses to produce the same result.

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 ~sqlalchemy.engine.CursorResult object via its out_parameters attribute, which returns a dictionary containing the values.