Release: 1.4.0b1 pre release | Release Date: unreleased

SQLAlchemy 1.4 Documentation

Visitor and Traversal Utilities

The sqlalchemy.sql.visitors module consists of classes and functions that serve the purpose of generically traversing a Core SQL expression structure. This is not unlike the Python ast module in that is presents a system by which a program can operate upon each component of a SQL expression. Common purposes this serves are locating various kinds of elements such as Table or BindParameter objects, as well as altering the state of the structure such as replacing certain FROM clauses with others.


the sqlalchemy.sql.visitors module is an internal API and is not fully public. It is subject to change and may additionally not function as expected for use patterns that aren’t considered within SQLAlchemy’s own internals.

The sqlalchemy.sql.visitors module is part of the internals of SQLAlchemy and it is not usually used by calling application code. It is however used in certain edge cases such as when constructing caching routines as well as when building out custom SQL expressions using the Custom SQL Constructs and Compilation Extension.

Visitor/traversal interface and library functions.

SQLAlchemy schema and expression constructs rely on a Python-centric version of the classic “visitor” pattern as the primary way in which they apply functionality. The most common use of this pattern is statement compilation, where individual expression classes match up to rendering methods that produce a string result. Beyond this, the visitor system is also used to inspect expressions for various information and patterns, as well as for the purposes of applying transformations to expressions.

Examples of how the visit system is used can be seen in the source code of for example the sqlalchemy.sql.util and the sqlalchemy.sql.compiler modules. Some background on clause adaption is also at .

function sqlalchemy.sql.visitors.iterate(obj, opts={})

Traverse the given expression structure, returning an iterator.

Traversal is configured to be breadth-first.

The central API feature used by the iterate() function is the ClauseElement.get_children() method of ClauseElement objects. This method should return all the ClauseElement objects which are associated with a particular ClauseElement object. For example, a Case structure will refer to a series of ColumnElement objects within its “whens” and “else_” member variables.

  • objClauseElement structure to be traversed

  • opts – dictionary of iteration options. This dictionary is usually empty in modern usage.

function sqlalchemy.sql.visitors.traverse_using(iterator, obj, visitors)

Visit the given expression structure using the given iterator of objects.

traverse_using() is usually called internally as the result of the traverse() function.

  • iterator – an iterable or sequence which will yield ClauseElement structures; the iterator is assumed to be the product of the iterate() function.

  • obj – the ClauseElement that was used as the target of the iterate() function.

  • visitors – dictionary of visit functions. See traverse() for details on this dictionary.

See also


function sqlalchemy.sql.visitors.traverse(obj, opts, visitors)

Traverse and visit the given expression structure using the default iterator.


from sqlalchemy.sql import visitors

stmt = select([some_table]).where( == 'bar')

def visit_bindparam(bind_param):
    print("found bound value: %s" % bind_param.value)

visitors.traverse(stmt, {}, {"bindparam": visit_bindparam})

The iteration of objects uses the iterate() function, which does a breadth-first traversal using a stack.

  • objClauseElement structure to be traversed

  • opts – dictionary of iteration options. This dictionary is usually empty in modern usage.

  • visitors – dictionary of visit functions. The dictionary should have strings as keys, each of which would correspond to the __visit_name__ of a particular kind of SQL expression object, and callable functions as values, each of which represents a visitor function for that kind of object.

function sqlalchemy.sql.visitors.cloned_traverse(obj, opts, visitors)

Clone the given expression structure, allowing modifications by visitors.

Traversal usage is the same as that of traverse(). The visitor functions present in the visitors dictionary may also modify the internals of the given structure as the traversal proceeds.

The central API feature used by the cloned_traverse() and replacement_traverse() functions, in addition to the ClauseElement.get_children() function that is used to achieve the iteration, is the ClauseElement._copy_internals() method. For a ClauseElement structure to support cloning and replacement traversals correctly, it needs to be able to pass a cloning function into its internal members in order to make copies of them.

function sqlalchemy.sql.visitors.replacement_traverse(obj, opts, replace)

Clone the given expression structure, allowing element replacement by a given replacement function.

This function is very similar to the cloned_traverse() function, except instead of being passed a dictionary of visitors, all elements are unconditionally passed into the given replace function. The replace function then has the option to return an entirely new object which will replace the one given. If it returns None, then the object is kept in place.

The difference in usage between cloned_traverse() and replacement_traverse() is that in the former case, an already-cloned object is passed to the visitor function, and the visitor function can then manipulate the internal state of the object. In the case of the latter, the visitor function should only return an entirely different object, or do nothing.

The use case for replacement_traverse() is that of replacing a FROM clause inside of a SQL structure with a different one, as is a common use case within the ORM.

class sqlalchemy.sql.visitors.Traversible

Base class for visitable objects, applies the TraversibleType metaclass.

class sqlalchemy.sql.visitors.TraversibleType(clsname, bases, clsdict)

Bases: builtins.type

Metaclass which assigns dispatch attributes to various kinds of “visitable” classes.

Attributes include:

  • The _compiler_dispatch method, corresponding to __visit_name__. This is called “external traversal” because the caller of each visit() method is responsible for sub-traversing the inner elements of each object. This is appropriate for string compilers and other traversals that need to call upon the inner elements in a specific pattern.

  • internal traversal collections _children_traversal, _cache_key_traversal, _copy_internals_traversal, generated from an optional _traverse_internals collection of symbols which comes from the InternalTraversal list of symbols. This is called “internal traversal” MARKMARK

