Commits

Mike Bayer committed e990ea2 Merge

merge into cymysql branch...

Comments (0)

Files changed (114)

 d557287431986274a796348750f1c6ce885b196c rel_0_7_6
 6495bcf87e10461675d8905d62f5632e634ec33c rel_0_8_0b1
 8d82961d34643c8d53d865ddf76911807a36fde6 rel_0_8_8b2
+662aaaa7bc65c47db7ecd2e0269f8a8fbb613acd rel_0_8_0

README.dialects.rst

 
     from sqlalchemy.testing import runner
 
-    runner.main()
+    # use this in setup.py 'test_suite':
+    # test_suite="run_tests.setup_py_test"
+    def setup_py_test():
+        runner.setup_py_test()
+
+    if __name__ == '__main__':
+        runner.main()
 
   Where above, the ``registry`` module, introduced in SQLAlchemy 0.8, provides
   an in-Python means of installing the dialect entrypoints without the use

doc/build/builder/changelog.py

                         self._parsed_content['released']))
         else:
             topsection.append(nodes.Text("no release date"))
+
+        intro_para = nodes.paragraph('', '')
+        for len_, text in enumerate(self._parsed_content['text']):
+            if ".. change::" in text:
+                break
+        if len_:
+            self.state.nested_parse(self._parsed_content['text'][0:len_], 0,
+                            intro_para)
+            topsection.append(intro_para)
+
         return topsection
 
 

doc/build/changelog/changelog_02.rst

       modified version (works in py2.3/2.4!) that uses a threading.RLock
       for a mutex.  this is to fix a reported case where a ConnectionFairy's
       __del__() method got called within the Queue's get() method, which
-      then returns its connection to the Queue via the the put() method,
+      then returns its connection to the Queue via the put() method,
       causing a reentrant hang unless threading.RLock is used.
 
     .. change::

doc/build/changelog/changelog_03.rst

         :tickets: 
 
       added a mutex to the mapper compilation step. ive been reluctant to add any
-      kind of threading anything to SA but this is one spot that its its really
+      kind of threading anything to SA but this is one spot that its really
       needed since mappers are typically "global", and while their state does not
       change during normal operation, the initial compilation step does modify
       internal state significantly, and this step usually occurs not at

doc/build/changelog/changelog_04.rst

       new synonym() behavior: an attribute will be placed on the mapped
       class, if one does not exist already, in all cases. if a property
       already exists on the class, the synonym will decorate the property
-      with the appropriate comparison operators so that it can be used in in
+      with the appropriate comparison operators so that it can be used in
       column expressions just like any other mapped attribute (i.e. usable in
       filter(), etc.) the "proxy=True" flag is deprecated and no longer means
       anything. Additionally, the flag "map_column=True" will automatically
         :tickets: 
 
       PG reflection, upon seeing the default schema name being used explicitly
-      as the "schema" argument in a Table, will assume that this is the the
+      as the "schema" argument in a Table, will assume that this is the
       user's desired convention, and will explicitly set the "schema" argument
       in foreign-key-related reflected tables, thus making them match only
       with Table constructors that also use the explicit "schema" argument
         :tickets: 810
 
       Fixed breakage with postgres and multiple two-phase transactions. Two-phase
-      commits and and rollbacks didn't automatically end up with a new transaction
+      commits and rollbacks didn't automatically end up with a new transaction
       as the usual dbapi commits/rollbacks do.
 
     .. change::

doc/build/changelog/changelog_06.rst

         :tickets: 1953
 
       The cx_oracle "decimal detection" logic, which takes place
-      for for result set columns with ambiguous numeric characteristics,
+      for result set columns with ambiguous numeric characteristics,
       now uses the decimal point character determined by the locale/
       NLS_LANG setting, using an on-first-connect detection of
       this character.  cx_oracle 5.0.3 or greater is also required
         :tickets: 1071
 
       Postgresql now reflects sequence names associated with
-      SERIAL columns correctly, after the name of of the sequence
+      SERIAL columns correctly, after the name of the sequence
       has been changed.  Thanks to Kumar McMillan for the patch.
 
     .. change::

doc/build/changelog/changelog_07.rst

 0.7 Changelog
 ==============
 
+.. changelog::
+    :version: 0.7.11
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2699
+
+      Fixed bug when a query of the form:
+      ``query(SubClass).options(subqueryload(Baseclass.attrname))``,
+      where ``SubClass`` is a joined inh of ``BaseClass``,
+      would fail to apply the ``JOIN`` inside the subquery
+      on the attribute load, producing a cartesian product.
+      The populated results still tended to be correct as additional
+      rows are just ignored, so this issue may be present as a
+      performance degradation in applications that are
+      otherwise working correctly.
+
+    .. change::
+        :tags: bug, orm
+        :tickets: 2689
+
+      Fixed bug in unit of work whereby a joined-inheritance
+      subclass could insert the row for the "sub" table
+      before the parent table, if the two tables had no
+      ForeignKey constraints set up between them.
+
+    .. change::
+        :tags: feature, postgresql
+        :tickets: 2676
+
+      Added support for Postgresql's traditional SUBSTRING
+      function syntax, renders as "SUBSTRING(x FROM y FOR z)"
+      when regular ``func.substring()`` is used.
+      Courtesy Gunnlaugur Þór Briem.
+
+    .. change::
+        :tags: bug, tests
+        :tickets: 2669
+        :pullreq: 41
+
+      Fixed an import of "logging" in test_execute which was not
+      working on some linux platforms.
+
+    .. change::
+        :tags: bug, orm
+        :tickets: 2674
+
+      Improved the error message emitted when a "backref loop" is detected,
+      that is when an attribute event triggers a bidirectional
+      assignment between two other attributes with no end.
+      This condition can occur not just when an object of the wrong
+      type is assigned, but also when an attribute is mis-configured
+      to backref into an existing backref pair.
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2674
+
+      A warning is emitted when a MapperProperty is assigned to a mapper
+      that replaces an existing property, if the properties in question
+      aren't plain column-based properties.   Replacement of relationship
+      properties is rarely (ever?) what is intended and usually refers to a
+      mapper mis-configuration.   This will also warn if a backref configures
+      itself on top of an existing one in an inheritance relationship
+      (which is an error in 0.8).
 
 .. changelog::
     :version: 0.7.10

doc/build/changelog/changelog_08.rst

 ==============
 
 .. changelog::
