- start building out very comprehensive docstrings for core functions

1 files changed, 447 insertions, 102 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 0e888fcf7..4f3c5dd73 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -45,18 +45,53 @@ def collate(expression, collation):
         _literal_as_text(collation),
         operators.collate, type_=expr.type)
 
-def between(ctest, cleft, cright):
-    """Return a ``BETWEEN`` predicate clause.
+def between(expr, lower_bound, upper_bound):
+    """Produce ``BETWEEN`` predicate clause.
 
-    Equivalent of SQL ``clausetest BETWEEN clauseleft AND clauseright``.
+    E.g.::
 
-    The :func:`between()` method on all
-    :class:`.ColumnElement` subclasses provides
-    similar functionality.
+        from sqlalchemy import between
+        stmt = select([users_table]).where(between(users_table.c.id, 5, 7))
+
+    Would produce SQL resembling::
+
+        SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
+
+    The :func:`.between` function is a standalone version of the
+    :meth:`.ColumnElement.between` method available on all
+    SQL expressions, as in::
+
+        stmt = select([users_table]).where(users_table.c.id.between(5, 7))
+
+    All arguments passed to :func:`.between`, including the left side
+    column expression, are coerced from Python scalar values if a
+    the value is not a :class:`.ColumnElement` subclass.   For example,
+    three fixed values can be compared as in::
+
+        print(between(5, 3, 7))
+
+    Which would produce::
+
+        :param_1 BETWEEN :param_2 AND :param_3
+
+    :param expr: a column expression, typically a :class:`.ColumnElement`
+     instance or alternatively a Python scalar expression to be coerced
+     into a column expression, serving as the left side of the ``BETWEEN``
+     expression.
+
+    :param lower_bound: a column or Python scalar expression serving as the lower
+     bound of the right side of the ``BETWEEN`` expression.
+
+    :param upper_bound: a column or Python scalar expression serving as the
+     upper bound of the right side of the ``BETWEEN`` expression.
+
+    .. seealso::
+
+        :meth:`.ColumnElement.between`
 
     """
-    ctest = _literal_as_binds(ctest)
-    return ctest.between(cleft, cright)
+    expr = _literal_as_binds(expr)
+    return expr.between(lower_bound, upper_bound)
 
 def literal(value, type_=None):
     """Return a literal clause, bound to a bind parameter.
@@ -506,13 +541,11 @@ class ColumnElement(ClauseElement, operators.ColumnOperators):
         >>> print column('a') + column('b')
         a + b
 
-    :class:`.ColumnElement` supports the ability to be a *proxy* element,
-    which indicates that the :class:`.ColumnElement` may be associated with
-    a :class:`.Selectable` which was derived from another :class:`.Selectable`.
-    An example of a "derived" :class:`.Selectable` is an :class:`.Alias` of a
-    :class:`~sqlalchemy.schema.Table`.  For the ambitious, an in-depth
-    discussion of this concept can be found at
-    `Expression Transformations <http://techspot.zzzeek.org/2008/01/23/expression-transformations/>`_.
+    .. seealso::
+
+        :class:`.Column`
+
+        :func:`.expression.column`
 
     """
 
@@ -689,7 +722,22 @@ class ColumnElement(ClauseElement, operators.ColumnOperators):
 
 
 class BindParameter(ColumnElement):
-    """Represent a bound parameter value.
+    """Represent a "bound expression" for usage in a SQL construct.
+
+    :class:`.BindParameter` is invoked explicitly using the
+    :func:`.bindparam` function, as in::
+
+        from sqlalchemy import bindparam
+
+        stmt = select([users_table]).\\
+                    where(users_table.c.name == bindparam('username'))
+
+    Detailed discussion of how :class:`.BindParameter` is used is
+    at :func:`.bindparam`.
+
+    .. seealso::
+
+        :func:`.bindparam`
 
     """
 
