SQLAlchemy 0.3 Documentation

Version: 0.3.5 Last Updated: 03/18/07 18:39:07

module sqlalchemy.orm

The mapper package provides object-relational functionality, building upon the schema and sql packages and tying operations to class properties and constructors.

Module Functions

def backref(name, **kwargs)

Create a BackRef object with explicit arguments, which are the same arguments one can send to relation().

Used with the backref keyword argument to relation() in place of a string argument.

def cascade_mappers(*classes_or_mappers)

Attempt to create a series of relations() between mappers automatically, via introspecting the foreign key relationships of the underlying tables.

Given a list of classes and/or mappers, identify the foreign key relationships between the given mappers or corresponding class mappers, and create relation() objects representing those relationships, including a backreference. Attempt to find the secondary table in a many-to-many relationship as well.

The names of the relations will be a lowercase version of the related class. In the case of one-to-many or many-to-many, the name will be pluralized, which currently is based on the English language (i.e. an 's' or 'es' added to it).

NOTE: this method usually works poorly, and its usage is generally not advised.

def class_mapper(class_, entity_name=None, compile=True)

Given a class and optional entity_name, return the primary Mapper associated with the key.

If no mapper can be located, raises InvalidRequestError.

def clear_mapper(m)

Remove the given mapper from the storage of mappers.

When a new mapper is created for the previous mapper's class, it will be used as that classes' new primary mapper.

def clear_mappers()

Remove all mappers that have been created thus far.

When new mappers are created, they will be assigned to their classes as their primary mapper.

def compile_mappers()

Compile all mappers that have been defined.

This is equivalent to calling compile() on any individual mapper.

def contains_alias(alias)

Return a MapperOption that will indicate to the query that the main table has been aliased.

alias is the string name or Alias object representing the alias.

def contains_eager(key, alias=None, decorator=None)

Return a MapperOption that will indicate to the query that the given attribute will be eagerly loaded.

Used when feeding SQL result sets directly into query.instances(). Also bundles an EagerLazyOption to turn on eager loading in case it isnt already.

alias is the string name of an alias, or an sql.Alias object, which represents the aliased columns in the query. This argument is optional.

decorator is mutually exclusive of alias and is a row-processing function which will be applied to the incoming row before sending to the eager load handler. use this for more sophisticated row adjustments beyond a straight alias.

def defer(name)

Return a MapperOption that will convert the column property of the given name into a deferred load.

Used with query.options()

def deferred(*columns, **kwargs)

Return a DeferredColumnProperty, which indicates this object attributes should only be loaded from its corresponding table column when first accessed.

Used with the properties dictionary sent to mapper().

def eagerload(name)

Return a MapperOption that will convert the property of the given name into an eager load.

Used with query.options().

def extension(ext)

Return a MapperOption that will insert the given MapperExtension to the beginning of the list of extensions that will be called in the context of the Query.

Used with query.options().

def lazyload(name)

Return a MapperOption that will convert the property of the given name into a lazy load.

Used with query.options().

def mapper(class_, table=None, *args, **params)

Return a new Mapper object.

See the Mapper class for a description of arguments.

def noload(name)

Return a MapperOption that will convert the property of the given name into a non-load.

Used with query.options().

def object_mapper(object, raiseerror=True)

Given an object, return the primary Mapper associated with the object instance.

object
The object instance.
raiseerror
Defaults to True: raise an InvalidRequestError if no mapper can be located. If False, return None.
def object_session(obj)

Return the Session to which the given object is bound, or None if none.

def polymorphic_union(table_map, typecolname, aliasname='p_union')

create a UNION statement used by a polymorphic mapper.

See the SQLAlchemy advanced mapping docs for an example of how this is used.

def relation(*args, **kwargs)

Provide a relationship of a primary Mapper to a secondary Mapper.

This corresponds to a parent-child or associative table relationship.

def synonym(name, proxy=False)

Set up name as a synonym to another MapperProperty.

Used with the properties dictionary sent to mapper().

def undefer(name)

Return a MapperOption that will convert the column property of the given name into a non-deferred (regular column) load.

Used with query.options().

back to section top

class MapperExtension(object)