+    :version: 0.8.1
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2708
+
+      Improved the behavior of instance management regarding
+      the creation of strong references within the Session;
+      an object will no longer have an internal reference cycle
+      created if it's in the transient state or moves into the
+      detached state - the strong ref is created only when the
+      object is attached to a Session and is removed when the
+      object is detached.  This makes it somewhat safer for an
+      object to have a `__del__()` method, even though this is
+      not recommended, as relationships with backrefs produce
+      cycles too.  A warning has been added when a class with
+      a `__del__()` method is mapped.
+
+    .. change::
+      :tags: bug, sql
+      :tickets: 2702
+
+      A major fix to the way in which a select() object produces
+      labeled columns when apply_labels() is used; this mode
+      produces a SELECT where each column is labeled as in
+      <tablename>_<columnname>, to remove column name collisions
+      for a multiple table select.   The fix is that if two labels
+      collide when combined with the table name, i.e.
+      "foo.bar_id" and "foo_bar.id", anonymous aliasing will be
+      applied to one of the dupes.  This allows the ORM to handle
+      both columns independently; previously, 0.7
+      would in some cases silently emit a second SELECT for the
+      column that was "duped", and in 0.8 an ambiguous column error
+      would be emitted.   The "keys" applied to the .c. collection
+      of the select() will also be deduped, so that the "column
+      being replaced" warning will no longer emit for any select()
+      that specifies use_labels, though the dupe key will be given
+      an anonymous label which isn't generally user-friendly.
+
+    .. change::
+      :tags: bug, mysql
+      :pullreq: 54
+
+      Updated a regexp to correctly extract error code on
+      google app engine v1.7.5 and newer.  Courtesy
+      Dan Ring.
+
+    .. change::
+      :tags: bug, examples
+
+      Fixed a long-standing bug in the caching example, where
+      the limit/offset parameter values wouldn't be taken into
+      account when computing the cache key.  The
+      _key_from_query() function has been simplified to work
+      directly from the final compiled statement in order to get
+      at both the full statement as well as the fully processed
+      parameter list.
+
+    .. change::
+      :tags: bug, mssql
+      :tickets: 2355
+
+      Part of a longer series of fixes needed for pyodbc+
+      mssql, a CAST to NVARCHAR(max) has been added to the bound
+      parameter for the table name and schema name in all information schema
+      queries to avoid the issue of comparing NVARCHAR to NTEXT,
+      which seems to be rejected by the ODBC driver in some cases,
+      such as FreeTDS (0.91 only?) plus unicode bound parameters being passed.
+      The issue seems to be specific to the SQL Server information
+      schema tables and the workaround is harmless for those cases
+      where the problem doesn't exist in the first place.
+
+    .. change::
+      :tags: bug, sql
+      :tickets: 2691
+
+      Fixed bug where disconnect detect on error would
+      raise an attribute error if the error were being
+      raised after the Connection object had already
+      been closed.
+
+    .. change::
+      :tags: bug, sql
+      :tickets: 2703
+
+      Reworked internal exception raises that emit
+      a rollback() before re-raising, so that the stack
+      trace is preserved from sys.exc_info() before entering
+      the rollback.  This so that the traceback is preserved
+      when using coroutine frameworks which may have switched
+      contexts before the rollback function returns.
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2697
+
+      Fixed bug whereby ORM would run the wrong kind of
+      query when refreshing an inheritance-mapped class
+      where the superclass was mapped to a non-Table
+      object, like a custom join() or a select(),
+      running a query that assumed a hierarchy that's
+      mapped to individual Table-per-class.
+
+    .. change::
+      :tags: bug, orm
+
+      Fixed `__repr__()` on mapper property constructs
+      to work before the object is initialized, so
+      that Sphinx builds with recent Sphinx versions
+      can read them.
+
+    .. change::
+      :tags: bug, sql, postgresql
+
+      The _Binary base type now converts values through
+      the bytes() callable when run on Python 3; in particular
+      psycopg2 2.5 with Python 3.3 seems to now be returning
+      the "memoryview" type, so this is converted to bytes
+      before return.
+
+    .. change::
+      :tags: bug, sql
+      :tickets: 2695
+
+      Improvements to Connection auto-invalidation
+      handling.  If a non-disconnect error occurs,
+      but leads to a delayed disconnect error within error
+      handling (happens with MySQL), the disconnect condition
+      is detected.  The Connection can now also be closed
+      when in an invalid state, meaning it will raise "closed"
+      on next usage, and additionally the "close with result"
+      feature will work even if the autorollback in an error
+      handling routine fails and regardless of whether the
+      condition is a disconnect or not.
+
+
+    .. change::
+      :tags: bug, orm, declarative
+      :tickets: 2656
+
+      Fixed indirect regression regarding :func:`.has_inherited_table`,
+      where since it considers the current class' ``__table__``, was
+      sensitive to when it was called.  This is 0.7's behavior also,
+      but in 0.7 things tended to "work out" within events like
+      ``__mapper_args__()``.  :func:`.has_inherited_table` now only
+      considers superclasses, so should return the same answer
+      regarding the current class no matter when it's called
+      (obviously assuming the state of the superclass).
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2699
+
+      Fixed bug when a query of the form:
+      ``query(SubClass).options(subqueryload(Baseclass.attrname))``,
+      where ``SubClass`` is a joined inh of ``BaseClass``,
+      would fail to apply the ``JOIN`` inside the subquery
+      on the attribute load, producing a cartesian product.
+      The populated results still tended to be correct as additional
+      rows are just ignored, so this issue may be present as a
+      performance degradation in applications that are
+      otherwise working correctly.  Also in 0.7.11.
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2689
+
+      Fixed bug in unit of work whereby a joined-inheritance
+      subclass could insert the row for the "sub" table
+      before the parent table, if the two tables had no
+      ForeignKey constraints set up between them.
+      Also in 0.7.11.
+
+    .. change::
+      :tags: bug, mssql
+      :pullreq: 47
+
+      Added support for additional "disconnect" messages
+      to the pymssql dialect.  Courtesy John Anderson.
+
+    .. change::
+      :tags: feature, sql
+
+      Loosened the check on dialect-specific argument names
+      passed to Table(); since we want to support external dialects
+      and also want to support args without a certain dialect
+      being installed, it only checks the format of the arg now,
+      rather than looking for that dialect in sqlalchemy.dialects.
+
+    .. change::
+      :tags: bug, sql
+
+      Fixed bug whereby a DBAPI that can return "0"
+      for cursor.lastrowid would not function correctly
+      in conjunction with :attr:`.ResultProxy.inserted_primary_key`.
+
+    .. change::
+      :tags: bug, mssql
+      :tickets: 2683
+      :pullreq: 46
+
+      Fixed Py3K bug regarding "binary" types and
+      pymssql.  Courtesy Marc Abramowitz.
+
+    .. change::
+      :tags: bug, postgresql
+      :tickets: 2680
+
+      Added missing HSTORE type to postgresql type names
+      so that the type can be reflected.
+
+.. changelog::
     :version: 0.8.0
+    :released: March 9, 2013
+
+    .. note::
+
+      There are some new behavioral changes as of 0.8.0
+      not present in 0.8.0b2.  They are present in the
+      migration document as follows:
+
+      * :ref:`legacy_is_orphan_addition`
+
+      * :ref:`metadata_create_drop_tables`
+
+      * :ref:`correlation_context_specific`
+
+    .. change::
+        :tags: feature, postgresql
+        :tickets: 2676
+
+      Added support for Postgresql's traditional SUBSTRING
+      function syntax, renders as "SUBSTRING(x FROM y FOR z)"
+      when regular ``func.substring()`` is used.
+      Also in 0.7.11.  Courtesy Gunnlaugur Þór Briem.
+
+    .. change::
+        :tags: feature, orm
+        :tickets: 2675
+
+      A meaningful :attr:`.QueryableAttribute.info` attribute is
+      added, which proxies down to the ``.info`` attribute on either
+      the :class:`.schema.Column` object if directly present, or
+      the :class:`.MapperProperty` otherwise.  The full behavior
+      is documented and ensured by tests to remain stable.
+
+    .. change::
+        :tags: bug, sql
+        :tickets: 2668
+
+      The behavior of SELECT correlation has been improved such that
+      the :meth:`.Select.correlate` and :meth:`.Select.correlate_except`
+      methods, as well as their ORM analogues, will still retain
+      "auto-correlation" behavior in that the FROM clause is modified
+      only if the output would be legal SQL; that is, the FROM clause
+      is left intact if the correlated SELECT is not used in the context
+      of an enclosing SELECT inside of the WHERE, columns, or HAVING clause.
+      The two methods now only specify conditions to the default
+      "auto correlation", rather than absolute FROM lists.
+
+    .. change::
+        :tags: feature, mysql
+        :pullreq: 42
+
+      New dialect for CyMySQL added, courtesy Hajime Nakagami.
+
+    .. change::
+        :tags: bug, orm
+        :tickets: 2674
+
+      Improved checking for an existing backref name conflict during
+      mapper configuration; will now test for name conflicts on
+      superclasses and subclasses, in addition to the current mapper,
+      as these conflicts break things just as much.  This is new for
+      0.8, but see below for a warning that will also be triggered
+      in 0.7.11.
+
+    .. change::
+        :tags: bug, orm
+        :tickets: 2674
+
+      Improved the error message emitted when a "backref loop" is detected,
+      that is when an attribute event triggers a bidirectional
+      assignment between two other attributes with no end.
+      This condition can occur not just when an object of the wrong
+      type is assigned, but also when an attribute is mis-configured
+      to backref into an existing backref pair.  Also in 0.7.11.
+
+    .. change::
+      :tags: bug, orm
+      :tickets: 2674
+
+      A warning is emitted when a MapperProperty is assigned to a mapper
+      that replaces an existing property, if the properties in question
+      aren't plain column-based properties.   Replacement of relationship
+      properties is rarely (ever?) what is intended and usually refers to a
+      mapper mis-configuration.   Also in 0.7.11.
+
+    .. change::
+        :tags: feature, orm
+
+      Can set/change the "cascade" attribute on a :func:`.relationship`
+      construct after it's been constructed already.  This is not
+      a pattern for normal use but we like to change the setting
+      for demonstration purposes in tutorials.
+
+    .. change::
+        :tags: bug, schema
+        :tickets: 2664
+
+      :meth:`.MetaData.create_all` and :meth:`.MetaData.drop_all` will
+      now accommodate an empty list as an instruction to not create/drop
+      any items, rather than ignoring the collection.
+
+
+    .. change::
+        :tags: bug, tests
+        :tickets: 2669
+        :pullreq: 41
+
+      Fixed an import of "logging" in test_execute which was not
+      working on some linux platforms.  Also in 0.7.11.
 
     .. change::
         :tags: bug, orm

doc/build/changelog/migration_08.rst

     This document describes changes between SQLAlchemy version 0.7,
     undergoing maintenance releases as of October, 2012,
     and SQLAlchemy version 0.8, which is expected for release
-    in late 2012.
+    in early 2013.
 
     Document date: October 25, 2012
+    Updated: March 9, 2013
 
 Introduction
 ============
 
 :ticket:`2179`
 
