SQLAlchemy 0.3 Documentation - module sqlalchemy.sql

Module Functions

def alias(*args, **params)

def and_(*clauses)

Join a list of clauses together by the AND operator.

The & operator can be used as well.

def asc(column)

Return an ascending ORDER BY clause element.

E.g.:

order_by = [asc(table1.mycol)]

def between(ctest, cleft, cright)

Return BETWEEN predicate clause.

Equivalent of SQL clausetest BETWEEN clauseleft AND clauseright.

This is better called off a ColumnElement directly, i.e.:

column.between(value1, value2)

def bindparam(key, value=None, type=None, shortname=None, unique=False)

Create a bind parameter clause with the given key.

value: a default value for this bind parameter. a bindparam with a value is called a value-based bindparam.
shortname: an alias for this bind parameter. usually used to alias the key and label of a column, i.e. somecolname and sometable_somecolname
type: a sqlalchemy.types.TypeEngine object indicating the type of this bind param, will invoke type-specific bind parameter processing
unique: if True, bind params sharing the same name will have their underlying key modified to a uniquely generated name. mostly useful with value-based bind params.

def case(whens, value=None, else_=None)

SQL CASE statement.

whens: A sequence of pairs to be translated into "when / then" clauses.
value: Optional for simple case statements.
else_: Optional as well, for case defaults.

def cast(clause, totype, **kwargs)

return CAST function CAST(clause AS totype)

Use with a sqlalchemy.types.TypeEngine object, i.e cast(table.c.unit_price * table.c.qty, Numeric(10,4)) or cast(table.c.timestamp, DATE)

def column(text, table=None, type=None, **kwargs)

Return a textual column clause, relative to a table.

This is also the primitive version of a schema.Column which is a subclass.

def delete(table, whereclause=None, **kwargs)

Return a DELETE clause element.

This can also be called from a table directly via the table's delete() method.

table: The table to be updated.
whereclause: A ClauseElement describing the WHERE condition of the UPDATE statement.

def desc(column)

Return a descending ORDER BY clause element.

E.g.:

order_by = [desc(table1.mycol)]

def except_(*selects, **params)

def except_all(*selects, **params)

def exists(*args, **kwargs)

def extract(field, expr)

return extract(field FROM expr)

def insert(table, values=None, **kwargs)

Return an INSERT clause element.

This can also be called from a table directly via the table's insert() method.

table: The table to be inserted into.
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.

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.

def intersect(*selects, **params)

def intersect_all(*selects, **params)

def join(left, right, onclause=None, **kwargs)

Return a JOIN clause element (regular inner join).

left: The left side of the join.
right: The right side of the join.
onclause: Optional criterion for the ON clause, is derived from foreign key relationships otherwise

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

def literal(value, type=None)

Return a literal clause, bound to a bind parameter.

Literal clauses are created automatically when used as the right-hand side of a boolean or math operation against a column object. Use this function when a literal is needed on the left-hand side (and optionally on the right as well).

The optional type parameter is a sqlalchemy.types.TypeEngine object which indicates bind-parameter and result-set translation for this literal.

def literal_column(text, table=None, type=None, **kwargs)

Return a textual column clause with the literal flag set.

This column will not be quoted.

def not_(clause)

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

The ~ operator can be used as well.

def null()

Return a _Null object, which compiles to NULL in a sql statement.

def or_(*clauses)

Join a list of clauses together by the OR operator.

The | operator can be used as well.

def outerjoin(left, right, onclause=None, **kwargs)

Return an OUTER JOIN clause element.

left: The left side of the join.
right: The right side of the join.
onclause: Optional criterion for the ON clause, is derived from foreign key relationships otherwise.

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

def select(columns=None, whereclause=None, from_obj=[], **kwargs)

Returns a SELECT clause element.

This can also be called via the table's select() method.

columns: A list of columns and/or selectable items to select columns from whereclause is a text or ClauseElement expression which will form the WHERE clause.
from_obj: A list of additional FROM objects, such as Join objects, which will extend or override the default FROM objects created from the column list and the whereclause.
**kwargs: Additional parameters for the Select object.