Base implementation for an object that provides overriding behavior to various Mapper functions. For each method in MapperExtension, a result of EXT_PASS indicates the functionality is not overridden.

def after_delete(self, mapper, connection, instance)

Receive an object instance after that instance is DELETEed.

def after_insert(self, mapper, connection, instance)

Receive an object instance after that instance is INSERTed.

def after_update(self, mapper, connection, instance)

Receive an object instance after that instance is UPDATEed.

def append_result(self, mapper, selectcontext, row, instance, identitykey, result, isnew)

Receive an object instance before that instance is appended to a result list.

If this method returns EXT_PASS, result appending will proceed normally. if this method returns any other value or None, result appending will not proceed for this instance, giving this extension an opportunity to do the appending itself, if desired.

mapper
The mapper doing the operation.
selectcontext
SelectionContext corresponding to the instances() call.
row
The result row from the database.
instance
The object instance to be appended to the result.
identitykey
The identity key of the instance.
result
List to which results are being appended.
isnew
Indicates if this is the first time we have seen this object instance in the current result set. if you are selecting from a join, such as an eager load, you might see the same object instance many times in the same result set.
def before_delete(self, mapper, connection, instance)

Receive an object instance before that instance is DELETEed.

def before_insert(self, mapper, connection, instance)

Receive an object instance before that instance is INSERTed into its table.

This is a good place to set up primary key values and such that aren't handled otherwise.

def before_update(self, mapper, connection, instance)

Receive an object instance before that instance is UPDATEed.

def create_instance(self, mapper, selectcontext, row, class_)

Receive a row when a new object instance is about to be created from that row.

The method can choose to create the instance itself, or it can return None to indicate normal object creation should take place.

mapper
The mapper doing the operation
selectcontext
SelectionContext corresponding to the instances() call
row
The result row from the database
class_
The class we are mapping.
def get(self, query, *args, **kwargs)

Override the get method of the Query object.

The return value of this method is used as the result of query.get() if the value is anything other than EXT_PASS.

def get_by(self, query, *args, **kwargs)

Override the get_by method of the Query object.

The return value of this method is used as the result of query.get_by() if the value is anything other than EXT_PASS.

def get_session(self)

Retrieve a contextual Session instance with which to register a new object.

Note: this is not called if a session is provided with the __init__ params (i.e. _sa_session).

def load(self, query, *args, **kwargs)

Override the load method of the Query object.

The return value of this method is used as the result of query.load() if the value is anything other than EXT_PASS.

def populate_instance(self, mapper, selectcontext, row, instance, identitykey, isnew)

Receive a newly-created instance before that instance has its attributes populated.

The normal population of attributes is according to each attribute's corresponding MapperProperty (which includes column-based attributes as well as relationships to other classes). If this method returns EXT_PASS, instance population will proceed normally. If any other value or None is returned, instance population will not proceed, giving this extension an opportunity to populate the instance itself, if desired.

def select(self, query, *args, **kwargs)

Override the select method of the Query object.

The return value of this method is used as the result of query.select() if the value is anything other than EXT_PASS.

def select_by(self, query, *args, **kwargs)

Override the select_by method of the Query object.

The return value of this method is used as the result of query.select_by() if the value is anything other than EXT_PASS.

def translate_row(self, mapper, context, row)

Perform pre-processing on the given result row and return a new row instance.

This is called as the very first step in the _instance() method.

back to section top

class Query(object)

Encapsulates the object-fetching operations provided by Mappers.

def __init__(self, class_or_mapper, session=None, entity_name=None, lockmode=None, with_options=None, extension=None, **kwargs)
def add_column(self, column)
def add_entity(self, entity)
def avg(self, col)

Execute the SQL avg() function against the given column.

def compile(self, whereclause=None, **kwargs)

Given a WHERE criterion, produce a ClauseElement-based statement suitable for usage in the execute() method.

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

Given a WHERE criterion, create a SELECT COUNT statement, execute and return the resulting count value.

def count_by(self, *args, **params)

Return the count of instances based on the given clauses and key/value criterion.

The criterion is constructed in the same way as the select_by() method.

def distinct(self)

Apply a DISTINCT to the query.