+.. _correlation_context_specific:
+
+Correlation is now always context-specific
+------------------------------------------
+
+To allow a wider variety of correlation scenarios, the behavior of
+:meth:`.Select.correlate` and :meth:`.Query.correlate` has changed slightly
+such that the SELECT statement will omit the "correlated" target from the
+FROM clause only if the statement is actually used in that context.  Additionally,
+it's no longer possible for a SELECT statement that's placed as a FROM
+in an enclosing SELECT statement to "correlate" (i.e. omit) a FROM clause.
+
+This change only makes things better as far as rendering SQL, in that it's no
+longer possible to render illegal SQL where there are insufficient FROM
+objects relative to what's being selected::
+
+    from sqlalchemy.sql import table, column, select
+
+    t1 = table('t1', column('x'))
+    t2 = table('t2', column('y'))
+    s = select([t1, t2]).correlate(t1)
+
+    print(s)
+
+Prior to this change, the above would return::
+
+    SELECT t1.x, t2.y FROM t2
+
+which is invalid SQL as "t1" is not referred to in any FROM clause.
+
+Now, in the absense of an enclosing SELECT, it returns::
+
+    SELECT t1.x, t2.y FROM t1, t2
+
+Within a SELECT, the correlation takes effect as expected::
+
+    s2 = select([t1, t2]).where(t1.c.x == t2.c.y).where(t1.c.x == s)
+
+    print (s2)
+
+    SELECT t1.x, t2.y FROM t1, t2
+    WHERE t1.x = t2.y AND t1.x =
+        (SELECT t1.x, t2.y FROM t2)
+
+This change is not expected to impact any existing applications, as
+the correlation behavior remains identical for properly constructed
+expressions.  Only an application that relies, most likely within a
+testing scenario, on the invalid string output of a correlated
+SELECT used in a non-correlating context would see any change.
+
+:ticket:`2668`
+
+
+.. _metadata_create_drop_tables:
+
+create_all() and drop_all() will now honor an empty list as such
+----------------------------------------------------------------
+
+The methods :meth:`.MetaData.create_all` and :meth:`.MetaData.drop_all`
+will now accept a list of :class:`.Table` objects that is empty,
+and will not emit any CREATE or DROP statements.  Previously,
+an empty list was interepreted the same as passing ``None``
+for a collection, and CREATE/DROP would be emitted for all
+items unconditionally.
+
+This is a bug fix but some applications may have been relying upon
+the previous behavior.
+
+:ticket:`2664`
+
 Repaired the Event Targeting of :class:`.InstrumentationEvents`
 ----------------------------------------------------------------
 

doc/build/conf.py

 # The short X.Y version.
 version = "0.8"
 # The full version, including alpha/beta/rc tags.
-release = "0.8.0b2"
+release = "0.8.0"
 
-release_date = "December 14, 2012"
+release_date = "March 9, 2013"
 
 site_base = "http://www.sqlalchemy.org"
 

doc/build/core/tutorial.rst

 
     >>> conn = engine.connect()
     >>> conn #doctest: +ELLIPSIS
-    <sqlalchemy.engine.Connection object at 0x...>
+    <sqlalchemy.engine.base.Connection object at 0x...>
 
-The :class:`~sqlalchemy.engine.Connection` object represents an actively
+The :class:`~sqlalchemy.engine.base.Connection` object represents an actively
 checked out DBAPI connection resource. Lets feed it our
 :class:`~sqlalchemy.sql.expression.Insert` object and see what happens:
 
 So the INSERT statement was now issued to the database. Although we got
 positional "qmark" bind parameters instead of "named" bind parameters in the
 output. How come ? Because when executed, the
-:class:`~sqlalchemy.engine.Connection` used the SQLite **dialect** to
+:class:`~sqlalchemy.engine.base.Connection` used the SQLite **dialect** to
 help generate the statement; when we use the ``str()`` function, the statement
 isn't aware of this dialect, and falls back onto a default which uses named
 parameters. We can view this manually as follows:
     'INSERT INTO users (name, fullname) VALUES (?, ?)'
 
 What about the ``result`` variable we got when we called ``execute()`` ? As
-the SQLAlchemy :class:`~sqlalchemy.engine.Connection` object references a
+the SQLAlchemy :class:`~sqlalchemy.engine.base.Connection` object references a
 DBAPI connection, the result, known as a
-:class:`~sqlalchemy.engine.ResultProxy` object, is analogous to the DBAPI
+:class:`~sqlalchemy.engine.result.ResultProxy` object, is analogous to the DBAPI
 cursor object. In the case of an INSERT, we can get important information from
 it, such as the primary key values which were generated from our statement:
 
 various behaviors of expression language constructs. In the usual case, an
 :class:`~sqlalchemy.sql.expression.Insert` statement is usually compiled
 against the parameters sent to the ``execute()`` method on
-:class:`~sqlalchemy.engine.Connection`, so that there's no need to use
+:class:`~sqlalchemy.engine.base.Connection`, so that there's no need to use
 the ``values`` keyword with :class:`~sqlalchemy.sql.expression.Insert`. Lets
 create a generic :class:`~sqlalchemy.sql.expression.Insert` statement again
 and use it in the "normal" way:
     {opensql}INSERT INTO users (id, name, fullname) VALUES (?, ?, ?)
     (2, 'wendy', 'Wendy Williams')
     COMMIT
-    {stop}<sqlalchemy.engine.ResultProxy object at 0x...>
+    {stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
 
-Above, because we specified all three columns in the the ``execute()`` method,
-the compiled :class:`~sqlalchemy.sql.expression.Insert` included all three
-columns. The :class:`~sqlalchemy.sql.expression.Insert` statement is compiled
+Above, because we specified all three columns in the ``execute()`` method,
+the compiled :class:`~.expression.Insert` included all three
+columns. The :class:`~.expression.Insert` statement is compiled
 at execution time based on the parameters we specified; if we specified fewer
-parameters, the :class:`~sqlalchemy.sql.expression.Insert` would have fewer
+parameters, the :class:`~.expression.Insert` would have fewer
 entries in its VALUES clause.
 
 To issue many inserts using DBAPI's ``executemany()`` method, we can send in a
     {opensql}INSERT INTO addresses (user_id, email_address) VALUES (?, ?)
     ((1, 'jack@yahoo.com'), (1, 'jack@msn.com'), (2, 'www@www.org'), (2, 'wendy@aol.com'))
     COMMIT
-    {stop}<sqlalchemy.engine.ResultProxy object at 0x...>
+    {stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
 
 Above, we again relied upon SQLite's automatic generation of primary key
 identifiers for each ``addresses`` row.
 within the COLUMNS clause of the select, and then executing. SQLAlchemy
 expanded the ``users`` table into the set of each of its columns, and also
 generated a FROM clause for us. The result returned is again a
-:class:`~sqlalchemy.engine.ResultProxy` object, which acts much like a
+:class:`~sqlalchemy.engine.result.ResultProxy` object, which acts much like a
 DBAPI cursor, including methods such as
-:func:`~sqlalchemy.engine.ResultProxy.fetchone` and
-:func:`~sqlalchemy.engine.ResultProxy.fetchall`. The easiest way to get
+:func:`~sqlalchemy.engine.result.ResultProxy.fetchone` and
+:func:`~sqlalchemy.engine.result.ResultProxy.fetchall`. The easiest way to get
 rows from it is to just iterate:
 
 .. sourcecode:: pycon+sql
     ...     print row
     (1, u'jack', u'Jack Jones')
     (2, u'wendy', u'Wendy Williams')
-    (3, u'fred', u'Fred Flintstone')
-    (4, u'mary', u'Mary Contrary')
 
 Above, we see that printing each row produces a simple tuple-like result. We
 have more options at accessing the data in each row. One very common way is
     ()
     {stop}name: jack ; fullname: Jack Jones
     name: wendy ; fullname: Wendy Williams
-    name: fred ; fullname: Fred Flintstone
-    name: mary ; fullname: Mary Contrary
 
 Result sets which have pending rows remaining should be explicitly closed
 before discarding. While the cursor and connection resources referenced by the
-:class:`~sqlalchemy.engine.ResultProxy` will be respectively closed and
+:class:`~sqlalchemy.engine.result.ResultProxy` will be respectively closed and
 returned to the connection pool when the object is garbage collected, it's
 better to make it explicit as some database APIs are very picky about such
 things:
     ...     print row
     (u'jack', u'Jack Jones')
     (u'wendy', u'Wendy Williams')
-    (u'fred', u'Fred Flintstone')
-    (u'mary', u'Mary Contrary')
 
 Lets observe something interesting about the FROM clause. Whereas the
 generated statement contains two distinct sections, a "SELECT columns" part
     (2, u'wendy', u'Wendy Williams', 2, 1, u'jack@msn.com')
     (2, u'wendy', u'Wendy Williams', 3, 2, u'www@www.org')
     (2, u'wendy', u'Wendy Williams', 4, 2, u'wendy@aol.com')
-    (3, u'fred', u'Fred Flintstone', 1, 1, u'jack@yahoo.com')
-    (3, u'fred', u'Fred Flintstone', 2, 1, u'jack@msn.com')
-    (3, u'fred', u'Fred Flintstone', 3, 2, u'www@www.org')
-    (3, u'fred', u'Fred Flintstone', 4, 2, u'wendy@aol.com')
-    (4, u'mary', u'Mary Contrary', 1, 1, u'jack@yahoo.com')
-    (4, u'mary', u'Mary Contrary', 2, 1, u'jack@msn.com')
-    (4, u'mary', u'Mary Contrary', 3, 2, u'www@www.org')
-    (4, u'mary', u'Mary Contrary', 4, 2, u'wendy@aol.com')
 
 It placed **both** tables into the FROM clause. But also, it made a real mess.
 Those who are familiar with SQL joins know that this is a **Cartesian
 product**; each row from the ``users`` table is produced against each row from
 the ``addresses`` table. So to put some sanity into this statement, we need a
-WHERE clause. Which brings us to the second argument of :func:`.select`:
+WHERE clause.  We do that using :meth:`.Select.where`:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users, addresses], users.c.id==addresses.c.user_id)
+    >>> s = select([users, addresses]).where(users.c.id == addresses.c.user_id)
     {sql}>>> for row in conn.execute(s):
     ...     print row  # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
+    SELECT users.id, users.name, users.fullname, addresses.id,
+       addresses.user_id, addresses.email_address
     FROM users, addresses
     WHERE users.id = addresses.user_id
     ()
 ``addresses`` rows made sense. But let's look at that expression? It's using
 just a Python equality operator between two different
 :class:`~sqlalchemy.schema.Column` objects. It should be clear that something