@@ -703,71 +751,193 @@ class BindParameter(ColumnElement):
                             isoutparam=False,
                             _compared_to_operator=None,
                             _compared_to_type=None):
-        """Construct a new :class:`.BindParameter`.
-
-            :param key:
-              the key for this bind param.  Will be used in the generated
-              SQL statement for dialects that use named parameters.  This
-              value may be modified when part of a compilation operation,
-              if other :class:`BindParameter` objects exist with the same
-              key, or if its length is too long and truncation is
-              required.
+        """Produce a "bound expression" for usage in a SQL construct.
+
+        The return value is an instance of :class:`.BindParameter`; this
+        is a :class:`.ColumnElement` subclass which represents a so-called
+        "placeholder" value in a SQL expression, the value of which is supplied
+        at the point at which the statement in executed against a database
+        connection.
+
+        In SQLAlchemy, the :func:`.bindparam` construct has
+        the ability to carry along the actual value that will be ultimately
+        used at expression time.  In this way, it serves not just as
+        a "placeholder" for eventual population, but also as a means of
+        representing so-called "unsafe" values which should not be rendered
+        directly in a SQL statement, but rather should be passed along
+        to the :term:`DBAPI` as values which need to be correctly escaped
+        and potentially handled for type-safety.
+
+        When using :func:`.bindparam` explicitly, the use case is typically
+        one of traditional deferment of parameters; the :func:`.bindparam`
+        construct accepts a name which can then be referred to at execution
+        time::
 
-            :param value:
-              Initial value for this bind param.  This value may be
-              overridden by the dictionary of parameters sent to statement
-              compilation/execution.
-
-              Defaults to ``None``, however if neither ``value`` nor
-              ``callable`` are passed explicitly, the ``required`` flag will be
-              set to ``True`` which has the effect of requiring a value be present
-              when the statement is actually executed.
-
-              .. versionchanged:: 0.8 The ``required`` flag is set to ``True``
-                 automatically if ``value`` or ``callable`` is not passed.
+            from sqlalchemy import bindparam
 
-            :param callable\_:
-              A callable function that takes the place of "value".  The function
-              will be called at statement execution time to determine the
-              ultimate value.   Used for scenarios where the actual bind
-              value cannot be determined at the point at which the clause
-              construct is created, but embedded bind values are still desirable.
+            stmt = select([users_table]).\\
+                        where(users_table.c.name == bindparam('username'))
 
-            :param type\_:
-              A ``TypeEngine`` object that will be used to pre-process the
-              value corresponding to this :class:`BindParameter` at
-              execution time.
+        The above statement, when rendered, will produce SQL similar to::
 
-            :param unique:
-              if True, the key name of this BindParamClause will be
-              modified if another :class:`BindParameter` of the same name
-              already has been located within the containing
-              :class:`.ClauseElement`.
+            SELECT id, name FROM user WHERE name = :username
 
-            :param required:
-              If ``True``, a value is required at execution time.  If not passed,
-              is set to ``True`` or ``False`` based on whether or not
-              one of ``value`` or ``callable`` were passed..
+        In order to populate the value of ``:username`` above, the value
+        would typically be applied at execution time to a method
+        like :meth:`.Connection.execute`::
 
-              .. versionchanged:: 0.8 If the ``required`` flag is not specified,
-                 it will be set automatically to ``True`` or ``False`` depending
-                 on whether or not the ``value`` or ``callable`` parameters
-                 were specified.
+            result = connection.execute(stmt, username='wendy')
 
-            :param quote:
-              True if this parameter name requires quoting and is not
-              currently known as a SQLAlchemy reserved word; this currently
-              only applies to the Oracle backend.
+        Explicit use of :func:`.bindparam` is also common when producing
+        UPDATE or DELETE statements that are to be invoked multiple times,
+        where the WHERE criterion of the statement is to change on each
+        invocation, such as::
 