def subquery(alias, *args, **kwargs)

def table(name, *columns)

Return a table clause.

This is a primitive version of the schema.Table object, which is a subclass of this object.

def text(text, engine=None, *args, **kwargs)

Create literal text to be inserted into a query.

When constructing a query from a select(), update(), insert() or delete(), using plain strings for argument values will usually result in text objects being created automatically. Use this function when creating textual clauses outside of other ClauseElement objects, or optionally wherever plain text is to be used.

Arguments include:

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.
engine: An optional 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 SELECT clause of the textual statement to type objects, which will be used to perform post-processing on columns within the result set (for textual statements that produce result sets).

def union(*selects, **params)

def union_all(*selects, **params)

def update(table, whereclause=None, values=None, **kwargs)

Return an UPDATE clause element.

This can also be called from a table directly via the table's update() method.

table: The table to be updated.
whereclause: A ClauseElement describing the WHERE condition of the UPDATE statement.
values: A dictionary which specifies the SET conditions of the UPDATE, and is optional. If left as None, the SET conditions are determined from the bind parameters used during the compile phase of the UPDATE statement. If the bind parameters also are None during the compile phase, then the SET conditions will be generated from the full list of table columns.

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 UPDATE statement's table, the statement will be correlated against the UPDATE statement.

back to section top

class AbstractDialect(object)

Represent the behavior of a particular database.

Used by Compiled objects.

back to section top

class Alias(FromClause)

def __init__(self, selectable, alias=None)

def accept_visitor(self, visitor)

engine = property()

def get_children(self, **kwargs)

def named_with_column(self)

def supports_execution(self)

back to section top

class ClauseElement(object)

Base class for elements of a programmatically constructed SQL expression.

def accept_visitor(self, visitor)

Accept a ClauseVisitor and call the appropriate visit_xxx method.

def compare(self, other)

Compare this ClauseElement to the given ClauseElement.

Subclasses should override the default behavior, which is a straight identity comparison.

def compile(self, engine=None, parameters=None, compiler=None, dialect=None)

Compile this SQL expression.

Uses the given Compiler, or the given AbstractDialect or Engine to create a Compiler. If no compiler arguments are given, tries to use the underlying Engine this ClauseElement is bound to to create a Compiler, if any.

Finally, if there is no bound Engine, uses an ANSIDialect to create a default Compiler.

parameters is a dictionary representing the default bind parameters to be used with the statement. If parameters is a list, it is assumed to be a list of dictionaries and the first dictionary in the list is used with which to compile against.

The bind parameters can in some cases determine the output of the compilation, such as for UPDATE and INSERT statements the bind parameters that are present determine the SET and VALUES clause of those statements.

def copy_container(self)

Return a copy of this ClauseElement, if this ClauseElement contains other ClauseElements.

If this ClauseElement is not a container, it should return self. This is used to create copies of expression trees that still reference the same leaf nodes. The new structure can then be restructured without affecting the original.

engine = property()

Attempts to locate a Engine within this ClauseElement structure, or returns None if none found.

def execute(self, *multiparams, **params)

Compile and execute this ClauseElement.

def get_children(self, **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).

def scalar(self, *multiparams, **params)

Compile and execute this ClauseElement, returning the result's scalar representation.

def supports_execution(self)

Return True if this clause element represents a complete executable statement.

back to section top

class ClauseParameters(dict)

Represent a dictionary/iterator of bind parameter key names/values.

Tracks the original BindParam objects as well as the keys/position of each parameter, and can return parameters as a dictionary or a list. Will process parameter values according to the TypeEngine objects present in the BindParams.

def __init__(self, dialect, positional=None)

def get_original(self, key)

Return the given parameter as it was originally placed in this ClauseParameters object, without any Type conversion.

def get_original_dict(self)

def get_raw_dict(self)

def get_raw_list(self)

def set_parameter(self, bindparam, value)

back to section top

class ClauseVisitor(object)