-is up. Saying ``1==1`` produces ``True``, and ``1==2`` produces ``False``, not
+is up. Saying ``1 == 1`` produces ``True``, and ``1 == 2`` produces ``False``, not
 a WHERE clause. So lets see exactly what that expression is doing:
 
 .. sourcecode:: pycon+sql
 
-    >>> users.c.id==addresses.c.user_id #doctest: +ELLIPSIS
+    >>> users.c.id == addresses.c.user_id #doctest: +ELLIPSIS
     <sqlalchemy.sql.expression.BinaryExpression object at 0x...>
 
 Wow, surprise ! This is neither a ``True`` nor a ``False``. Well what is it ?
 
 .. sourcecode:: pycon+sql
 
-    >>> str(users.c.id==addresses.c.user_id)
+    >>> str(users.c.id == addresses.c.user_id)
     'users.id = addresses.user_id'
 
 As you can see, the ``==`` operator is producing an object that is very much
-like the :class:`~sqlalchemy.sql.expression.Insert` and :func:`.select`
+like the :class:`~.expression.Insert` and :func:`.select`
 objects we've made so far, thanks to Python's ``__eq__()`` builtin; you call
 ``str()`` on it and it produces SQL. By now, one can see that everything we
 are working with is ultimately the same type of object. SQLAlchemy terms the
-base class of all of these expressions as ``sqlalchemy.sql.ClauseElement``.
+base class of all of these expressions as :class:`~.expression.ColumnElement`.
 
 Operators
 ==========
 
 .. sourcecode:: pycon+sql
 
-    >>> print users.c.id==addresses.c.user_id
+    >>> print users.c.id == addresses.c.user_id
     users.id = addresses.user_id
 
 If we use a literal value (a literal meaning, not a SQLAlchemy clause object),
 
 .. sourcecode:: pycon+sql
 
-    >>> print users.c.id==7
+    >>> print users.c.id == 7
     users.id = :id_1
 
-The ``7`` literal is embedded in
-:class:`~sqlalchemy.sql.expression.ClauseElement`; we can use the same trick
+The ``7`` literal is embedded the resulting
+:class:`~.expression.ColumnElement`; we can use the same trick
 we did with the :class:`~sqlalchemy.sql.expression.Insert` object to see it:
 
 .. sourcecode:: pycon+sql
 
-    >>> (users.c.id==7).compile().params
+    >>> (users.c.id == 7).compile().params
     {u'id_1': 7}
 
 Most Python operators, as it turns out, produce a SQL expression here, like
     >>> print users.c.id + addresses.c.id
     users.id + addresses.id
 
-Interestingly, the type of the :class:`~sqlalchemy.schema.Column` is important
-! If we use ``+`` with two string based columns (recall we put types like
+Interestingly, the type of the :class:`~sqlalchemy.schema.Column` is important!
+If we use ``+`` with two string based columns (recall we put types like
 :class:`~sqlalchemy.types.Integer` and :class:`~sqlalchemy.types.String` on
 our :class:`~sqlalchemy.schema.Column` objects at the beginning), we get
 something different:
 
 .. sourcecode:: pycon+sql
 
-    >>> print (users.c.name + users.c.fullname).compile(bind=create_engine('mysql://'))
+    >>> print (users.c.name + users.c.fullname).\
+    ...      compile(bind=create_engine('mysql://'))
     concat(users.name, users.fullname)
 
 The above illustrates the SQL that's generated for an
 We'd like to show off some of our operators inside of :func:`.select`
 constructs. But we need to lump them together a little more, so let's first
 introduce some conjunctions. Conjunctions are those little words like AND and
-OR that put things together. We'll also hit upon NOT. AND, OR and NOT can work
+OR that put things together. We'll also hit upon NOT. :func:`.and_`, :func:`.or_`,
+and :func:`.not_` can work
 from the corresponding functions SQLAlchemy provides (notice we also throw in
-a LIKE):
+a :meth:`~.ColumnOperators.like`):
 
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import and_, or_, not_
-    >>> print and_(users.c.name.like('j%'), users.c.id==addresses.c.user_id, #doctest: +NORMALIZE_WHITESPACE
-    ...     or_(addresses.c.email_address=='wendy@aol.com', addresses.c.email_address=='jack@yahoo.com'),
-    ...     not_(users.c.id>5))
+    >>> print and_(
+    ...         users.c.name.like('j%'),
+    ...         users.c.id == addresses.c.user_id, #doctest: +NORMALIZE_WHITESPACE
+    ...         or_(
+    ...              addresses.c.email_address == 'wendy@aol.com',
+    ...              addresses.c.email_address == 'jack@yahoo.com'
+    ...         ),
+    ...         not_(users.c.id > 5)
+    ...       )
     users.name LIKE :name_1 AND users.id = addresses.user_id AND
-    (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)
+    (addresses.email_address = :email_address_1
+       OR addresses.email_address = :email_address_2)
     AND users.id <= :id_1
 
 And you can also use the re-jiggered bitwise AND, OR and NOT operators,
 
 .. sourcecode:: pycon+sql
 
-    >>> print users.c.name.like('j%') & (users.c.id==addresses.c.user_id) &  \
-    ...     ((addresses.c.email_address=='wendy@aol.com') | (addresses.c.email_address=='jack@yahoo.com')) \
+    >>> print users.c.name.like('j%') & (users.c.id == addresses.c.user_id) &  \
+    ...     (
+    ...       (addresses.c.email_address == 'wendy@aol.com') | \
+    ...       (addresses.c.email_address == 'jack@yahoo.com')
+    ...     ) \
     ...     & ~(users.c.id>5) # doctest: +NORMALIZE_WHITESPACE
     users.name LIKE :name_1 AND users.id = addresses.user_id AND
-    (addresses.email_address = :email_address_1 OR addresses.email_address = :email_address_2)
+    (addresses.email_address = :email_address_1
+        OR addresses.email_address = :email_address_2)
     AND users.id <= :id_1
 
 So with all of this vocabulary, let's select all users who have an email
 address at AOL or MSN, whose name starts with a letter between "m" and "z",
 and we'll also generate a column containing their full name combined with
 their email address. We will add two new constructs to this statement,