-            :param isoutparam:
-              if True, the parameter should be treated like a stored procedure
-              "OUT" parameter.
+            stmt = users_table.update().\\
+                            where(user_table.c.name == bindparam('username')).\\
+                            values(fullname=bindparam('fullname'))
+
+            connection.execute(stmt, [
+                                {"username": "wendy", "fullname": "Wendy Smith"},
+                                {"username": "jack", "fullname": "Jack Jones"},
+                        ])
+
+        SQLAlchemy's Core expression system makes wide use of :func:`.bindparam`
+        in an implicit sense.   It is typical that Python literal values passed to
+        virtually all SQL expression functions are coerced into fixed
+        :func:`.bindparam` constructs.  For example, given a comparison operation
+        such as::
+
+            expr = users_table.c.name == 'Wendy'
+
+        The above expression will produce a :class:`.BinaryExpression`
+        contruct, where the left side is the :class:`.Column` object
+        representing the ``name`` column, and the right side is a :class:`.BindParameter`
+        representing the literal value::
+
+            print(repr(expr.right))
+            BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
+
+        The expression above will render SQL such as::
+
+            user.name = :name_1
+
+        Where the ``:name_1`` parameter name is an anonymous name.  The
+        actual string ``Wendy`` is not in the rendered string, but is carried
+        along where it is later used within statement execution.  If we
+        invoke a statement like the following::
+
+            stmt = select([users_table]).where(users_table.c.name == 'Wendy')
+            result = connection.execute(stmt)
+
+        We would see SQL logging output as::
+
+            SELECT "user".id, "user".name
+            FROM "user"
+            WHERE "user".name = %(name_1)s
+            {'name_1': 'Wendy'}
+
+        Above, we see that ``Wendy`` is passed as a parameter to the database,
+        while the placeholder ``:name_1`` is rendered in the appropriate form
+        for the target database, in this case the Postgresql database.
+
+        Similarly, :func:`.bindparam` is invoked automatically
+        when working with :term:`CRUD` statements as far as the "VALUES"
+        portion is concerned.   The :func:`.insert` construct produces an
+        ``INSERT`` expression which will, at statement execution time, generate
+        bound placeholders based on the arguments passed, as in::
+
+            stmt = users_table.insert()
+            result = connection.execute(stmt, name='Wendy')
+
+        The above will produce SQL output as::
+
+            INSERT INTO "user" (name) VALUES (%(name)s)
+            {'name': 'Wendy'}
+
+        The :class:`.Insert` construct, at compilation/execution time,
+        rendered a single :func:`.bindparam` mirroring the column
+        name ``name`` as a result of the single ``name`` parameter
+        we passed to the :meth:`.Connection.execute` method.
+
+        :param key:
+          the key (e.g. the name) for this bind param.
+          Will be used in the generated
+          SQL statement for dialects that use named parameters.  This
+          value may be modified when part of a compilation operation,
+          if other :class:`BindParameter` objects exist with the same
+          key, or if its length is too long and truncation is
+          required.
+
+        :param value:
+          Initial value for this bind param.  Will be used at statement
+          execution time as the value for this parameter passed to the
+          DBAPI, if no other value is indicated to the statement execution
+          method for this particular parameter name.  Defaults to ``None``.
+
+        :param callable\_:
+          A callable function that takes the place of "value".  The function
+          will be called at statement execution time to determine the
+          ultimate value.   Used for scenarios where the actual bind
+          value cannot be determined at the point at which the clause
+          construct is created, but embedded bind values are still desirable.
+
+        :param type\_:
+          A :class:`.TypeEngine` class or instance representing an optional
+          datatype for this :func:`.bindparam`.  If not passed, a type
+          may be determined automatically for the bind, based on the given
+          value; for example, trivial Python types such as ``str``,
+          ``int``, ``bool``
+          may result in the :class:`.String`, :class:`.Integer` or
+          :class:`.Boolean` types being autoamtically selected.
+
+          The type of a :func:`.bindparam` is significant especially in that
+          the type will apply pre-processing to the value before it is
+          passed to the database.  For example, a :func:`.bindparam` which
+          refers to a datetime value, and is specified as holding the
+          :class:`.DateTime` type, may apply conversion needed to the
+          value (such as stringification on SQLite) before passing the value
+          to the database.
+
+        :param unique:
+          if True, the key name of this :class:`.BindParameter` will be
+          modified if another :class:`.BindParameter` of the same name
+          already has been located within the containing
+          expression.  This flag is used generally by the internals
+          when producing so-called "anonymous" bound expressions, it
+          isn't generally applicable to explicitly-named :func:`.bindparam`
+          constructs.
+
+        :param required:
+          If ``True``, a value is required at execution time.  If not passed,
+          it defaults to ``True`` if neither :paramref:`.bindparam.value`
+          or :paramref:`.bindparam.callable` were passed.  If either of these
+          parameters are present, then :paramref:`.bindparam.required` defaults
+          to ``False``.
+
+          .. versionchanged:: 0.8 If the ``required`` flag is not specified,
+             it will be set automatically to ``True`` or ``False`` depending
+             on whether or not the ``value`` or ``callable`` parameters
+             were specified.
+
+        :param quote:
+          True if this parameter name requires quoting and is not
+          currently known as a SQLAlchemy reserved word; this currently
+          only applies to the Oracle backend, where bound names must
+          sometimes be quoted.
+
+        :param isoutparam:
+          if True, the parameter should be treated like a stored procedure
+          "OUT" parameter.  This applies to backends such as Oracle which
+          support OUT parameters.
 