def execute(self, clauseelement, params=None, *args, **kwargs)

Execute the given ClauseElement-based statement against this Query's session/mapper, return the resulting list of instances.

After execution, close the ResultProxy and its underlying resources. This method is one step above the instances() method, which takes the executed statement's ResultProxy directly.

def filter(self, criterion)

apply the given filtering criterion to the query and return the newly resulting Query

the criterion is any sql.ClauseElement applicable to the WHERE clause of a select.

def filter_by(self, *args, **kwargs)

apply the given filtering criterion to the query and return the newly resulting Query

The criterion is constructed in the same way as the select_by() method.

def get(self, ident, **kwargs)

Return an instance of the object based on the given identifier, or None if not found.

The ident argument is a scalar or tuple of primary key column values in the order of the table def's primary key columns.

def get_by(self, *args, **params)

Return a single object instance based on the given key/value criterion.

The criterion is constructed in the same way as the select_by() method.

def group_by(self, criterion)

apply one or more GROUP BY criterion to the query and return the newly resulting Query

def instances(self, cursor, *mappers_or_columns, **kwargs)

Return a list of mapped instances corresponding to the rows in a given cursor (i.e. ResultProxy).

*mappers_or_columns is an optional list containing one or more of classes, mappers, strings or sql.ColumnElements which will be applied to each row and added horizontally to the result set, which becomes a list of tuples. The first element in each tuple is the usual result based on the mapper represented by this Query. Each additional element in the tuple corresponds to an entry in the *mappers_or_columns list.

For each element in *mappers_or_columns, if the element is a mapper or mapped class, an additional class instance will be present in the tuple. If the element is a string or sql.ColumnElement, the corresponding result column from each row will be present in the tuple.

Note that when *mappers_or_columns is present, "uniquing" for the result set is disabled, so that the resulting tuples contain entities as they actually correspond. this indicates that multiple results may be present if this option is used.

def join(self, prop)

create a join of this Query object's criterion to a relationship and return the newly resulting Query.

'prop' may be a string property name in which it is located in the same manner as keyword arguments in select_by, or it may be a list of strings in which case the property is located by direct traversal of each keyname (i.e. like join_via()).

def join_by(self, *args, **params)

Return a ClauseElement representing the WHERE clause that would normally be sent to select_whereclause() by select_by().

The criterion is constructed in the same way as the select_by() method.

def join_to(self, key)

Given the key name of a property, will recursively descend through all child properties from this Query's mapper to locate the property, and will return a ClauseElement representing a join from this Query's mapper to the endmost mapper.

def join_via(self, keys)

Given a list of keys that represents a path from this Query's mapper to a related mapper based on names of relations from one mapper to the next, return a ClauseElement representing a join from this Query's mapper to the endmost mapper.

def limit(self, limit)

Apply a LIMIT to the query.

def list(self)

Return the results represented by this Query as a list.

This results in an execution of the underlying query.

def load(self, ident, **kwargs)

Return an instance of the object based on the given identifier.

If not found, raises an exception. The method will remove all pending changes to the object already existing in the Session. The ident argument is a scalar or tuple of primary key column values in the order of the table def's primary key columns.

def max(self, col)

Execute the SQL max() function against the given column.

def min(self, col)

Execute the SQL min() function against the given column.

def offset(self, offset)

Apply an OFFSET to the query.

def options(self, *args, **kwargs)

Return a new Query object, applying the given list of MapperOptions.

def order_by(self, criterion)

apply one or more ORDER BY criterion to the query and return the newly resulting Query

def outerjoin(self, prop)

create a left outer join of this Query object's criterion to a relationship and return the newly resulting Query.

'prop' may be a string property name in which it is located in the same manner as keyword arguments in select_by, or it may be a list of strings in which case the property is located by direct traversal of each keyname (i.e. like join_via()).

primary_key_columns = property()
def select(self, arg=None, **kwargs)

Select instances of the object from the database.

arg can be any ClauseElement, which will form the criterion with which to load the objects.

For more advanced usage, arg can also be a Select statement object, which will be executed and its resulting rowset used to build new object instances.

In this case, the developer must ensure that an adequate set of columns exists in the rowset with which to build new object instances.