-``between()`` and ``label()``. ``between()`` produces a BETWEEN clause, and
-``label()`` is used in a column expression to produce labels using the ``AS``
+:meth:`~.ColumnOperators.between` and :meth:`~.ColumnElement.label`.
+:meth:`~.ColumnOperators.between` produces a BETWEEN clause, and
+:meth:`~.ColumnElement.label` is used in a column expression to produce labels using the ``AS``
 keyword; it's recommended when selecting from expressions that otherwise would
 not have a name:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([(users.c.fullname + ", " + addresses.c.email_address).label('title')],
-    ...        and_(
-    ...            users.c.id==addresses.c.user_id,
-    ...            users.c.name.between('m', 'z'),
-    ...           or_(
-    ...              addresses.c.email_address.like('%@aol.com'),
-    ...              addresses.c.email_address.like('%@msn.com')
+    >>> s = select([(users.c.fullname +
+    ...               ", " + addresses.c.email_address).
+    ...                label('title')]).\
+    ...        where(
+    ...           and_(
+    ...               users.c.id == addresses.c.user_id,
+    ...               users.c.name.between('m', 'z'),
+    ...               or_(
+    ...                  addresses.c.email_address.like('%@aol.com'),
+    ...                  addresses.c.email_address.like('%@msn.com')
+    ...               )
     ...           )
     ...        )
-    ...    )
-    >>> print conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE
+    >>> conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE
     SELECT users.fullname || ? || addresses.email_address AS title
     FROM users, addresses
     WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
 clause, the where clause, and also some other elements which we haven't
 covered yet, which include ORDER BY, GROUP BY, and HAVING.
 
+A shortcut to using :func:`.and_` is to chain together multiple
+:meth:`~.Select.where` clauses.   The above can also be written as:
+
+.. sourcecode:: pycon+sql
+
+    >>> s = select([(users.c.fullname +
+    ...               ", " + addresses.c.email_address).
+    ...                label('title')]).\
+    ...        where(users.c.id == addresses.c.user_id).\
+    ...        where(users.c.name.between('m', 'z')).\
+    ...        where(
+    ...               or_(
+    ...                  addresses.c.email_address.like('%@aol.com'),
+    ...                  addresses.c.email_address.like('%@msn.com')
+    ...               )
+    ...        )
+    >>> conn.execute(s).fetchall() #doctest: +NORMALIZE_WHITESPACE
+    SELECT users.fullname || ? || addresses.email_address AS title
+    FROM users, addresses
+    WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
+    (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
+    (', ', 'm', 'z', '%@aol.com', '%@msn.com')
+    [(u'Wendy Williams, wendy@aol.com',)]
+
+The way that we can build up a :func:`.select` construct through successive
+method calls is called :term:`method chaining`.
+
 .. _sqlexpression_text:
 
 Using Text
 Our last example really became a handful to type. Going from what one
 understands to be a textual SQL expression into a Python construct which
 groups components together in a programmatic style can be hard. That's why
-SQLAlchemy lets you just use strings too. The ``text()`` construct represents
-any textual statement. To use bind parameters with ``text()``, always use the
-named colon format. Such as below, we create a ``text()`` and execute it,
-feeding in the bind parameters to the ``execute()`` method:
+SQLAlchemy lets you just use strings too. The :func:`~.expression.text` construct represents
+any textual statement, in a backend-agnostic way.
+To use bind parameters with :func:`~.expression.text`, always use the
+named colon format. Such as below, we create a :func:`~.expression.text` and execute it,
+feeding in the bind parameters to the :meth:`~.Connection.execute` method:
 
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import text
-    >>> s = text("""SELECT users.fullname || ', ' || addresses.email_address AS title
-    ...            FROM users, addresses
-    ...            WHERE users.id = addresses.user_id AND users.name BETWEEN :x AND :y AND
-    ...            (addresses.email_address LIKE :e1 OR addresses.email_address LIKE :e2)
-    ...        """)
-    {sql}>>> print conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() # doctest:+NORMALIZE_WHITESPACE
+    >>> s = text(
+    ...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
+    ...         "FROM users, addresses "
+    ...         "WHERE users.id = addresses.user_id "
+    ...         "AND users.name BETWEEN :x AND :y "
+    ...         "AND (addresses.email_address LIKE :e1 "
+    ...             "OR addresses.email_address LIKE :e2)")
+    {sql}>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall() # doctest:+NORMALIZE_WHITESPACE
     SELECT users.fullname || ', ' || addresses.email_address AS title
     FROM users, addresses
     WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
     ('m', 'z', '%@aol.com', '%@msn.com')
     {stop}[(u'Wendy Williams, wendy@aol.com',)]
 
-To gain a "hybrid" approach, the `select()` construct accepts strings for most
+To gain a "hybrid" approach, the :func:`.select` construct accepts strings for most
 of its arguments. Below we combine the usage of strings with our constructed
 :func:`.select` object, by using the :func:`.select` object to structure the
 statement, and strings to provide all the content within the structure. For
 this example, SQLAlchemy is not given any :class:`~sqlalchemy.schema.Column`
 or :class:`~sqlalchemy.schema.Table` objects in any of its expressions, so it
-cannot generate a FROM clause. So we also give it the ``from_obj`` keyword
-argument, which is a list of ``ClauseElements`` (or strings) to be placed
-within the FROM clause:
+cannot generate a FROM clause. So we also use the :meth:`~.Select.select_from`
+method, which accepts a :class:`.FromClause` or string expression
+to be placed within the FROM clause:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select(["users.fullname || ', ' || addresses.email_address AS title"],
-    ...        and_(
-    ...            "users.id = addresses.user_id",
-    ...             "users.name BETWEEN 'm' AND 'z'",
-    ...             "(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"
-    ...        ),
-    ...         from_obj=['users', 'addresses']
-    ...    )
-    {sql}>>> print conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE
+    >>> s = select([
+    ...            "users.fullname || ', ' || addresses.email_address AS title"
+    ...          ]).\
+    ...           where(
+    ...              and_(
+    ...                 "users.id = addresses.user_id",
+    ...                 "users.name BETWEEN 'm' AND 'z'",
+    ...                 "(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"
+    ...             )
+    ...           ).select_from('users, addresses')
+    {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE
     SELECT users.fullname || ', ' || addresses.email_address AS title
     FROM users, addresses
-    WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
+    WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z'
+    AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
     ('%@aol.com', '%@msn.com')
     {stop}[(u'Wendy Williams, wendy@aol.com',)]
 
 datatypes in use; for example, if our bind parameters required UTF-8 encoding
 before going in, or conversion from a Python ``datetime`` into a string (as is
 required with SQLite), we would have to add extra information to our
-``text()`` construct. Similar issues arise on the result set side, where
+:func:`~.expression.text` construct. Similar issues arise on the result set side, where
 SQLAlchemy also performs type-specific data conversion in some cases; still
-more information can be added to ``text()`` to work around this. But what we
+more information can be added to :func:`~.expression.text` to work around this. But what we
 really lose from our statement is the ability to manipulate it, transform it,
 and analyze it. These features are critical when using the ORM, which makes
 heavy usage of relational transformations. To show off what we mean, we'll
 
     >>> a1 = addresses.alias()
     >>> a2 = addresses.alias()
-    >>> s = select([users], and_(
-    ...        users.c.id==a1.c.user_id,
-    ...        users.c.id==a2.c.user_id,
-    ...        a1.c.email_address=='jack@msn.com',
-    ...        a2.c.email_address=='jack@yahoo.com'
-    ...   ))
-    {sql}>>> print conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    >>> s = select([users]).\
+    ...        where(and_(
+    ...            users.c.id == a1.c.user_id,
+    ...            users.c.id == a2.c.user_id,
+    ...            a1.c.email_address == 'jack@msn.com',
+    ...            a2.c.email_address == 'jack@yahoo.com'
+    ...        ))
+    {sql}>>> conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, users.name, users.fullname
     FROM users, addresses AS addresses_1, addresses AS addresses_2
-    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?
+    WHERE users.id = addresses_1.user_id
+        AND users.id = addresses_2.user_id
+        AND addresses_1.email_address = ?
+        AND addresses_2.email_address = ?
     ('jack@msn.com', 'jack@yahoo.com')
     {stop}[(1, u'jack', u'Jack Jones')]
 
 .. sourcecode:: pycon+sql
 
     >>> a1 = s.correlate(None).alias()
-    >>> s = select([users.c.name], users.c.id==a1.c.id)
-    {sql}>>> print conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    >>> s = select([users.c.name]).where(users.c.id == a1.c.id)
+    {sql}>>> conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
     SELECT users.name
-    FROM users, (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
-    FROM users, addresses AS addresses_1, addresses AS addresses_2
-    WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id AND addresses_1.email_address = ? AND addresses_2.email_address = ?) AS anon_1
+    FROM users,
+        (SELECT users.id AS id, users.name AS name, users.fullname AS fullname
+            FROM users, addresses AS addresses_1, addresses AS addresses_2
+            WHERE users.id = addresses_1.user_id AND users.id = addresses_2.user_id
+            AND addresses_1.email_address = ?
+            AND addresses_2.email_address = ?) AS anon_1
     WHERE users.id = anon_1.id
     ('jack@msn.com', 'jack@yahoo.com')
     {stop}[(u'jack',)]
 cornerstone of the SELECT is the JOIN expression. We've already been doing
 joins in our examples, by just placing two tables in either the columns clause
 or the where clause of the :func:`.select` construct. But if we want to make a
-real "JOIN" or "OUTERJOIN" construct, we use the ``join()`` and
-``outerjoin()`` methods, most commonly accessed from the left table in the
+real "JOIN" or "OUTERJOIN" construct, we use the :meth:`~.FromClause.join` and
+:meth:`~.FromClause.outerjoin` methods, most commonly accessed from the left table in the
 join:
 
 .. sourcecode:: pycon+sql
 
 .. sourcecode:: pycon+sql
 
-    >>> print users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
-    users JOIN addresses ON addresses.email_address LIKE users.name || :name_1
+    >>> print users.join(addresses,
+    ...                 addresses.c.email_address.like(users.c.name + '%')
+    ...             )
+    users JOIN addresses ON addresses.email_address LIKE (users.name || :name_1)
 
 When we create a :func:`.select` construct, SQLAlchemy looks around at the
 tables we've mentioned and then places them in the FROM clause of the
 statement. When we use JOINs however, we know what FROM clause we want, so
-here we make usage of the ``from_obj`` keyword argument:
+here we make use of the :meth:`~.Select.select_from` method:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users.c.fullname], from_obj=[
-    ...    users.join(addresses, addresses.c.email_address.like(users.c.name + '%'))
-    ...    ])
-    {sql}>>> print conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    >>> s = select([users.c.fullname]).select_from(
+    ...    users.join(addresses,
+    ...             addresses.c.email_address.like(users.c.name + '%'))
+    ...    )
+    {sql}>>> conn.execute(s).fetchall()  # doctest: +NORMALIZE_WHITESPACE
     SELECT users.fullname
-    FROM users JOIN addresses ON addresses.email_address LIKE users.name || ?
+    FROM users JOIN addresses ON addresses.email_address LIKE (users.name || ?)
     ('%',)
     {stop}[(u'Jack Jones',), (u'Jack Jones',), (u'Wendy Williams',)]
 
-The ``outerjoin()`` function just creates ``LEFT OUTER JOIN`` constructs. It's
-used just like ``join()``:
+The :meth:`~.FromClause.outerjoin` method creates ``LEFT OUTER JOIN`` constructs,
+and is used in the same way as :meth:`~.FromClause.join`:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users.c.fullname], from_obj=[users.outerjoin(addresses)])
+    >>> s = select([users.c.fullname]).select_from(users.outerjoin(addresses))
     >>> print s  # doctest: +NORMALIZE_WHITESPACE
     SELECT users.fullname
-    FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
+        FROM users
+        LEFT OUTER JOIN addresses ON users.id = addresses.user_id
 
 That's the output ``outerjoin()`` produces, unless, of course, you're stuck in
 a gig using Oracle prior to version 9, and you've set up your engine (which
 If you don't know what that SQL means, don't worry ! The secret tribe of
 Oracle DBAs don't want their black magic being found out ;).
 