A class that knows how to traverse and visit ClauseElements.

Each ClauseElement's accept_visitor() method will call a corresponding visit_XXXX() method here. Traversal of a hierarchy of ClauseElements is achieved via the traverse() method, which is passed the lead ClauseElement.

By default, ClauseVisitor traverses all elements fully. Options can be specified at the class level via the __traverse_options__ dictionary which will be passed to the get_children() method of each ClauseElement; these options can indicate modifications to the set of elements returned, such as to not return column collections (column_collections=False) or to return Schema-level items (schema_visitor=True).

def traverse(self, obj)

def visit_alias(self, alias)

def visit_binary(self, binary)

def visit_bindparam(self, bindparam)

def visit_calculatedclause(self, calcclause)

def visit_cast(self, cast)

def visit_clauselist(self, list)

def visit_column(self, column)

def visit_compound(self, compound)

def visit_compound_select(self, compound)

def visit_fromclause(self, fromclause)

def visit_function(self, func)

def visit_join(self, join)

def visit_label(self, label)

def visit_null(self, null)

def visit_select(self, select)

def visit_table(self, table)

def visit_textclause(self, textclause)

def visit_typeclause(self, typeclause)

back to section top

class ColumnCollection(OrderedProperties)

An ordered dictionary that stores a list of ColumnElement instances.

Overrides the __eq__() method to produce SQL clauses between sets of correlated columns.

def __init__(self, *cols)

def add(self, column)

Add a column to this collection.

The key attribute of the column will be used as the hash key for this dictionary.

def contains_column(self, col)

back to section top

class ColumnElement(Selectable,_CompareMixin)

Represent a column element within the list of a Selectable's columns.

A ColumnElement can either be directly associated with a TableClause, or a free-standing textual column with no table, or is a proxy column, indicating it is placed on a Selectable such as an Alias or Select statement and ultimately corresponds to a TableClause-attached column (or in the case of a CompositeSelect, a proxy ColumnElement may correspond to several TableClause-attached columns).

columns = property()

Columns accessor which just returns self, to provide compatibility with Selectable objects.

foreign_key = property()

foreign_keys = property()

Foreign key accessor. Points to a list of ForeignKey objects which represents a Foreign Key placed on this column's ultimate ancestor.

orig_set = property()

A Set containing TableClause-bound, non-proxied ColumnElements for which this ColumnElement is a proxy. In all cases except for a column proxied from a Union (i.e. CompoundSelect), this set will be just one element.

primary_key = property()

Primary key flag. Indicates if this Column represents part or whole of a primary key.

def shares_lineage(self, othercolumn)

Return True if the given ColumnElement has a common ancestor to this ColumnElement.

back to section top

class Compiled(ClauseVisitor)

Represent a compiled SQL expression.

The __str__ method of the Compiled object should produce the actual text of the statement. Compiled objects are specific to their underlying database dialect, and also may or may not be specific to the columns referenced within a particular set of bind parameters. In no case should the Compiled object be dependent on the actual values of those bind parameters, even though it may reference those values as defaults.

def __init__(self, dialect, statement, parameters, engine=None, traversal=None)

Construct a new Compiled object.

statement: ClauseElement to be compiled.
parameters: Optional dictionary indicating a set of bind parameters specified with this Compiled object. These parameters are the default values corresponding to the ClauseElement's _BindParamClauses when the Compiled is executed. In the case of an INSERT or UPDATE statement, these parameters will also result in the creation of new _BindParamClause objects for each key and will also affect the generated column list in an INSERT statement and the SET clauses of an UPDATE statement. The keys of the parameter dictionary can either be the string names of columns or _ColumnClause objects.
engine: Optional Engine to compile this statement against.

def compile(self)

def execute(self, *multiparams, **params)

Execute this compiled object.

def get_params(self, **params)

Return the bind params for this compiled object.

Will start with the default parameters specified when this Compiled object was first constructed, and will override those values with those sent via **params, which are key/value pairs. Each key should match one of the _BindParamClause objects compiled into this object; either the key or shortname property of the _BindParamClause.