def select_by(self, *args, **params)

Return an array of object instances based on the given clauses and key/value criterion.

*args
a list of zero or more ClauseElements which will be connected by AND operators.
**params

a set of zero or more key/value parameters which are converted into ClauseElements. the keys are mapped to property or column names mapped by this mapper's Table, and the values are coerced into a WHERE clause separated by AND operators. If the local property/column names dont contain the key, a search will be performed against this mapper's immediate list of relations as well, forming the appropriate join conditions if a matching property is located.

if the located property is a column-based property, the comparison value should be a scalar with an appropriate type. If the property is a relationship-bound property, the comparison value should be an instance of the related class.

E.g.:

result = usermapper.select_by(user_name = 'fred')
def select_from(self, from_obj)

Set the from_obj parameter of the query.

from_obj is a list of one or more tables.

def select_statement(self, statement, **params)

Given a ClauseElement-based statement, execute and return the resulting instances.

def select_text(self, text, **params)

Given a literal string-based statement, execute and return the resulting instances.

def select_whereclause(self, whereclause=None, params=None, **kwargs)

Given a WHERE criterion, create a SELECT statement, execute and return the resulting instances.

def selectfirst(self, arg=None, **kwargs)

Like select(), but only return the first result by itself, or None if no objects returned.

def selectfirst_by(self, *args, **params)

Like select_by(), but only return the first result by itself, or None if no objects returned. Synonymous with get_by().

The criterion is constructed in the same way as the select_by() method.

def selectone(self, arg=None, **kwargs)

Like selectfirst(), but throw an error if not exactly one result was returned.

def selectone_by(self, *args, **params)

Like selectfirst_by(), but throws an error if not exactly one result was returned.

The criterion is constructed in the same way as the select_by() method.

session = property()
def sum(self, col)

Execute the SQL sum() function against the given column.

table = property()
def with_lockmode(self, mode)

Return a new Query object with the specified locking mode.

back to section top

class Session(object)

Encapsulates a set of objects being operated upon within an object-relational operation.

The Session object is not threadsafe. For thread-management of Sessions, see the sqlalchemy.ext.sessioncontext module.

def __init__(self, bind_to=None, hash_key=None, import_session=None, echo_uow=False, weak_identity_map=False)
def bind_mapper(self, mapper, bindto)

Bind the given Mapper to the given Engine or Connection.

All subsequent operations involving this Mapper will use the given bindto.

def bind_table(self, table, bindto)

Bind the given Table to the given Engine or Connection.

All subsequent operations involving this Table will use the given bindto.

def clear(self)

Remove all object instances from this Session.

This is equivalent to calling expunge() for all objects in this Session.

def close(self)

Close this Session.

def connect(self, mapper=None, **kwargs)

Return a unique connection corresponding to the given mapper.

This connection will not be part of any pre-existing transactional context.

def connection(self, mapper, **kwargs)

Return a Connection corresponding to the given mapper.

Used by the execute() method which performs select operations for Mapper and Query.

If this Session is transactional, the connection will be in the context of this session's transaction. Otherwise, the connection is returned by the contextual_connect method, which some Engines override to return a thread-local connection, and will have close_with_result set to True.

The given **kwargs will be sent to the engine's contextual_connect() method, if no transaction is in progress.

def create_transaction(self, **kwargs)

Return a new SessionTransaction corresponding to an existing or new transaction.

If the transaction is new, the returned SessionTransaction will have commit control over the underlying transaction, else will have rollback control only.

def delete(self, object, entity_name=None)

Mark the given instance as deleted.

The delete operation occurs upon flush().

deleted = property()

A Set of all objects marked as 'deleted' within this Session

dirty = property()

A Set of all objects marked as 'dirty' within this Session

echo_uow = property()
def execute(self, mapper, clause, params, **kwargs)

Using the given mapper to identify the appropriate Engine or Connection to be used for statement execution, execute the given ClauseElement using the provided parameter dictionary.

Return a ResultProxy corresponding to the execution's results.

If this method allocates a new Connection for the operation, then the ResultProxy's close() method will release the resources of the underlying Connection, otherwise its a no-op.