-              .. seealso::
+        .. seealso::
 
-                :func:`.outparam`
+            :ref:`coretutorial_bind_param`
 
+            :ref:`coretutorial_insert_expressions`
 
+            :func:`.outparam`
 
         """
         if isinstance(key, ColumnClause):
@@ -1521,22 +1691,73 @@ class BooleanClauseList(ClauseList, ColumnElement):
 
     @classmethod
     def and_(cls, *clauses):
-        """Join a list of clauses together using the ``AND`` operator.
+        """Produce a conjunction of clauses joined by ``AND``.
+
+        E.g.::
+
+            from sqlalchemy import and_
+
+            stmt = select([users_table]).where(
+                            and_(
+                                users_table.c.name == 'wendy',
+                                users_table.c.enrolled == True
+                            )
+                        )
+
+        The :func:`.and_` conjunction is also available using the
+        Python ``&`` operator (though note that compound expressions
+        need to be parenthesized in order to function with Python
+        operator precedence behavior)::
 
-        The ``&`` operator is also overloaded on all :class:`.ColumnElement`
-        subclasses to produce the
-        same result.
+            stmt = select([users_table]).where(
+                            (users_table.c.name == 'wendy') &
+                            (users_table.c.enrolled == True)
+                        )
+
+        The :func:`.and_` operation is also implicit in some cases;
+        the :meth:`.Select.where` method for example can be invoked multiple
+        times against a statement, which will have the effect of each
+        clause being combined using :func:`.and_`::
+
+            stmt = select([users_table]).\\
+                        where(users_table.c.name == 'wendy').\\
+                        where(users_table.c.enrolled == True)
+
+        .. seealso::
+
+            :func:`.or_`
 
         """
         return cls._construct(operators.and_, True_, False_, *clauses)
 
     @classmethod
     def or_(cls, *clauses):
-        """Join a list of clauses together using the ``OR`` operator.
+        """Produce a conjunction of clauses joined by ``OR``.
 