-Intro to Generative Selects
-================================================
-
-We've now gained the ability to construct very sophisticated statements. We
-can use all kinds of operators, table constructs, text, joins, and aliases.
-The point of all of this, as mentioned earlier, is not that it's an "easier"
-or "better" way to write SQL than just writing a SQL statement yourself; the
-point is that it's better for writing *programmatically generated* SQL which
-can be morphed and adapted as needed in automated scenarios.
-
-To support this, the :func:`.select` construct we've been working with
-supports piecemeal construction, in addition to the "all at once" method we've
-been doing. Suppose you're writing a search function, which receives criterion
-and then must construct a select from it. To accomplish this, upon each
-criterion encountered, you apply "generative" criterion to an existing
-:func:`.select` construct with new elements, one at a time. We start with a
-basic :func:`.select` constructed with the shortcut method available on the
-``users`` table:
-
-.. sourcecode:: pycon+sql
-
-    >>> query = users.select()
-    >>> print query  # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.id, users.name, users.fullname
-    FROM users
-
-We encounter search criterion of "name='jack'". So we apply WHERE criterion
-stating such:
-
-.. sourcecode:: pycon+sql
-
-    >>> query = query.where(users.c.name=='jack')
-
-Next, we encounter that they'd like the results in descending order by full
-name. We apply ORDER BY, using an extra modifier ``desc``:
-
-.. sourcecode:: pycon+sql
-
-    >>> query = query.order_by(users.c.fullname.desc())
-
-We also come across that they'd like only users who have an address at MSN. A
-quick way to tack this on is by using an EXISTS clause, which we correlate to
-the ``users`` table in the enclosing SELECT:
-
-.. sourcecode:: pycon+sql
-
-    >>> from sqlalchemy.sql import exists
-    >>> query = query.where(
-    ...    exists([addresses.c.id],
-    ...        and_(addresses.c.user_id==users.c.id, addresses.c.email_address.like('%@msn.com'))
-    ...    ).correlate(users))
-
-And finally, the application also wants to see the listing of email addresses
-at once; so to save queries, we outerjoin the ``addresses`` table (using an
-outer join so that users with no addresses come back as well; since we're
-programmatic, we might not have kept track that we used an EXISTS clause
-against the ``addresses`` table too...). Additionally, since the ``users`` and
-``addresses`` table both have a column named ``id``, let's isolate their names
-from each other in the COLUMNS clause by using labels:
-
-.. sourcecode:: pycon+sql
-
-    >>> query = query.column(addresses).select_from(users.outerjoin(addresses)).apply_labels()
-
-Let's bake for .0001 seconds and see what rises:
-
-.. sourcecode:: pycon+sql
-
-    >>> conn.execute(query).fetchall()  # doctest: +NORMALIZE_WHITESPACE
-    {opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses.id AS addresses_id, addresses.user_id AS addresses_user_id, addresses.email_address AS addresses_email_address
-    FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
-    WHERE users.name = ? AND (EXISTS (SELECT addresses.id
-    FROM addresses
-    WHERE addresses.user_id = users.id AND addresses.email_address LIKE ?)) ORDER BY users.fullname DESC
-    ('jack', '%@msn.com')
-    {stop}[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]
-
-The generative approach is about starting small, adding one thing at a time,
-to arrive with a full statement.
-
-Transforming a Statement
-------------------------
-
-We've seen how methods like :meth:`.Select.where` and :meth:`.SelectBase.order_by` are
-part of the so-called *Generative* family of methods on the :func:`.select` construct,
-where one :func:`.select` copies itself to return a new one with modifications.
-SQL constructs also support another form of generative behavior which is
-the *transformation*.   This is an advanced technique that most core applications
-won't use directly; however, it is a system which the ORM relies on heavily,
-and can be useful for any system that deals with generalized behavior of Core SQL
-constructs.
-
-Using a transformation we can take our ``users``/``addresses`` query and replace
-all occurrences of ``addresses`` with an alias of itself.   That is, anywhere
-that ``addresses`` is referred to in the original query, the new query will
-refer to ``addresses_1``, which is selected as ``addresses AS addresses_1``.
-The :meth:`.FromClause.replace_selectable` method can achieve this:
-
-.. sourcecode:: pycon+sql
-
-    >>> a1 = addresses.alias()
-    >>> query = query.replace_selectable(addresses, a1)
-    >>> print query  # doctest: +NORMALIZE_WHITESPACE
-    {opensql}SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
-    FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
-    WHERE users.name = :name_1 AND (EXISTS (SELECT addresses_1.id
-    FROM addresses AS addresses_1
-    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE :email_address_1)) ORDER BY users.fullname DESC
-
-For a query such as the above, we can access the columns referred
-to by the ``a1`` alias in a result set using the :class:`.Column` objects
-present directly on ``a1``:
-
-.. sourcecode:: pycon+sql
-
-    {sql}>>> for row in conn.execute(query):
-    ...     print "Name:", row[users.c.name], "; Email Address", row[a1.c.email_address]  # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, addresses_1.id AS addresses_1_id, addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address AS addresses_1_email_address
-    FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
-    WHERE users.name = ? AND (EXISTS (SELECT addresses_1.id
-    FROM addresses AS addresses_1
-    WHERE addresses_1.user_id = users.id AND addresses_1.email_address LIKE ?)) ORDER BY users.fullname DESC
-    ('jack', '%@msn.com')
-    {stop}Name: jack ; Email Address jack@yahoo.com
-    Name: jack ; Email Address jack@msn.com
-
 Everything Else
 ================
 
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import bindparam
-    >>> s = users.select(users.c.name==bindparam('username'))
+    >>> s = users.select(users.c.name == bindparam('username'))
     {sql}>>> conn.execute(s, username='wendy').fetchall() # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, users.name, users.fullname
     FROM users
     {sql}>>> conn.execute(s, username='wendy').fetchall() # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, users.name, users.fullname
     FROM users
-    WHERE users.name LIKE ? || '%'
+    WHERE users.name LIKE (? || '%')
     ('wendy',)
     {stop}[(2, u'wendy', u'Wendy Williams')]
 
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users, addresses],
-    ...    users.c.name.like(bindparam('name', type_=String) + text("'%'")) |
-    ...    addresses.c.email_address.like(bindparam('name', type_=String) + text("'@%'")),
-    ...    from_obj=[users.outerjoin(addresses)])
+    >>> s = select([users, addresses]).\
+    ...     where(
+    ...        or_(
+    ...          users.c.name.like(
+    ...                 bindparam('name', type_=String) + text("'%'")),
+    ...          addresses.c.email_address.like(
+    ...                 bindparam('name', type_=String) + text("'@%'"))
+    ...        )
+    ...     ).\
+    ...     select_from(users.outerjoin(addresses)).\
+    ...     order_by(addresses.c.id)
     {sql}>>> conn.execute(s, name='jack').fetchall() # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address
+    SELECT users.id, users.name, users.fullname, addresses.id,
+        addresses.user_id, addresses.email_address
     FROM users LEFT OUTER JOIN addresses ON users.id = addresses.user_id
-    WHERE users.name LIKE ? || '%' OR addresses.email_address LIKE ? || '@%'
+    WHERE users.name LIKE (? || '%') OR addresses.email_address LIKE (? || '@%')
+    ORDER BY addresses.id
     ('jack', 'jack')
     {stop}[(1, u'jack', u'Jack Jones', 1, 1, u'jack@yahoo.com'), (1, u'jack', u'Jack Jones', 2, 1, u'jack@msn.com')]
 
 
 .. sourcecode:: pycon+sql
 
-    >>> print conn.execute(
-    ...     select([func.max(addresses.c.email_address, type_=String).label('maxemail')])
-    ... ).scalar() # doctest: +NORMALIZE_WHITESPACE
+    >>> conn.execute(
+    ...     select([
+    ...            func.max(addresses.c.email_address, type_=String).
+    ...                label('maxemail')
+    ...           ])
+    ...     ).scalar() # doctest: +NORMALIZE_WHITESPACE
     {opensql}SELECT max(addresses.email_address) AS maxemail
     FROM addresses
     ()
-    {stop}www@www.org
+    {stop}u'www@www.org'
 
 Databases such as PostgreSQL and Oracle which support functions that return
 whole result sets can be assembled into selectable units, which can be used in
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import column
-    >>> calculate = select([column('q'), column('z'), column('r')],
-    ...     from_obj=[func.calculate(bindparam('x'), bindparam('y'))])
-
-    >>> print select([users], users.c.id > calculate.c.z) # doctest: +NORMALIZE_WHITESPACE
+    >>> calculate = select([column('q'), column('z'), column('r')]).\
+    ...        select_from(
+    ...             func.calculate(
+    ...                    bindparam('x'),
+    ...                    bindparam('y')
+    ...                )
+    ...             )
+    >>> calc = calculate.alias()
+    >>> print select([users]).where(users.c.id > calc.c.z) # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, users.name, users.fullname
     FROM users, (SELECT q, z, r
-    FROM calculate(:x, :y))
-    WHERE users.id > z
+    FROM calculate(:x, :y)) AS anon_1
+    WHERE users.id > anon_1.z
 
 If we wanted to use our ``calculate`` statement twice with different bind
 parameters, the :func:`~sqlalchemy.sql.expression.ClauseElement.unique_params`
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users], users.c.id.between(
-    ...    calculate.alias('c1').unique_params(x=17, y=45).c.z,
-    ...    calculate.alias('c2').unique_params(x=5, y=12).c.z))
-
+    >>> calc1 = calculate.alias('c1').unique_params(x=17, y=45)
+    >>> calc2 = calculate.alias('c2').unique_params(x=5, y=12)
+    >>> s = select([users]).\
+    ...         where(users.c.id.between(calc1.c.z, calc2.c.z))
     >>> print s # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, users.name, users.fullname