def scalar(self, *multiparams, **params)

Execute this compiled object and return the result's scalar value.

back to section top

class CompoundSelect(_SelectBaseMixin,FromClause)

def __init__(self, keyword, *selects, **kwargs)

def accept_visitor(self, visitor)

def get_children(self, column_collections=True, **kwargs)

name = property()

back to section top

class Executor(object)

Interface representing a "thing that can produce Compiled objects and execute them".

def compiler(self, statement, parameters, **kwargs)

Return a Compiled object for the given statement and parameters.

def execute_compiled(self, compiled, parameters, echo=None, **kwargs)

Execute a Compiled object.

back to section top

class FromClause(Selectable)

Represent an element that can be used within the FROM clause of a SELECT statement.

def __init__(self, name=None)

def accept_visitor(self, visitor)

def alias(self, name=None)

c = property()

columns = property()

def corresponding_column(self, column, raiseerr=True, keys_ok=False, require_embedded=False)

Given a ColumnElement, return the exported ColumnElement object from this Selectable which corresponds to that original Column via a common anscestor column.

column: the target ColumnElement to be matched
raiseerr: if True, raise an error if the given ColumnElement could not be matched. if False, non-matches will return None.
keys_ok: if the ColumnElement cannot be matched, attempt to match based on the string "key" property of the column alone. This makes the search much more liberal.
require_embedded: only return corresponding columns for 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 anscestor with one of the exported columns of this FromClause.

def count(self, whereclause=None, **params)

def default_order_by(self)

foreign_keys = property()

def join(self, right, *args, **kwargs)

def named_with_column(self)

True if the name of this FromClause may be prepended to a column in a generated SQL statement.

oid_column = property()

original_columns = property()

A dictionary mapping an original Table-bound column to a proxied column in this FromClause.

def outerjoin(self, right, *args, **kwargs)

primary_key = property()

back to section top

class Join(FromClause)

def __init__(self, left, right, onclause=None, isouter=False)

def accept_visitor(self, visitor)

def alias(self, name=None)

Create a Select out of this Join clause and return an Alias of it.

The Select is not correlating.

engine = property()

def get_children(self, **kwargs)

name = property()

def select(self, whereclause=None, fold_equivalents=False, **kwargs)

Create a Select from this Join.

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 equivalent columns in the column list of the resulting select. 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

back to section top

class Select(_SelectBaseMixin,FromClause)

Represent a SELECT statement, with appendable clauses, as well as the ability to execute itself and return a result set.

def __init__(self, columns=None, whereclause=None, from_obj=[], order_by=None, group_by=None, having=None, use_labels=False, distinct=False, for_update=False, engine=None, limit=None, offset=None, scalar=False, correlate=True)

def accept_visitor(self, visitor)

def append_column(self, column)

def append_from(self, fromclause)

def append_having(self, having)

def append_whereclause(self, whereclause)

def correlate(self, from_obj)

Given a FROM object, correlate this SELECT statement to it.

This basically means the given from object will not come out in this select statement's FROM clause when printed.

froms = property()

A collection containing all elements of the FROM clause

def get_children(self, column_collections=True, **kwargs)

def label(self, name)

def union(self, other, **kwargs)

def union_all(self, other, **kwargs)

back to section top

class Selectable(ClauseElement)

Represent a column list-holding object.

def accept_visitor(self, visitor)

def select(self, whereclauses=None, **params)

back to section top

class TableClause(FromClause)

def __init__(self, name, *columns)

def accept_visitor(self, visitor)

def alias(self, name=None)

def append_column(self, c)

def count(self, whereclause=None, **params)

def delete(self, whereclause=None)

def get_children(self, column_collections=True, **kwargs)

def insert(self, values=None)

def join(self, right, *args, **kwargs)

def named_with_column(self)

original_columns = property()

def outerjoin(self, right, *args, **kwargs)

def select(self, whereclause=None, **params)

def update(self, whereclause=None, values=None)