-        The ``|`` operator is also overloaded on all
-        :class:`.ColumnElement` subclasses to produce the
-        same result.
+        E.g.::
+
+            from sqlalchemy import or_
+
+            stmt = select([users_table]).where(
+                            or_(
+                                users_table.c.name == 'wendy',
+                                users_table.c.name == 'jack'
+                            )
+                        )
+
+        The :func:`.or_` conjunction is also available using the
+        Python ``|`` operator (though note that compound expressions
+        need to be parenthesized in order to function with Python
+        operator precedence behavior)::
+
+            stmt = select([users_table]).where(
+                            (users_table.c.name == 'wendy') |
+                            (users_table.c.name == 'jack')
+                        )
+
+        .. seealso::
+
+            :func:`.and_`
 
         """
         return cls._construct(operators.or_, False_, True_, *clauses)
@@ -1831,15 +2052,38 @@ class UnaryExpression(ColumnElement):
 
     @classmethod
     def _create_nullsfirst(cls, column):
-        """Return a NULLS FIRST ``ORDER BY`` clause element.
+        """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
 
-        e.g.::
+        :func:`.nullsfirst` is intended to modify the expression produced
+        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
+        should be handled when they are encountered during ordering::
+
+
+            from sqlalchemy import desc, nullsfirst
+
+            stmt = select([users_table]).\\
+                        order_by(nullsfirst(desc(users_table.c.name)))
 
-          someselect.order_by(desc(table1.mycol).nullsfirst())
+        The SQL expression from the above would resemble::
 
-        produces::
+            SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
 
-          ORDER BY mycol DESC NULLS FIRST
+        Like :func:`.asc` and :func:`.desc`, :func:`.nullsfirst` is typically
+        invoked from the column expression itself using :meth:`.ColumnElement.nullsfirst`,
+        rather than as its standalone function version, as in::
+
+            stmt = select([users_table]).\\
+                        order_by(users_table.c.name.desc().nullsfirst())
+
+        .. seealso::
+
+            :func:`.asc`
+
+            :func:`.desc`
+
+            :func:`.nullslast`
+
+            :meth:`.Select.order_by`
 
         """
         return UnaryExpression(
@@ -1848,15 +2092,38 @@ class UnaryExpression(ColumnElement):
 
     @classmethod
     def _create_nullslast(cls, column):
-        """Return a NULLS LAST ``ORDER BY`` clause element.
+        """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
 
-        e.g.::
+        :func:`.nullslast` is intended to modify the expression produced
+        by :func:`.asc` or :func:`.desc`, and indicates how NULL values
+        should be handled when they are encountered during ordering::
+
+
+            from sqlalchemy import desc, nullslast
+
+            stmt = select([users_table]).\\
+                        order_by(nullslast(desc(users_table.c.name)))
 
-          someselect.order_by(desc(table1.mycol).nullslast())
+        The SQL expression from the above would resemble::
 
-        produces::
+            SELECT id, name FROM user ORDER BY name DESC NULLS LAST
 
-            ORDER BY mycol DESC NULLS LAST
+        Like :func:`.asc` and :func:`.desc`, :func:`.nullslast` is typically
+        invoked from the column expression itself using :meth:`.ColumnElement.nullslast`,
+        rather than as its standalone function version, as in::
+
+            stmt = select([users_table]).\\
+                        order_by(users_table.c.name.desc().nullslast())
+
+        .. seealso::
+
+            :func:`.asc`
+
+            :func:`.desc`
+
+            :func:`.nullsfirst`
+
+            :meth:`.Select.order_by`
 
         """
         return UnaryExpression(
@@ -1869,11 +2136,33 @@ class UnaryExpression(ColumnElement):
 
         e.g.::
 
-          someselect.order_by(desc(table1.mycol))
+            from sqlalchemy import desc
+
+            stmt = select([users_table]).order_by(desc(users_table.c.name))
+
+        will produce SQL as::
+
+            SELECT id, name FROM user ORDER BY name DESC
+
+        The :func:`.desc` function is a standalone version of the
+        :meth:`.ColumnElement.desc` method available on all SQL expressions,
+        e.g.::
+
+
+            stmt = select([users_table]).order_by(users_table.c.name.desc())
+
+        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
+         with which to apply the :func:`.desc` operation.
+
+        .. seealso::
+
+            :func:`.asc`
 
-        produces::
+            :func:`.nullsfirst`
 
-            ORDER BY mycol DESC
+            :func:`.nullslast`
+
+            :meth:`.Select.order_by`
 
         """
         return UnaryExpression(
@@ -1885,11 +2174,32 @@ class UnaryExpression(ColumnElement):
 
         e.g.::
 
-          someselect.order_by(asc(table1.mycol))
+            from sqlalchemy import asc
+            stmt = select([users_table]).order_by(asc(users_table.c.name))
+
+        will produce SQL as::
+
+            SELECT id, name FROM user ORDER BY name ASC
+
+        The :func:`.asc` function is a standalone version of the
+        :meth:`.ColumnElement.asc` method available on all SQL expressions,
+        e.g.::
+
+
+            stmt = select([users_table]).order_by(users_table.c.name.asc())
+
+        :param column: A :class:`.ColumnElement` (e.g. scalar SQL expression)
+         with which to apply the :func:`.asc` operation.
+
+        .. seealso::
+
+            :func:`.desc`
+
+            :func:`.nullsfirst`
 
-        produces::
+            :func:`.nullslast`
 
-          ORDER BY mycol ASC
+            :meth:`.Select.order_by`
 
         """
         return UnaryExpression(
@@ -1897,15 +2207,38 @@ class UnaryExpression(ColumnElement):
 
     @classmethod
     def _create_distinct(cls, expr):
-        """Return a ``DISTINCT`` clause.
+        """Produce an column-expression-level unary ``DISTINCT`` clause.
 
-        e.g.::
+        This applies the ``DISTINCT`` keyword to an individual column
+        expression, and is typically contained within an aggregate function,
+        as in::
+
+            from sqlalchemy import distinct, func
+            stmt = select([func.count(distinct(users_table.c.name))])
+
+        The above would produce an expression resembling::
+
+            SELECT COUNT(DISTINCT name) FROM user
 
-            distinct(a)
+        The :func:`.distinct` function is also available as a column-level
+        method, e.g. :meth:`.ColumnElement.distinct`, as in::
 
-        renders::
+            stmt = select([func.count(users_table.c.name.distinct())])
+
+        The :func:`.distinct` operator is different from the
+        :meth:`.Select.distinct` method of :class:`.Select`,
+        which produces a ``SELECT`` statement
+        with ``DISTINCT`` applied to the result set as a whole,
+        e.g. a ``SELECT DISTINCT`` expression.  See that method for further
+        information.
+
+        .. seealso::
 
-            DISTINCT a
+            :meth:`.ColumnElement.distinct`
+
+            :meth:`.Select.distinct`
+
+            :data:`.func`
 
         """
         expr = _literal_as_binds(expr)
@@ -2278,12 +2611,18 @@ class Label(ColumnElement):
 class ColumnClause(Immutable, 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 :class:`.Selectable`.
+    :class:`.ColumnClause` is the immediate superclass of the schema-specific
+    :class:`.Column` object.  While the :class:`.Column` class has all the
+    same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause`
+    class is usable by itself in those cases where behavioral requirements
+    are limited to simple SQL expression generation.  The object has none of the
+    associations with schema-level metadata or with execution-time behavior
+    that :class:`.Column` does, so in that sense is a "lightweight" version
+    of :class:`.Column`.
 
     :class:`.ColumnClause` is constructed by itself typically via
-    the :func:`~.expression.column` function.  It may be placed directly
+    the :func:`~.expression.column` function.  The datatype is optional.
+    It may be placed directly
     into constructs such as :func:`.select` constructs::
 
         from sqlalchemy.sql import column, select
@@ -2318,6 +2657,12 @@ class ColumnClause(Immutable, ColumnElement):
     not associated with any :class:`.schema.MetaData`, unlike it's
     more full fledged :class:`.schema.Table` counterpart.
 
+    .. seealso::
+
+        :class:`.Column`
+
+        :ref:`metadata_toplevel`
+
     """
     __visit_name__ = 'column'