-    FROM users, (SELECT q, z, r
-    FROM calculate(:x_1, :y_1)) AS c1, (SELECT q, z, r
-    FROM calculate(:x_2, :y_2)) AS c2
+    FROM users,
+        (SELECT q, z, r FROM calculate(:x_1, :y_1)) AS c1,
+        (SELECT q, z, r FROM calculate(:x_2, :y_2)) AS c2
     WHERE users.id BETWEEN c1.z AND c2.z
 
     >>> s.compile().params
     {u'x_2': 5, u'y_2': 12, u'y_1': 45, u'x_1': 17}
 
-See also :data:`~.expression.func`.
 
 Window Functions
 -----------------
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([users.c.id, func.row_number().over(order_by=users.c.name)])
+    >>> s = select([
+    ...         users.c.id,
+    ...         func.row_number().over(order_by=users.c.name)
+    ...     ])
     >>> print s # doctest: +NORMALIZE_WHITESPACE
     SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1
     FROM users
 -------------------------------
 
 Unions come in two flavors, UNION and UNION ALL, which are available via
-module level functions:
+module level functions :func:`~.expression.union` and
+:func:`~.expression.union_all`:
 
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import union
     >>> u = union(
-    ...     addresses.select(addresses.c.email_address=='foo@bar.com'),
-    ...    addresses.select(addresses.c.email_address.like('%@yahoo.com')),
+    ...     addresses.select().
+    ...             where(addresses.c.email_address == 'foo@bar.com'),
+    ...    addresses.select().
+    ...             where(addresses.c.email_address.like('%@yahoo.com')),
     ... ).order_by(addresses.c.email_address)
 
-    {sql}>>> print conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
+    {sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
     SELECT addresses.id, addresses.user_id, addresses.email_address
     FROM addresses
-    WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address
+    WHERE addresses.email_address = ?
+    UNION
+    SELECT addresses.id, addresses.user_id, addresses.email_address
     FROM addresses
     WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address
     ('foo@bar.com', '%@yahoo.com')
     {stop}[(1, 1, u'jack@yahoo.com')]
 
-Also available, though not supported on all databases, are ``intersect()``,
-``intersect_all()``, ``except_()``, and ``except_all()``:
+Also available, though not supported on all databases, are
+:func:`~.expression.intersect`,
+:func:`~.expression.intersect_all`,
+:func:`~.expression.except_`, and :func:`~.expression.except_all`:
 
 .. sourcecode:: pycon+sql
 
     >>> from sqlalchemy.sql import except_
     >>> u = except_(
-    ...    addresses.select(addresses.c.email_address.like('%@%.com')),
-    ...    addresses.select(addresses.c.email_address.like('%@msn.com'))
+    ...    addresses.select().
+    ...             where(addresses.c.email_address.like('%@%.com')),
+    ...    addresses.select().
+    ...             where(addresses.c.email_address.like('%@msn.com'))
     ... )
 
-    {sql}>>> print conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
+    {sql}>>> conn.execute(u).fetchall() # doctest: +NORMALIZE_WHITESPACE
     SELECT addresses.id, addresses.user_id, addresses.email_address
     FROM addresses
-    WHERE addresses.email_address LIKE ? EXCEPT SELECT addresses.id, addresses.user_id, addresses.email_address
+    WHERE addresses.email_address LIKE ?
+    EXCEPT
+    SELECT addresses.id, addresses.user_id, addresses.email_address
     FROM addresses
     WHERE addresses.email_address LIKE ?
     ('%@%.com', '%@msn.com')
 
     >>> u = except_(
     ...    union(
-    ...         addresses.select(addresses.c.email_address.like('%@yahoo.com')),
-    ...         addresses.select(addresses.c.email_address.like('%@msn.com'))
+    ...         addresses.select().
+    ...             where(addresses.c.email_address.like('%@yahoo.com')),
+    ...         addresses.select().
+    ...             where(addresses.c.email_address.like('%@msn.com'))
     ...     ).alias().select(),   # apply subquery here
     ...    addresses.select(addresses.c.email_address.like('%@msn.com'))
     ... )
-    {sql}>>> print conn.execute(u).fetchall()   # doctest: +NORMALIZE_WHITESPACE
+    {sql}>>> conn.execute(u).fetchall()   # doctest: +NORMALIZE_WHITESPACE
     SELECT anon_1.id, anon_1.user_id, anon_1.email_address
     FROM (SELECT addresses.id AS id, addresses.user_id AS user_id,
-    addresses.email_address AS email_address FROM addresses
-    WHERE addresses.email_address LIKE ? UNION SELECT addresses.id AS id,
-    addresses.user_id AS user_id, addresses.email_address AS email_address
-    FROM addresses WHERE addresses.email_address LIKE ?) AS anon_1 EXCEPT
+        addresses.email_address AS email_address
+        FROM addresses
+        WHERE addresses.email_address LIKE ?
+        UNION
+        SELECT addresses.id AS id,
+            addresses.user_id AS user_id,
+            addresses.email_address AS email_address
+        FROM addresses
+        WHERE addresses.email_address LIKE ?) AS anon_1
+    EXCEPT
     SELECT addresses.id, addresses.user_id, addresses.email_address
     FROM addresses
     WHERE addresses.email_address LIKE ?
     ('%@yahoo.com', '%@msn.com', '%@msn.com')
     {stop}[(1, 1, u'jack@yahoo.com')]
 
+.. _scalar_selects:
 
 Scalar Selects
 --------------
 
-To embed a SELECT in a column expression, use
-:func:`~sqlalchemy.sql.expression.SelectBase.as_scalar`:
+A scalar select is a SELECT that returns exactly one row and one
+column.  It can then be used as a column expression.  A scalar select
+is often a :term:`correlated subquery`, which relies upon the enclosing
+SELECT statement in order to acquire at least one of its FROM clauses.
+
+The :func:`.select` construct can be modified to act as a
+column expression by calling either the :meth:`~.SelectBase.as_scalar`
+or :meth:`~.SelectBase.label` method:
 
 .. sourcecode:: pycon+sql
 
-    {sql}>>> print conn.execute(select([   # doctest: +NORMALIZE_WHITESPACE
-    ...       users.c.name,
-    ...       select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).as_scalar()
-    ...    ])).fetchall()
-    SELECT users.name, (SELECT count(addresses.id) AS count_1
+    >>> stmt = select([func.count(addresses.c.id)]).\
+    ...             where(users.c.id == addresses.c.user_id).\
+    ...             as_scalar()
+
+The above construct is now a :class:`~.expression.ScalarSelect` object,
+and is no longer part of the :class:`~.expression.FromClause` hierarchy;
+it instead is within the :class:`~.expression.ColumnElement` family of
+expression constructs.  We can place this construct the same as any
+other column within another :func:`.select`:
+
+.. sourcecode:: pycon+sql
+
+    >>> conn.execute(select([users.c.name, stmt])).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1
     FROM addresses
     WHERE users.id = addresses.user_id) AS anon_1
     FROM users
     ()
-    {stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]
+    {stop}[(u'jack', 2), (u'wendy', 2)]
 
-Alternatively, applying a ``label()`` to a select evaluates it as a scalar as
-well:
+To apply a non-anonymous column name to our scalar select, we create
+it using :meth:`.SelectBase.label` instead:
 
 .. sourcecode:: pycon+sql
 