class sqlalchemy.sql.visitors.ExternalTraversal

Base class for visitor objects which can traverse externally using the traverse() function.

Direct usage of the traverse() function is usually preferred.

method sqlalchemy.sql.visitors.ExternalTraversal.chain(visitor)

‘Chain’ an additional ClauseVisitor onto this ClauseVisitor.

The chained visitor will receive all visit events after this one.

method sqlalchemy.sql.visitors.ExternalTraversal.iterate(obj)

Traverse the given expression structure, returning an iterator of all elements.

method sqlalchemy.sql.visitors.ExternalTraversal.traverse(obj)

Traverse and visit the given expression structure.

attribute sqlalchemy.sql.visitors.ExternalTraversal.visitor_iterator

Iterate through this visitor and each ‘chained’ visitor.

class sqlalchemy.sql.visitors.InternalTraversal

Defines visitor symbols used for internal traversal.

The InternalTraversal class is used in two ways. One is that it can serve as the superclass for an object that implements the various visit methods of the class. The other is that the symbols themselves of InternalTraversal are used within the _traverse_internals collection. Such as, the Case object defines _travserse_internals as

_traverse_internals = [
    ("value", InternalTraversal.dp_clauseelement),
    ("whens", InternalTraversal.dp_clauseelement_tuples),
    ("else_", InternalTraversal.dp_clauseelement),

Above, the Case class indicates its internal state as the attributes named value, whens, and else_. They each link to an InternalTraversal method which indicates the type of datastructure referred towards.

Using the _traverse_internals structure, objects of type InternalTraversible will have the following methods automatically implemented:

  • Traversible.get_children()

  • Traversible._copy_internals()

  • Traversible._gen_cache_key()

Subclasses can also implement these methods directly, particularly for the Traversible._copy_internals() method, when special steps are needed.

New in version 1.4.

method sqlalchemy.sql.visitors.InternalTraversal.dispatch(visit_symbol)

Given a method from InternalTraversal, return the corresponding method on a subclass.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_annotations_key = symbol('AK')

Visit the _annotations_cache_key element.

This is a dictionary of additional information about a ClauseElement that modifies its role. It should be included when comparing or caching objects, however generating this key is relatively expensive. Visitors should check the “_annotations” dict for non-None first before creating this key.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_anon_name = symbol('AN')

Visit a potentially “anonymized” string value.

The string value is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_boolean = symbol('B')

Visit a boolean value.

The boolean value is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_clauseelement = symbol('CE')

Visit a ClauseElement object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_clauseelement_list = symbol('CL')

Visit a list of ClauseElement objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_clauseelement_tuples = symbol('CT')

Visit a list of tuples which contain ClauseElement objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_dialect_options = symbol('DO')

Visit a dialect options structure.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_dml_multi_values = symbol('DML_MV')

Visit the values() multi-valued list of dictionaries of an Insert object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_dml_ordered_values = symbol('DML_OV')

Visit the values() ordered tuple list of an Update object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_dml_values = symbol('DML_V')

Visit the values() dictionary of a ValuesBase (e.g. Insert or Update) object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_fromclause_canonical_column_collection = symbol('FC')

Visit a FromClause object in the context of the columns attribute.

The column collection is “canonical”, meaning it is the originally defined location of the ColumnClause objects. Right now this means that the object being visited is a TableClause or Table object only.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_fromclause_ordered_set = symbol('CO')

Visit an ordered set of FromClause objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_has_cache_key = symbol('HC')

Visit a HasCacheKey object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_has_cache_key_list = symbol('HL')

Visit a list of HasCacheKey objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_named_ddl_element = symbol('DD')

Visit a simple named DDL element.

The current object used by this method is the Sequence.

The object is only considered to be important for cache key generation as far as its name, but not any other aspects of it.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_operator = symbol('O')

Visit an operator.

The operator is a function from the sqlalchemy.sql.operators module.

The operator value is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_plain_dict = symbol('PD')

Visit a dictionary with string keys.

The keys of the dictionary should be strings, the values should be immutable and hashable. The dictionary is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_plain_obj = symbol('PO')

Visit a plain python object.

The value should be immutable and hashable, such as an integer. The value is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_prefix_sequence = symbol('PS')

Visit the sequence represented by HasPrefixes or HasSuffixes.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_propagate_attrs = symbol('PA')

Visit the propagate attrs dict. this hardcodes to the particular elements we care about right now.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_statement_hint_list = symbol('SH')

Visit the _statement_hints collection of a Select object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_string = symbol('S')

Visit a plain string value.

Examples include table and column names, bound parameter keys, special keywords such as “UNION”, “UNION ALL”.

The string value is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_string_clauseelement_dict = symbol('CD')

Visit a dictionary of string keys to ClauseElement objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_string_list = symbol('SL')

Visit a list of strings.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_string_multi_dict = symbol('MD')

Visit a dictionary of string keys to values which may either be plain immutable/hashable or HasCacheKey objects.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_table_hint_list = symbol('TH')

Visit the _hints collection of a Select object.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_type = symbol('T')

Visit a TypeEngine object

The type object is considered to be significant for cache key generation.

attribute sqlalchemy.sql.visitors.InternalTraversal.dp_unknown_structure = symbol('UK')

Visit an unknown structure.

Previous: Expression Serializer Extension Next: Schema Definition Language