SQL Statements and Expressions API
Selectables, Tables, FROM objects
The most fundamental part of the SQL expression API are the “column elements”,
which allow for basic SQL expression support. The core of all SQL expression
constructs is the ClauseElement
, which is the base for several
sub-branches. The ColumnElement
class is the fundamental unit
used to construct any kind of typed SQL expression.
sqlalchemy.sql.expression.
and_
(*clauses)¶Join a list of clauses together using the AND
operator.
The &
operator is also overloaded on all ColumnElement
subclasses to produce the
same result.
sqlalchemy.sql.expression.
asc
(column)¶Return an ascending ORDER BY
clause element.
e.g.:
someselect.order_by(asc(table1.mycol))
produces:
ORDER BY mycol ASC
sqlalchemy.sql.expression.
between
(ctest, cleft, cright)¶Return a BETWEEN
predicate clause.
Equivalent of SQL clausetest BETWEEN clauseleft AND clauseright
.
The between()
method on all
ColumnElement
subclasses provides
similar functionality.
sqlalchemy.sql.expression.
bindparam
(key, value=<symbol 'NO_ARG>, type_=None, unique=False, required=<symbol 'NO_ARG>, quote=None, callable_=None)¶Create a bind parameter clause with the given key.
Parameters: |
|
---|
sqlalchemy.sql.expression.
case
(whens, value=None, else_=None)¶Produce a 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.
Usage examples:
case([(orderline.c.qty > 100, item.c.specialprice),
(orderline.c.qty > 10, item.c.bulkprice)
], else_=item.c.regularprice)
case(value=emp.c.type, whens={
'engineer': emp.c.salary * 1.1,
'manager': emp.c.salary * 3,
})
Using literal_column()
, to allow for databases that
do not support bind parameters in the then
clause. The type
can be specified which determines the type of the case()
construct
overall:
case([(orderline.c.qty > 100,
literal_column("'greaterthan100'", String)),
(orderline.c.qty > 10, literal_column("'greaterthan10'",
String))
], else_=literal_column("'lethan10'", String))
sqlalchemy.sql.expression.
cast
(clause, totype, **kwargs)¶Return a CAST
function.
Equivalent of SQL CAST(clause AS totype)
.
Use with a TypeEngine
subclass, i.e:
cast(table.c.unit_price * table.c.qty, Numeric(10,4))
or:
cast(table.c.timestamp, DATE)
sqlalchemy.sql.expression.
column
(text, type_=None)¶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:
from sqlalchemy.sql import table, column
Parameters: |
|
---|
See ColumnClause
for further examples.
sqlalchemy.sql.expression.
collate
(expression, collation)¶Return the clause expression COLLATE collation
.
e.g.:
collate(mycolumn, 'utf8_bin')
produces:
mycolumn COLLATE utf8_bin
sqlalchemy.sql.expression.
desc
(column)¶Return a descending ORDER BY
clause element.
e.g.:
someselect.order_by(desc(table1.mycol))
produces:
ORDER BY mycol DESC
sqlalchemy.sql.expression.
distinct
(expr)¶Return a DISTINCT
clause.
e.g.:
distinct(a)
renders:
DISTINCT a
sqlalchemy.sql.expression.
extract
(field, expr)¶Return the clause extract(field FROM expr)
.
sqlalchemy.sql.expression.
false
()¶Return a False_
object, which compiles to false
, or the
boolean equivalent for the target dialect.
sqlalchemy.sql.expression.
func
= <sqlalchemy.sql.expression._FunctionGenerator object>¶Generate SQL function expressions.
func
is a special object instance which generates SQL
functions based on name-based attributes, e.g.:
>>> print func.count(1)
count(:param_1)
The element is a column-oriented SQL element like any other, and is used in that way:
>>> print select([func.count(table.c.id)])
SELECT count(sometable.id) FROM sometable
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:
>>> print func.current_timestamp()
CURRENT_TIMESTAMP
To call functions which are present in dot-separated packages, specify them in the same manner:
>>> print func.stats.yield_curve(5, 10)
stats.yield_curve(:yield_curve_1, :yield_curve_2)
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:
>>> print func.my_string(u'hi', type_=Unicode) + ' ' + \
... func.my_string(u'there', type_=Unicode)
my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
The object returned by a func
call is usually 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:
print connection.execute(func.current_timestamp()).scalar()
In a few exception cases, the func
accessor
will redirect a name to a built-in expression such as cast()
or extract()
, as these names have well-known meaning
but are not exactly the same as “functions” from a SQLAlchemy
perspective.
New in version 0.8: func
can return non-function expression
constructs for common quasi-functional names like cast()
and extract()
.
Functions which are interpreted as “generic” functions know how to calculate their return type automatically. For a listing of known generic functions, see SQL and Generic Functions.
sqlalchemy.sql.expression.
label
(name, obj)¶Return a Label
object for the
given ColumnElement
.
A label changes the name of an element in the columns clause of a
SELECT
statement, typically via the AS
SQL keyword.
This functionality is more conveniently available via the
label()
method on ColumnElement
.
ColumnElement
.sqlalchemy.sql.expression.
literal
(value, type_=None)¶Return a literal clause, bound to a bind parameter.
Literal clauses are created automatically when non- ClauseElement
objects (such as strings, ints, dates, etc.) are used in a comparison
operation with a ColumnElement
subclass, such as a Column
object.
Use this function to force the
generation of a literal clause, which will be created as a
BindParameter
with a bound value.
Parameters: |
|
---|
sqlalchemy.sql.expression.
literal_column
(text, type_=None)¶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: |
|
---|
sqlalchemy.sql.expression.
not_
(clause)¶Return a negation of the given clause, i.e. NOT(clause)
.
The ~
operator is also overloaded on all
ColumnElement
subclasses to produce the
same result.
sqlalchemy.sql.expression.
nullsfirst
(column)¶Return a NULLS FIRST ORDER BY
clause element.
e.g.:
someselect.order_by(desc(table1.mycol).nullsfirst())
produces:
ORDER BY mycol DESC NULLS FIRST
sqlalchemy.sql.expression.
nullslast
(column)¶Return a NULLS LAST ORDER BY
clause element.
e.g.:
someselect.order_by(desc(table1.mycol).nullslast())
produces:
ORDER BY mycol DESC NULLS LAST
sqlalchemy.sql.expression.
or_
(*clauses)¶Join a list of clauses together using the OR
operator.
The |
operator is also overloaded on all
ColumnElement
subclasses to produce the
same result.
sqlalchemy.sql.expression.
outparam
(key, type_=None)¶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.
sqlalchemy.sql.expression.
over
(func, partition_by=None, order_by=None)¶Produce an OVER clause 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: |
|
---|
This function is also available from the func
construct itself via the FunctionElement.over()
method.
New in version 0.7.
sqlalchemy.sql.expression.
text
(text, bind=None, *args, **kwargs)¶Create a SQL construct that is represented by a literal string.
E.g.:
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.
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:
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 autocommit
option:
t = text("EXEC my_procedural_thing()").\
execution_options(autocommit=True)
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: |
|
---|
sqlalchemy.sql.expression.
true
()¶Return a True_
object, which compiles to true
, or the
boolean equivalent for the target dialect.
sqlalchemy.sql.expression.
tuple_
(*expr)¶Return a SQL tuple.
Main usage is to produce a composite IN construct:
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.
sqlalchemy.sql.expression.
type_coerce
(expr, type_)¶Coerce the given expression into the given type, on the Python side only.
type_coerce()
is roughly similar to cast()
, except no
“CAST” expression is rendered - the given type is only applied towards
expression typing and against received result values.
e.g.:
from sqlalchemy.types import TypeDecorator
import uuid
class AsGuid(TypeDecorator):
impl = String
def process_bind_param(self, value, dialect):
if value is not None:
return str(value)
else:
return None
def process_result_value(self, value, dialect):
if value is not None:
return uuid.UUID(value)
else:
return None
conn.execute(
select([type_coerce(mytable.c.ident, AsGuid)]).\
where(
type_coerce(mytable.c.ident, AsGuid) ==
uuid.uuid3(uuid.NAMESPACE_URL, 'bar')
)
)
sqlalchemy.sql.expression.
BinaryExpression
(left, right, operator, type_=None, negate=None, modifiers=None)¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent an expression that is LEFT <operator> RIGHT
.
A BinaryExpression
is generated automatically
whenever two column expressions are used in a Python binary expresion:
>>> from sqlalchemy.sql import column
>>> column('a') + column('b')
<sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
>>> print column('a') + column('b')
a + b
Inherited-members: | |
---|---|
compare
(other, **kw)¶Compare this BinaryExpression
against the
given BinaryExpression
.
sqlalchemy.sql.expression.
BindParameter
(key, value, type_=None, unique=False, callable_=None, isoutparam=False, required=False, quote=None, _compared_to_operator=None, _compared_to_type=None)¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent a bind parameter.
Public constructor is the bindparam()
function.
__eq__
(other)¶__eq__()
method of ColumnOperators
Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
__init__
(key, value, type_=None, unique=False, callable_=None, isoutparam=False, required=False, quote=None, _compared_to_operator=None, _compared_to_type=None)¶Construct a BindParameter.
Parameters: |
|
---|
__le__
(other)¶__le__()
method of ColumnOperators
Implement the <=
operator.
In a column context, produces the clause a <= b
.
__lt__
(other)¶__lt__()
method of ColumnOperators
Implement the <
operator.
In a column context, produces the clause a < b
.
__ne__
(other)¶__ne__()
method of ColumnOperators
Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
anon_label
¶anon_label
attribute of ColumnElement
provides a constant ‘anonymous label’ for this ColumnElement.
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.
asc
()¶asc()
method of ColumnOperators
Produce a asc()
clause against the
parent object.
between
(cleft, cright)¶between()
method of ColumnOperators
Produce a between()
clause against
the parent object, given the lower and upper range.
collate
(collation)¶collate()
method of ColumnOperators
Produce a collate()
clause against
the parent object, given the collation string.
compare
(other, **kw)¶Compare this BindParameter
to the given
clause.
compile
(bind=None, dialect=None, **kw)¶compile()
method of ClauseElement
Compile this SQL expression.
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: |
|
---|
concat
(other)¶concat()
method of ColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
contains
(other, **kwargs)¶contains()
method of ColumnOperators
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%<other>%'
desc
()¶desc()
method of ColumnOperators
Produce a desc()
clause against the
parent object.
distinct
()¶distinct()
method of ColumnOperators
Produce a distinct()
clause against the
parent object.
effective_value
¶Return the value of this bound parameter,
taking into account if the callable
parameter
was set.
The callable
value will be evaluated
and returned if present, else value
.
endswith
(other, **kwargs)¶endswith()
method of ColumnOperators
Implement the ‘endswith’ operator.
In a column context, produces the clause LIKE '%<other>'
expression
¶expression
attribute of ColumnElement
Return a column expression.
Part of the inspection interface; returns self.
get_children
(**kwargs)¶get_children()
method of ClauseElement
Return immediate child elements of this ClauseElement
.
This is used for visit traversal.
**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).
ilike
(other, escape=None)¶ilike()
method of ColumnOperators
Implement the ilike
operator.
In a column context, produces the clause a ILIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.ilike("%foobar%"))
Parameters: |
|
---|
See also
in_
(other)¶in_()
method of ColumnOperators
Implement the in
operator.
In a column context, produces the clause a IN other
.
“other” may be a tuple/list of column expressions,
or a select()
construct.
is_
(other)¶is_()
method of ColumnOperators
Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
isnot
(other)¶isnot()
method of ColumnOperators
Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
label
(name)¶label()
method of ColumnElement
Produce a column label, i.e. <columnname> AS <name>
.
This is a shortcut to the label()
function.
if ‘name’ is None, an anonymous label name will be generated.
like
(other, escape=None)¶like()
method of ColumnOperators
Implement the like
operator.
In a column context, produces the clause a LIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.like("%foobar%"))
Parameters: |
|
---|
See also
match
(other, **kwargs)¶match()
method of ColumnOperators
Implements the ‘match’ operator.
In a column context, this produces a MATCH clause, i.e.
MATCH '<other>'
. The allowed contents of other
are database backend specific.
notilike
(other, escape=None)¶notilike()
method of ColumnOperators
implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
New in version 0.8.
See also
notin_
(other)¶notin_()
method of ColumnOperators
implement the NOT IN
operator.
This is equivalent to using negation with ColumnOperators.in_()
,
i.e. ~x.in_(y)
.
New in version 0.8.
See also
notlike
(other, escape=None)¶notlike()
method of ColumnOperators
implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
New in version 0.8.
See also
nullsfirst
()¶nullsfirst()
method of ColumnOperators
Produce a nullsfirst()
clause against the
parent object.
nullslast
()¶nullslast()
method of ColumnOperators
Produce a nullslast()
clause against the
parent object.
op
(opstring, precedence=0)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
Parameters: |
|
---|
params
(*optionaldict, **kwargs)¶params()
method of ClauseElement
Return a copy with bindparam()
elements replaced.
Returns a copy of this ClauseElement with bindparam()
elements replaced with values taken from the given dictionary:
>>> clause = column('x') + bindparam('foo')
>>> print clause.compile().params
{'foo':None}
>>> print clause.params({'foo':7}).compile().params
{'foo':7}
self_group
(against=None)¶self_group()
method of ClauseElement
Apply a ‘grouping’ to this 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 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 x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of ClauseElement
just returns self.
shares_lineage
(othercolumn)¶shares_lineage()
method of ColumnElement
Return True if the given ColumnElement
has a common ancestor to this ColumnElement
.
startswith
(other, **kwargs)¶startswith()
method of ColumnOperators
Implement the startwith
operator.
In a column context, produces the clause LIKE '<other>%'
unique_params
(*optionaldict, **kwargs)¶unique_params()
method of ClauseElement
Return a copy with bindparam()
elements replaced.
Same functionality as params()
, except adds unique=True
to affected bind parameters so that multiple statements can be
used.
sqlalchemy.sql.expression.
Case
(whens, value=None, else_=None)¶sqlalchemy.sql.expression.
Cast
(clause, totype, **kwargs)¶sqlalchemy.sql.expression.
ClauseElement
¶Bases: sqlalchemy.sql.visitors.Visitable
Base class for elements of a programmatically constructed SQL expression.
compare
(other, **kw)¶Compare this ClauseElement to the given ClauseElement.
Subclasses should override the default behavior, which is a straight identity comparison.
**kw are arguments consumed by subclass compare() methods and
may be used to modify the criteria for comparison.
(see ColumnElement
)
compile
(bind=None, dialect=None, **kw)¶Compile this SQL expression.
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: |
|
---|
get_children
(**kwargs)¶Return immediate child elements of this ClauseElement
.
This is used for visit traversal.
**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).
params
(*optionaldict, **kwargs)¶Return a copy with bindparam()
elements replaced.
Returns a copy of this ClauseElement with bindparam()
elements replaced with values taken from the given dictionary:
>>> clause = column('x') + bindparam('foo')
>>> print clause.compile().params
{'foo':None}
>>> print clause.params({'foo':7}).compile().params
{'foo':7}
self_group
(against=None)¶Apply a ‘grouping’ to this 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 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 x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of ClauseElement
just returns self.
unique_params
(*optionaldict, **kwargs)¶Return a copy with bindparam()
elements replaced.
Same functionality as params()
, except adds unique=True
to affected bind parameters so that multiple statements can be
used.
sqlalchemy.sql.expression.
ClauseList
(*clauses, **kwargs)¶Bases: sqlalchemy.sql.expression.ClauseElement
Describe a list of clauses, separated by an operator.
By default, is comma-separated, such as a column listing.
compare
(other, **kw)¶Compare this ClauseList
to the given ClauseList
,
including a comparison of all the clause items.
sqlalchemy.sql.expression.
ColumnClause
(text, selectable=None, type_=None, is_literal=False)¶Bases: sqlalchemy.sql.expression.Immutable
, sqlalchemy.sql.expression.ColumnElement
Represents a generic column expression from any textual string.
This includes columns associated with tables, aliases and select
statements, but also any arbitrary text. May or may not be bound
to an underlying Selectable
.
ColumnClause
is constructed by itself typically via
the column()
function. It may be placed directly
into constructs such as select()
constructs:
from sqlalchemy.sql import column, select
c1, c2 = column("c1"), column("c2")
s = select([c1, c2]).where(c1==5)
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:
from sqlalchemy.sql import literal_column, select
s = select([literal_column("5 + 7")])
ColumnClause
can also be used in a table-like
fashion by combining the column()
function
with the table()
function, to produce
a “lightweight” form of table metadata:
from sqlalchemy.sql import table, column
user = table("user",
column("id"),
column("name"),
column("description"),
)
The above construct can be created in an ad-hoc fashion and is
not associated with any schema.MetaData
, unlike it’s
more full fledged schema.Table
counterpart.
Parameters: |
|
---|
__eq__
(other)¶__eq__()
method of ColumnOperators
Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
__le__
(other)¶__le__()
method of ColumnOperators
Implement the <=
operator.
In a column context, produces the clause a <= b
.
__lt__
(other)¶__lt__()
method of ColumnOperators
Implement the <
operator.
In a column context, produces the clause a < b
.
__ne__
(other)¶__ne__()
method of ColumnOperators
Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
anon_label
¶anon_label
attribute of ColumnElement
provides a constant ‘anonymous label’ for this ColumnElement.
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.
asc
()¶asc()
method of ColumnOperators
Produce a asc()
clause against the
parent object.
between
(cleft, cright)¶between()
method of ColumnOperators
Produce a between()
clause against
the parent object, given the lower and upper range.
collate
(collation)¶collate()
method of ColumnOperators
Produce a collate()
clause against
the parent object, given the collation string.
compare
(other, use_proxies=False, equivalents=None, **kw)¶compare()
method of ColumnElement
Compare this ColumnElement to another.
Special arguments understood:
Parameters: |
|
---|
compile
(bind=None, dialect=None, **kw)¶compile()
method of ClauseElement
Compile this SQL expression.
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: |
|
---|
concat
(other)¶concat()
method of ColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
contains
(other, **kwargs)¶contains()
method of ColumnOperators
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%<other>%'
desc
()¶desc()
method of ColumnOperators
Produce a desc()
clause against the
parent object.
distinct
()¶distinct()
method of ColumnOperators
Produce a distinct()
clause against the
parent object.
endswith
(other, **kwargs)¶endswith()
method of ColumnOperators
Implement the ‘endswith’ operator.
In a column context, produces the clause LIKE '%<other>'
expression
¶expression
attribute of ColumnElement
Return a column expression.
Part of the inspection interface; returns self.
get_children
(**kwargs)¶get_children()
method of ClauseElement
Return immediate child elements of this ClauseElement
.
This is used for visit traversal.
**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).
ilike
(other, escape=None)¶ilike()
method of ColumnOperators
Implement the ilike
operator.
In a column context, produces the clause a ILIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.ilike("%foobar%"))
Parameters: |
|
---|
See also
in_
(other)¶in_()
method of ColumnOperators
Implement the in
operator.
In a column context, produces the clause a IN other
.
“other” may be a tuple/list of column expressions,
or a select()
construct.
is_
(other)¶is_()
method of ColumnOperators
Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
isnot
(other)¶isnot()
method of ColumnOperators
Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
label
(name)¶label()
method of ColumnElement
Produce a column label, i.e. <columnname> AS <name>
.
This is a shortcut to the label()
function.
if ‘name’ is None, an anonymous label name will be generated.
like
(other, escape=None)¶like()
method of ColumnOperators
Implement the like
operator.
In a column context, produces the clause a LIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.like("%foobar%"))
Parameters: |
|
---|
See also
match
(other, **kwargs)¶match()
method of ColumnOperators
Implements the ‘match’ operator.
In a column context, this produces a MATCH clause, i.e.
MATCH '<other>'
. The allowed contents of other
are database backend specific.
notilike
(other, escape=None)¶notilike()
method of ColumnOperators
implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
New in version 0.8.
See also
notin_
(other)¶notin_()
method of ColumnOperators
implement the NOT IN
operator.
This is equivalent to using negation with ColumnOperators.in_()
,
i.e. ~x.in_(y)
.
New in version 0.8.
See also
notlike
(other, escape=None)¶notlike()
method of ColumnOperators
implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
New in version 0.8.
See also
nullsfirst
()¶nullsfirst()
method of ColumnOperators
Produce a nullsfirst()
clause against the
parent object.
nullslast
()¶nullslast()
method of ColumnOperators
Produce a nullslast()
clause against the
parent object.
op
(opstring, precedence=0)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
Parameters: |
|
---|
self_group
(against=None)¶self_group()
method of ClauseElement
Apply a ‘grouping’ to this 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 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 x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of ClauseElement
just returns self.
shares_lineage
(othercolumn)¶shares_lineage()
method of ColumnElement
Return True if the given ColumnElement
has a common ancestor to this ColumnElement
.
startswith
(other, **kwargs)¶startswith()
method of ColumnOperators
Implement the startwith
operator.
In a column context, produces the clause LIKE '<other>%'
sqlalchemy.sql.expression.
ColumnCollection
(*cols)¶Bases: sqlalchemy.util._collections.OrderedProperties
An ordered dictionary that stores a list of ColumnElement instances.
Overrides the __eq__()
method to produce SQL clauses between
sets of correlated columns.
add
(column)¶Add a column to this collection.
The key attribute of the column will be used as the hash key for this dictionary.
replace
(column)¶add the given column to this collection, removing unaliased versions of this column as well as existing columns with the same key.
e.g.:
t = Table('sometable', metadata, Column('col1', Integer)) t.columns.replace(Column('col1', Integer, key='columnone'))will remove the original ‘col1’ from the collection, and add the new column under the name ‘columnname’.
Used by schema.Column to override columns during table reflection.
sqlalchemy.sql.expression.
ColumnElement
¶Bases: sqlalchemy.sql.expression.ClauseElement
, sqlalchemy.sql.operators.ColumnOperators
Represent a column-oriented SQL expression suitable for usage in the “columns” clause, WHERE clause etc. of a statement.
While the most familiar kind of ColumnElement
is the
Column
object, ColumnElement
serves as the basis
for any unit that may be present in a SQL expression, including
the expressions themselves, SQL functions, bound parameters,
literal expressions, keywords such as NULL
, etc.
ColumnElement
is the ultimate base class for all such elements.
A ColumnElement
provides the ability to generate new
ColumnElement
objects using Python expressions. This means that Python operators
such as ==
, !=
and <
are overloaded to mimic SQL operations,
and allow the instantiation of further ColumnElement
instances
which are composed from other, more fundamental ColumnElement
objects. For example, two ColumnClause
objects can be added
together with the addition operator +
to produce
a BinaryExpression
.
Both ColumnClause
and BinaryExpression
are subclasses
of ColumnElement
:
>>> from sqlalchemy.sql import column
>>> column('a') + column('b')
<sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
>>> print column('a') + column('b')
a + b
ColumnElement
supports the ability to be a proxy element,
which indicates that the ColumnElement
may be associated with
a Selectable
which was derived from another Selectable
.
An example of a “derived” Selectable
is an Alias
of a
Table
. For the ambitious, an in-depth
discussion of this concept can be found at
Expression Transformations.
__eq__
(other)¶__eq__()
method of ColumnOperators
Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
__init__
¶__init__
attribute of object
x.__init__(...) initializes x; see help(type(x)) for signature
__le__
(other)¶__le__()
method of ColumnOperators
Implement the <=
operator.
In a column context, produces the clause a <= b
.
__lt__
(other)¶__lt__()
method of ColumnOperators
Implement the <
operator.
In a column context, produces the clause a < b
.
__ne__
(other)¶__ne__()
method of ColumnOperators
Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
anon_label
¶provides a constant ‘anonymous label’ for this ColumnElement.
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.
asc
()¶asc()
method of ColumnOperators
Produce a asc()
clause against the
parent object.
base_columns
¶between
(cleft, cright)¶between()
method of ColumnOperators
Produce a between()
clause against
the parent object, given the lower and upper range.
bind
= None¶collate
(collation)¶collate()
method of ColumnOperators
Produce a collate()
clause against
the parent object, given the collation string.
comparator
¶compare
(other, use_proxies=False, equivalents=None, **kw)¶Compare this ColumnElement to another.
Special arguments understood:
Parameters: |
|
---|
compile
(bind=None, dialect=None, **kw)¶compile()
method of ClauseElement
Compile this SQL expression.
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: |
|
---|
concat
(other)¶concat()
method of ColumnOperators
Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
contains
(other, **kwargs)¶contains()
method of ColumnOperators
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%<other>%'
desc
()¶desc()
method of ColumnOperators
Produce a desc()
clause against the
parent object.
distinct
()¶distinct()
method of ColumnOperators
Produce a distinct()
clause against the
parent object.
endswith
(other, **kwargs)¶endswith()
method of ColumnOperators
Implement the ‘endswith’ operator.
In a column context, produces the clause LIKE '%<other>'
expression
¶Return a column expression.
Part of the inspection interface; returns self.
foreign_keys
= []¶get_children
(**kwargs)¶get_children()
method of ClauseElement
Return immediate child elements of this ClauseElement
.
This is used for visit traversal.
**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).
ilike
(other, escape=None)¶ilike()
method of ColumnOperators
Implement the ilike
operator.
In a column context, produces the clause a ILIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.ilike("%foobar%"))
Parameters: |
|
---|
See also
in_
(other)¶in_()
method of ColumnOperators
Implement the in
operator.
In a column context, produces the clause a IN other
.
“other” may be a tuple/list of column expressions,
or a select()
construct.
is_
(other)¶is_()
method of ColumnOperators
Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
is_clause_element
= True¶is_selectable
= False¶isnot
(other)¶isnot()
method of ColumnOperators
Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
label
(name)¶Produce a column label, i.e. <columnname> AS <name>
.
This is a shortcut to the label()
function.
if ‘name’ is None, an anonymous label name will be generated.
like
(other, escape=None)¶like()
method of ColumnOperators
Implement the like
operator.
In a column context, produces the clause a LIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.like("%foobar%"))
Parameters: |
|
---|
See also
match
(other, **kwargs)¶match()
method of ColumnOperators
Implements the ‘match’ operator.
In a column context, this produces a MATCH clause, i.e.
MATCH '<other>'
. The allowed contents of other
are database backend specific.
notilike
(other, escape=None)¶notilike()
method of ColumnOperators
implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
New in version 0.8.
See also
notin_
(other)¶notin_()
method of ColumnOperators
implement the NOT IN
operator.
This is equivalent to using negation with ColumnOperators.in_()
,
i.e. ~x.in_(y)
.
New in version 0.8.
See also
notlike
(other, escape=None)¶notlike()
method of ColumnOperators
implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
New in version 0.8.
See also
nullsfirst
()¶nullsfirst()
method of ColumnOperators
Produce a nullsfirst()
clause against the
parent object.
nullslast
()¶nullslast()
method of ColumnOperators
Produce a nullslast()
clause against the
parent object.
op
(opstring, precedence=0)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
Parameters: |
|
---|
operate
(op, *other, **kwargs)¶params
(*optionaldict, **kwargs)¶params()
method of ClauseElement
Return a copy with bindparam()
elements replaced.
Returns a copy of this ClauseElement with bindparam()
elements replaced with values taken from the given dictionary:
>>> clause = column('x') + bindparam('foo')
>>> print clause.compile().params
{'foo':None}
>>> print clause.params({'foo':7}).compile().params
{'foo':7}
primary_key
= False¶proxy_set
¶quote
= None¶reverse_operate
(op, other, **kwargs)¶self_group
(against=None)¶self_group()
method of ClauseElement
Apply a ‘grouping’ to this 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 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 x OR (y AND z)
- AND takes precedence
over OR.
The base self_group()
method of ClauseElement
just returns self.
shares_lineage
(othercolumn)¶Return True if the given ColumnElement
has a common ancestor to this ColumnElement
.
startswith
(other, **kwargs)¶startswith()
method of ColumnOperators
Implement the startwith
operator.
In a column context, produces the clause LIKE '<other>%'
supports_execution
= False¶timetuple
= None¶type
¶unique_params
(*optionaldict, **kwargs)¶unique_params()
method of ClauseElement
Return a copy with bindparam()
elements replaced.
Same functionality as params()
, except adds unique=True
to affected bind parameters so that multiple statements can be
used.
sqlalchemy.sql.operators.
ColumnOperators
¶Bases: sqlalchemy.sql.operators.Operators
Defines boolean, comparison, and other operators for
ColumnElement
expressions.
By default, all methods call down to
operate()
or 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:
def __eq__(self, other):
return self.operate(operators.eq, other)
Where operators.eq
is essentially:
def eq(a, b):
return a == b
The core column expression unit ColumnElement
overrides Operators.operate()
and others
to return further ColumnElement
constructs,
so that the ==
operation above is replaced by a clause
construct.
See also:
Redefining and Creating New Operators
__add__
(other)¶Implement the +
operator.
In a column context, produces the clause a + b
if the parent object has non-string affinity.
If the parent object has a string affinity,
produces the concatenation operator, a || b
-
see ColumnOperators.concat()
.
__and__
(other)¶Implement the &
operator.
When used with SQL expressions, results in an
AND operation, equivalent to
and_()
, that is:
a & b
is equivalent to:
from sqlalchemy import and_
and_(a, b)
Care should be taken when using &
regarding
operator precedence; the &
operator has the highest precedence.
The operands should be enclosed in parenthesis if they contain
further sub expressions:
(a == 2) & (b == 4)
__delattr__
¶__delattr__
attribute of object
x.__delattr__(‘name’) <==> del x.name
__div__
(other)¶Implement the /
operator.
In a column context, produces the clause a / b
.
__eq__
(other)¶Implement the ==
operator.
In a column context, produces the clause a = b
.
If the target is None
, produces a IS NULL
.
__format__
()¶__format__()
method of object
default object formatter
__ge__
(other)¶Implement the >=
operator.
In a column context, produces the clause a >= b
.
__getattribute__
¶__getattribute__
attribute of object
x.__getattribute__(‘name’) <==> x.name
__getitem__
(index)¶Implement the [] operator.
This can be used by some database-specific types such as Postgresql ARRAY and HSTORE.
__gt__
(other)¶Implement the >
operator.
In a column context, produces the clause a > b
.
__hash__
¶__init__
¶__init__
attribute of object
x.__init__(...) initializes x; see help(type(x)) for signature
__invert__
()¶__invert__()
method of Operators
Implement the ~
operator.
When used with SQL expressions, results in a
NOT operation, equivalent to
not_()
, that is:
~a
is equivalent to:
from sqlalchemy import not_
not_(a)
__le__
(other)¶Implement the <=
operator.
In a column context, produces the clause a <= b
.
__lshift__
(other)¶implement the << operator.
Not used by SQLAlchemy core, this is provided for custom operator systems which want to use << as an extension point.
__lt__
(other)¶Implement the <
operator.
In a column context, produces the clause a < b
.
__mod__
(other)¶Implement the %
operator.
In a column context, produces the clause a % b
.
__mul__
(other)¶Implement the *
operator.
In a column context, produces the clause a * b
.
__ne__
(other)¶Implement the !=
operator.
In a column context, produces the clause a != b
.
If the target is None
, produces a IS NOT NULL
.
__neg__
()¶Implement the -
operator.
In a column context, produces the clause -a
.
__new__
(S, ...) → a new object with type S, a subtype of T¶__new__()
method of object
__or__
(other)¶Implement the |
operator.
When used with SQL expressions, results in an
OR operation, equivalent to
or_()
, that is:
a | b
is equivalent to:
from sqlalchemy import or_
or_(a, b)
Care should be taken when using |
regarding
operator precedence; the |
operator has the highest precedence.
The operands should be enclosed in parenthesis if they contain
further sub expressions:
(a == 2) | (b == 4)
__radd__
(other)¶Implement the +
operator in reverse.
__rdiv__
(other)¶Implement the /
operator in reverse.
__reduce__
()¶__reduce__()
method of object
helper for pickle
__reduce_ex__
()¶__reduce_ex__()
method of object
helper for pickle
__repr__
¶__repr__
attribute of object
__rmul__
(other)¶Implement the *
operator in reverse.
__rshift__
(other)¶implement the >> operator.
Not used by SQLAlchemy core, this is provided for custom operator systems which want to use >> as an extension point.
__rsub__
(other)¶Implement the -
operator in reverse.
__rtruediv__
(other)¶Implement the //
operator in reverse.
__setattr__
¶__setattr__
attribute of object
x.__setattr__(‘name’, value) <==> x.name = value
__sizeof__
() → int¶__sizeof__()
method of object
size of object in memory, in bytes
__str__
¶__str__
attribute of object
__sub__
(other)¶Implement the -
operator.
In a column context, produces the clause a - b
.
__subclasshook__
()¶__subclasshook__()
method of object
Abstract classes can override this to customize issubclass().
This is invoked early on by abc.ABCMeta.__subclasscheck__(). It should return True, False or NotImplemented. If it returns NotImplemented, the normal algorithm is used. Otherwise, it overrides the normal algorithm (and the outcome is cached).
__truediv__
(other)¶Implement the //
operator.
In a column context, produces the clause a / b
.
__weakref__
¶__weakref__
attribute of Operators
list of weak references to the object (if defined)
between
(cleft, cright)¶Produce a between()
clause against
the parent object, given the lower and upper range.
collate
(collation)¶Produce a collate()
clause against
the parent object, given the collation string.
concat
(other)¶Implement the ‘concat’ operator.
In a column context, produces the clause a || b
,
or uses the concat()
operator on MySQL.
contains
(other, **kwargs)¶Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%<other>%'
distinct
()¶Produce a distinct()
clause against the
parent object.
endswith
(other, **kwargs)¶Implement the ‘endswith’ operator.
In a column context, produces the clause LIKE '%<other>'
ilike
(other, escape=None)¶Implement the ilike
operator.
In a column context, produces the clause a ILIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.ilike("%foobar%"))
Parameters: |
|
---|
See also
in_
(other)¶Implement the in
operator.
In a column context, produces the clause a IN other
.
“other” may be a tuple/list of column expressions,
or a select()
construct.
is_
(other)¶Implement the IS
operator.
Normally, IS
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
isnot
(other)¶Implement the IS NOT
operator.
Normally, IS NOT
is generated automatically when comparing to a
value of None
, which resolves to NULL
. However, explicit
usage of IS NOT
may be desirable if comparing to boolean values
on certain platforms.
New in version 0.7.9.
See also
like
(other, escape=None)¶Implement the like
operator.
In a column context, produces the clause a LIKE other
.
E.g.:
select([sometable]).where(sometable.c.column.like("%foobar%"))
Parameters: |
|
---|
See also
match
(other, **kwargs)¶Implements the ‘match’ operator.
In a column context, this produces a MATCH clause, i.e.
MATCH '<other>'
. The allowed contents of other
are database backend specific.
notilike
(other, escape=None)¶implement the NOT ILIKE
operator.
This is equivalent to using negation with
ColumnOperators.ilike()
, i.e. ~x.ilike(y)
.
New in version 0.8.
See also
notin_
(other)¶implement the NOT IN
operator.
This is equivalent to using negation with ColumnOperators.in_()
,
i.e. ~x.in_(y)
.
New in version 0.8.
See also
notlike
(other, escape=None)¶implement the NOT LIKE
operator.
This is equivalent to using negation with
ColumnOperators.like()
, i.e. ~x.like(y)
.
New in version 0.8.
See also
nullsfirst
()¶Produce a nullsfirst()
clause against the
parent object.
nullslast
()¶Produce a nullslast()
clause against the
parent object.
op
(opstring, precedence=0)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
Parameters: |
|
---|
operate
(op, *other, **kwargs)¶Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower()
to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
Parameters: |
|
---|
reverse_operate
(op, other, **kwargs)¶reverse_operate()
method of Operators
Reverse operate on an argument.
Usage is the same as operate()
.
startswith
(other, **kwargs)¶Implement the startwith
operator.
In a column context, produces the clause LIKE '<other>%'
timetuple
= None¶Hack, allows datetime objects to be compared on the LHS.
sqlalchemy.sql.expression.
Extract
(field, expr, **kwargs)¶sqlalchemy.sql.expression.
False_
¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent the false
keyword in a SQL statement.
Public constructor is the false()
function.
sqlalchemy.sql.expression.
Label
(name, element, type_=None)¶Bases: sqlalchemy.sql.expression.ColumnElement
Represents a column label (AS).
Represent a label, as typically applied to any column-level
element using the AS
sql keyword.
This object is constructed from the label()
module level
function as well as the label()
method available on all
ColumnElement
subclasses.
sqlalchemy.sql.expression.
Null
¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent the NULL keyword in a SQL statement.
Public constructor is the null()
function.
sqlalchemy.sql.expression.
Over
(func, partition_by=None, order_by=None)¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent an OVER clause.
This is a special operator against a so-called “window” function, as well as any aggregate function, which produces results relative to the result set itself. It’s supported only by certain database backends.
sqlalchemy.sql.expression.
TextClause
(text='', bind=None, bindparams=None, typemap=None, autocommit=None)¶Bases: sqlalchemy.sql.expression.Executable
, sqlalchemy.sql.expression.ClauseElement
Represent a literal SQL text fragment.
Public constructor is the text()
function.
sqlalchemy.sql.expression.
Tuple
(*clauses, **kw)¶Bases: sqlalchemy.sql.expression.ClauseList
, sqlalchemy.sql.expression.ColumnElement
sqlalchemy.sql.expression.
True_
¶Bases: sqlalchemy.sql.expression.ColumnElement
Represent the true
keyword in a SQL statement.
Public constructor is the true()
function.
sqlalchemy.sql.operators.
custom_op
(opstring, precedence=0)¶Represent a ‘custom’ operator.
custom_op
is normally instantitated when the
ColumnOperators.op()
method is used to create a
custom operator callable. The class can also be used directly
when programmatically constructing expressions. E.g.
to represent the “factorial” operation:
from sqlalchemy.sql import UnaryExpression
from sqlalchemy.sql import operators
from sqlalchemy import Numeric
unary = UnaryExpression(table.c.somecolumn,
modifier=operators.custom_op("!"),
type_=Numeric)
sqlalchemy.sql.operators.
Operators
¶Base of comparison and logical operators.
Implements base methods operate()
and
reverse_operate()
, as well as
__and__()
,
__or__()
,
__invert__()
.
Usually is used via its most common subclass
ColumnOperators
.
__and__
(other)¶Implement the &
operator.
When used with SQL expressions, results in an
AND operation, equivalent to
and_()
, that is:
a & b
is equivalent to:
from sqlalchemy import and_
and_(a, b)
Care should be taken when using &
regarding
operator precedence; the &
operator has the highest precedence.
The operands should be enclosed in parenthesis if they contain
further sub expressions:
(a == 2) & (b == 4)
__invert__
()¶Implement the ~
operator.
When used with SQL expressions, results in a
NOT operation, equivalent to
not_()
, that is:
~a
is equivalent to:
from sqlalchemy import not_
not_(a)
__or__
(other)¶Implement the |
operator.
When used with SQL expressions, results in an
OR operation, equivalent to
or_()
, that is:
a | b
is equivalent to:
from sqlalchemy import or_
or_(a, b)
Care should be taken when using |
regarding
operator precedence; the |
operator has the highest precedence.
The operands should be enclosed in parenthesis if they contain
further sub expressions:
(a == 2) | (b == 4)
__weakref__
¶list of weak references to the object (if defined)
op
(opstring, precedence=0)¶produce a generic operator function.
e.g.:
somecolumn.op("*")(5)
produces:
somecolumn * 5
This function can also be used to make bitwise operators explicit. For example:
somecolumn.op('&')(0xff)
is a bitwise AND of the value in somecolumn
.
Parameters: |
|
---|
operate
(op, *other, **kwargs)¶Operate on an argument.
This is the lowest level of operation, raises
NotImplementedError
by default.
Overriding this on a subclass can allow common
behavior to be applied to all operations.
For example, overriding ColumnOperators
to apply func.lower()
to the left and right
side:
class MyComparator(ColumnOperators):
def operate(self, op, other):
return op(func.lower(self), func.lower(other))
Parameters: |
|
---|
reverse_operate
(op, other, **kwargs)¶Reverse operate on an argument.
Usage is the same as operate()
.
sqlalchemy.sql.expression.
UnaryExpression
(element, operator=None, modifier=None, type_=None, negate=None)¶Bases: sqlalchemy.sql.expression.ColumnElement
Define a ‘unary’ expression.
A unary expression has a single column expression and an operator. The operator can be placed on the left (where it is called the ‘operator’) or right (where it is called the ‘modifier’) of the column expression.
compare
(other, **kw)¶Compare this UnaryExpression
against the given
ClauseElement
.