-    {sql}>>> print conn.execute(select([    # doctest: +NORMALIZE_WHITESPACE
-    ...       users.c.name,
-    ...       select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).label('address_count')
-    ...    ])).fetchall()
-    SELECT users.name, (SELECT count(addresses.id) AS count_1
+    >>> stmt = select([func.count(addresses.c.id)]).\
+    ...             where(users.c.id == addresses.c.user_id).\
+    ...             label("address_count")
+    >>> conn.execute(select([users.c.name, stmt])).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, (SELECT count(addresses.id) AS count_1
     FROM addresses
     WHERE users.id = addresses.user_id) AS address_count
     FROM users
     ()
-    {stop}[(u'jack', 2), (u'wendy', 2), (u'fred', 0), (u'mary', 0)]
+    {stop}[(u'jack', 2), (u'wendy', 2)]
 
 .. _correlated_subqueries:
 
 Correlated Subqueries
 ---------------------
 
-Notice in the examples on "scalar selects", the FROM clause of each embedded
+Notice in the examples on :ref:`scalar_selects`, the FROM clause of each embedded
 select did not contain the ``users`` table in its FROM clause. This is because
-SQLAlchemy automatically attempts to correlate embedded FROM objects to that
-of an enclosing query. To disable this, or to specify explicit FROM clauses to
-be correlated, use ``correlate()``::
+SQLAlchemy automatically :term:`correlates` embedded FROM objects to that
+of an enclosing query, if present, and if the inner SELECT statement would
+still have at least one FROM clause of its own.  For example:
 
-    >>> s = select([users.c.name], users.c.id==select([users.c.id]).correlate(None))
-    >>> print s # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.name
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([addresses.c.user_id]).\
+    ...             where(addresses.c.user_id == users.c.id).\
+    ...             where(addresses.c.email_address == 'jack@yahoo.com')
+    >>> enclosing_stmt = select([users.c.name]).where(users.c.id == stmt)
+    >>> conn.execute(enclosing_stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name
     FROM users
-    WHERE users.id = (SELECT users.id
-    FROM users)
+    WHERE users.id = (SELECT addresses.user_id
+        FROM addresses
+        WHERE addresses.user_id = users.id
+        AND addresses.email_address = ?)
+    ('jack@yahoo.com',)
+    {stop}[(u'jack',)]
 
-    >>> s = select([users.c.name, addresses.c.email_address], users.c.id==
-    ...        select([users.c.id], users.c.id==addresses.c.user_id).correlate(addresses)
-    ...    )
-    >>> print s # doctest: +NORMALIZE_WHITESPACE
-    SELECT users.name, addresses.email_address
-    FROM users, addresses
-    WHERE users.id = (SELECT users.id
-    FROM users
-    WHERE users.id = addresses.user_id)
+Auto-correlation will usually do what's expected, however it can also be controlled.
+For example, if we wanted a statement to correlate only to the ``addresses`` table
+but not the ``users`` table, even if both were present in the enclosing SELECT,
+we use the :meth:`~.Select.correlate` method to specify those FROM clauses that
+may be correlated:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.id]).\
+    ...             where(users.c.id == addresses.c.user_id).\
+    ...             where(users.c.name == 'jack').\
+    ...             correlate(addresses)
+    >>> enclosing_stmt = select(
+    ...         [users.c.name, addresses.c.email_address]).\
+    ...     select_from(users.join(addresses)).\
+    ...     where(users.c.id == stmt)
+    >>> conn.execute(enclosing_stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, addresses.email_address
+     FROM users JOIN addresses ON users.id = addresses.user_id
+     WHERE users.id = (SELECT users.id
+     FROM users
+     WHERE users.id = addresses.user_id AND users.name = ?)
+     ('jack',)
+     {stop}[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]
+
+To entirely disable a statement from correlating, we can pass ``None``
+as the argument:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.id]).\
+    ...             where(users.c.name == 'wendy').\
+    ...             correlate(None)
+    >>> enclosing_stmt = select([users.c.name]).\
+    ...     where(users.c.id == stmt)
+    >>> conn.execute(enclosing_stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name
+     FROM users
+     WHERE users.id = (SELECT users.id
+      FROM users
+      WHERE users.name = ?)
+    ('wendy',)
+    {stop}[(u'wendy',)]
 
 Ordering, Grouping, Limiting, Offset...ing...
 ---------------------------------------------
 
-
-The :func:`.select` function can take keyword arguments ``order_by``,
-``group_by`` (as well as ``having``), ``limit``, and ``offset``. There's also
-``distinct=True``. These are all also available as generative functions.
-``order_by()`` expressions can use the modifiers ``asc()`` or ``desc()`` to
-indicate ascending or descending.
+Ordering is done by passing column expressions to the
+:meth:`~.SelectBase.order_by` method:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = select([addresses.c.user_id, func.count(addresses.c.id)]).\
-    ...     group_by(addresses.c.user_id).having(func.count(addresses.c.id)>1)
-    {sql}>>> print conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
-    SELECT addresses.user_id, count(addresses.id) AS count_1
-    FROM addresses GROUP BY addresses.user_id
-    HAVING count(addresses.id) > ?
-    (1,)
-    {stop}[(1, 2), (2, 2)]
+    >>> stmt = select([users.c.name]).order_by(users.c.name)
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name
+    FROM users ORDER BY users.name
+    ()
+    {stop}[(u'jack',), (u'wendy',)]
 
-    >>> s = select([addresses.c.email_address, addresses.c.id]).distinct().\
-    ...     order_by(addresses.c.email_address.desc(), addresses.c.id)
-    {sql}>>> conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
-    SELECT DISTINCT addresses.email_address, addresses.id
-    FROM addresses ORDER BY addresses.email_address DESC, addresses.id
+Ascending or descending can be controlled using the :meth:`~.ColumnElement.asc`
+and :meth:`~.ColumnElement.desc` modifiers:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.name]).order_by(users.c.name.desc())
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name
+    FROM users ORDER BY users.name DESC
     ()
-    {stop}[(u'www@www.org', 3), (u'wendy@aol.com', 4), (u'jack@yahoo.com', 1), (u'jack@msn.com', 2)]
+    {stop}[(u'wendy',), (u'jack',)]
 
-    >>> s = select([addresses]).offset(1).limit(1)
-    {sql}>>> print conn.execute(s).fetchall() # doctest: +NORMALIZE_WHITESPACE
-    SELECT addresses.id, addresses.user_id, addresses.email_address
-    FROM addresses
-    LIMIT 1 OFFSET 1
+Grouping refers to the GROUP BY clause, and is usually used in conjunction
+with aggregate functions to establish groups of rows to be aggregated.
+This is provided via the :meth:`~.SelectBase.group_by` method:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.name, func.count(addresses.c.id)]).\
+    ...             select_from(users.join(addresses)).\
+    ...             group_by(users.c.name)
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, count(addresses.id) AS count_1
+    FROM users JOIN addresses
+        ON users.id = addresses.user_id
+    GROUP BY users.name
     ()
-    {stop}[(2, 1, u'jack@msn.com')]
+    {stop}[(u'jack', 2), (u'wendy', 2)]
+
+HAVING can be used to filter results on an aggregate value, after GROUP BY has
+been applied.  It's available here via the :meth:`~.Select.having`
+method:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.name, func.count(addresses.c.id)]).\
+    ...             select_from(users.join(addresses)).\
+    ...             group_by(users.c.name).\
+    ...             having(func.length(users.c.name) > 4)
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, count(addresses.id) AS count_1
+    FROM users JOIN addresses
+        ON users.id = addresses.user_id
+    GROUP BY users.name
+    HAVING length(users.name) > ?
+    (4,)
+    {stop}[(u'wendy', 2)]
+
+A common system of dealing with duplicates in composed SELECT statments
+is the DISTINCT modifier.  A simple DISTINCT clause can be added using the
+:meth:`.Select.distinct` method:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.name]).\
+    ...             where(addresses.c.email_address.
+    ...                    contains(users.c.name)).\
+    ...             distinct()
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT DISTINCT users.name
+    FROM users, addresses
+    WHERE addresses.email_address LIKE '%%' || users.name || '%%'
+    ()
+    {stop}[(u'jack',), (u'wendy',)]
+
+Most database backends support a system of limiting how many rows
+are returned, and the majority also feature a means of starting to return
+rows after a given "offset".   While common backends like Postgresql,
+MySQL and SQLite support LIMIT and OFFSET keywords, other backends
+need to refer to more esoteric features such as "window functions"
+and row ids to achieve the same effect.  The :meth:`~.Select.limit`
+and :meth:`~.Select.offset` methods provide an easy abstraction
+into the current backend's methodology:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.name, addresses.c.email_address]).\
+    ...             select_from(users.join(addresses)).\
+    ...             limit(1).offset(1)
+    >>> conn.execute(stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, addresses.email_address
+    FROM users JOIN addresses ON users.id = addresses.user_id
+     LIMIT ? OFFSET ?
+    (1, 1)
+    {stop}[(u'jack', u'jack@msn.com')]
+
 
 .. _inserts_and_updates:
 
-Inserts and Updates
-===================
+Inserts, Updates and Deletes
+============================
 
-Finally, we're back to INSERT for some more detail. The
-:func:`~sqlalchemy.sql.expression.insert` construct provides a :meth:`~.ValuesBase.values`
-method which can be used to send any value or clause expression to the VALUES
-portion of the INSERT::
+We've seen :meth:`~.TableClause.insert` demonstrated
+earlier in this tutorial.   Where :meth:`~.TableClause.insert`
+prodces INSERT, the :meth:`~.TableClause.update`
+method produces UPDATE.  Both of these constructs feature
+a method called :meth:`~.ValuesBase.values` which specifies
+the VALUES or SET clause of the statement.
 
-    # insert from a function
-    users.insert().values(id=12, name=func.upper('jack'))
+The :meth:`~.ValuesBase.values` method accommodates any column expression
+as a value:
 
-    # insert from a concatenation expression
-    addresses.insert().values(email_address = name + '@' + host)
+.. sourcecode:: pycon+sql
 
-``values()`` can be mixed with per-execution values::
+    >>> stmt = users.update().\
+    ...             values(fullname="Fullname: " + users.c.name)
+    >>> conn.execute(stmt) #doctest: +ELLIPSIS
+    {opensql}UPDATE users SET fullname=(? || users.name)
+    ('Fullname: ',)
+    COMMIT
+    {stop}<sqlalchemy.engine.result.ResultProxy object at 0x...>
 
-    conn.execute(
-        users.insert().values(name=func.upper('jack')),
-        fullname='Jack Jones'
-    )
+When using :meth:`~.TableClause.insert` or :meth:`~.TableClause.update`
+in an "execute many" context, we may also want to specify named
+bound parameters which we can refer to in the argument list.
+The two constructs will automatically generate bound placeholders
+for any column names passed in the dictionaries sent to
+:meth:`~.Connection.execute` at execution time.  However, if we
+wish to use explicitly targeted named parameters with composed expressions,
+we need to use the :func:`~.expression.bindparam` construct.
+When using :func:`~.expression.bindparam` with
+:meth:`~.TableClause.insert` or :meth:`~.TableClause.update`,
+the names of the table's columns themselves are reserved for the
+"automatic" generation of bind names.  We can combine the usage