def expire(self, obj)

mark the given object as expired.

this will add an instrumentation to all mapped attributes on the instance such that when an attribute is next accessed, the session will reload all attributes on the instance from the database.

def expunge(self, object)

Remove the given object from this Session.

This will free all internal references to the object. Cascading will be applied according to the expunge cascade rule.

def flush(self, objects=None)

flush all the object modifications present in this session to the database.

'objects' is a list or tuple of objects specifically to be flushed; if None, all new and modified objects are flushed.

def get(self, class_, ident, **kwargs)

return an instance of the object based on the given identifier, or None if not found.

The ident argument is a scalar or tuple of primary key column values in the order of the table def's primary key columns.

the entity_name keyword argument may also be specified which further qualifies the underlying Mapper used to perform the query.

def get_bind(self, mapper)

Return the Engine or Connection which is used to execute statements on behalf of the given Mapper.

Calling connect() on the return result will always result in a Connection object. This method disregards any SessionTransaction that may be in progress.

The order of searching is as follows:

  1. if an Engine or Connection was bound to this Mapper specifically within this Session, return that Engine or Connection.
  2. if an Engine or Connection was bound to this Mapper's underlying Table within this Session (i.e. not to the Table directly), return that Engine or Conneciton.
  3. if an Engine or Connection was bound to this Session, return that Engine or Connection.
  4. finally, return the Engine which was bound directly to the Table's MetaData object.

If no Engine is bound to the Table, an exception is raised.

def has_key(self, key)
def identity_key(self, *args, **kwargs)

Get an identity key.

Valid call signatures:

identity_key(class_, ident, entity_name=None)
class_
mapped class
ident
primary key, if the key is composite this is a tuple
entity_name
optional entity name. May be given as a positional arg or as a keyword arg.
identity_key(instance=instance)
instance
object instance (must be given as a keyword arg)
identity_key(row=row, class=class_, entity_name=None)
row
result proxy row (must be given as a keyword arg)
identity_map = property()

A dictionary consisting of all objects within this Session keyed to their _instance_key value.

def import_instance(self, *args, **kwargs)

Deprecated. A synynom for merge().

def is_expired(self, obj, unexpire=False)

Return True if the given object has been marked as expired.

def load(self, class_, ident, **kwargs)

return an instance of the object based on the given identifier.

If not found, raises an exception. The method will remove all pending changes to the object already existing in the Session. The ident argument is a scalar or tuple of primary key columns in the order of the table def's primary key columns.

the entity_name keyword argument may also be specified which further qualifies the underlying Mapper used to perform the query.

def mapper(self, class_, entity_name=None)

Given an Class, return the primary Mapper responsible for persisting it.

def merge(self, object, entity_name=None, _recursive=None)

Copy the state of the given object onto the persistent object with the same identifier.

If there is no persistent instance currently associated with the session, it will be loaded. Return the persistent instance. If the given instance is unsaved, save a copy of and return it as a newly persistent instance. The given instance does not become associated with the session.

This operation cascades to associated instances if the association is mapped with cascade="merge".

new = property()

A Set of all objects marked as 'new' within this Session.

def query(self, mapper_or_class, entity_name=None, **kwargs)

return a new Query object corresponding to this Session and the mapper, or the classes' primary mapper.

def refresh(self, obj)

reload the attributes for the given object from the database, clear any changes made.

def save(self, object, entity_name=None)

Add a transient (unsaved) instance to this Session.

This operation cascades the save_or_update method to associated instances if the relation is mapped with cascade="save-update".

The entity_name keyword argument will further qualify the specific Mapper used to handle this instance.

def save_or_update(self, object, entity_name=None)

Save or update the given object into this Session.

The presence of an _instance_key attribute on the instance determines whether to save() or update() the instance.

def scalar(self, mapper, clause, params, **kwargs)

Like execute() but return a scalar result.

sql = property()
def update(self, object, entity_name=None)

Bring the given detached (saved) instance into this Session.

If there is a persistent instance with the same identifier already associated with this Session, an exception is thrown.

This operation cascades the save_or_update method to associated instances if the relation is mapped with cascade="save-update".

back to section top