tutorial 2.0 WIPreview/mike_bayer/tutorial20

Add SelectBase.exists() method as it seems strange this is
not available already.  The Exists construct itself does
not provide full SELECT-building capabilities so it makes
sense this should be used more like a scalar_subquery.

Make sure stream_results is getting set up when yield_per
is used, for 2.0 style statements as well.  this was
hardcoded inside of Query.yield_per() and is now moved
to take place within QueryContext.

Change-Id: Icafcd4fd9b708772343d56edf40995c9e8f835d6

14 files changed, 395 insertions, 182 deletions

diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index a7f71f5e5..fddbc501a 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -1139,7 +1139,8 @@ class CreateEnginePlugin(object):
             :class:`_engine.URL` object should impliement the
             :meth:`_engine.CreateEnginePlugin.update_url` method.
 
-        :param kwargs: The keyword arguments passed to :func:`.create_engine`.
+        :param kwargs: The keyword arguments passed to
+         :func:`_sa.create_engine`.
 
         """
         self.url = url
diff --git a/lib/sqlalchemy/engine/result.py b/lib/sqlalchemy/engine/result.py
index cb452ac73..73b07e540 100644
--- a/lib/sqlalchemy/engine/result.py
+++ b/lib/sqlalchemy/engine/result.py
@@ -710,6 +710,10 @@ class Result(_WithKeys, ResultInternal):
        :class:`.ResultProxy` interface.   When using the ORM, a higher level
        object called :class:`.ChunkedIteratorResult` is normally used.
 
+    .. seealso::
+
+        :ref:`tutorial_fetching_rows` - in the :doc:`/tutorial/index`
+
     """
 
     _process_row = Row
diff --git a/lib/sqlalchemy/engine/row.py b/lib/sqlalchemy/engine/row.py
index 288f08e29..60954fcec 100644
--- a/lib/sqlalchemy/engine/row.py
+++ b/lib/sqlalchemy/engine/row.py
@@ -500,10 +500,14 @@ class RowMapping(BaseRow, collections_abc.Mapping):
     """A ``Mapping`` that maps column names and objects to :class:`.Row` values.
 
     The :class:`.RowMapping` is available from a :class:`.Row` via the
-    :attr:`.Row._mapping` attribute and supplies Python mapping (i.e.
-    dictionary) access to the  contents of the row.   This includes support
-    for testing of containment of specific keys (string column names or
-    objects), as well as iteration of keys, values, and items::
+    :attr:`.Row._mapping` attribute, as well as from the iterable interface
+    provided by the :class:`.MappingResult` object returned by the
+    :meth:`_engine.Result.mappings` method.
+
+    :class:`.RowMapping` supplies Python mapping (i.e. dictionary) access to
+    the  contents of the row.   This includes support for testing of
+    containment of specific keys (string column names or objects), as well
+    as iteration of keys, values, and items::
 
         for row in result:
             if 'a' in row._mapping:
diff --git a/lib/sqlalchemy/orm/context.py b/lib/sqlalchemy/orm/context.py
index 5e9cf9cce..12759f018 100644
--- a/lib/sqlalchemy/orm/context.py
+++ b/lib/sqlalchemy/orm/context.py
@@ -216,6 +216,16 @@ class ORMCompileState(CompileState):
             statement._execution_options,
         )
 
+        if "yield_per" in execution_options or load_options._yield_per:
+            execution_options = execution_options.union(
+                {
+                    "stream_results": True,
+                    "max_row_buffer": execution_options.get(
+                        "yield_per", load_options._yield_per
+                    ),
+                }
+            )
+
         bind_arguments["clause"] = statement
 
         # new in 1.4 - the coercions system is leveraged to allow the
diff --git a/lib/sqlalchemy/orm/interfaces.py b/lib/sqlalchemy/orm/interfaces.py
index b1ff1a049..64f561cbd 100644
--- a/lib/sqlalchemy/orm/interfaces.py
+++ b/lib/sqlalchemy/orm/interfaces.py
@@ -499,6 +499,8 @@ class PropComparator(operators.ColumnOperators):
 
         .. seealso::
 
+            :ref:`orm_queryguide_join_on_augmented`
+
             :ref:`loader_option_criteria`
 
             :func:`.with_loader_criteria`
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 277dda6fb..f79c19849 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -770,66 +770,18 @@ class Query(
         (e.g. approximately 1000) is used, even with DBAPIs that buffer
         rows (which are most).
 
-        The :meth:`_query.Query.yield_per` method **is not compatible
-        subqueryload eager loading or joinedload eager loading when
-        using collections**.  It is potentially compatible with "select in"
-        eager loading, **provided the database driver supports multiple,
-        independent cursors** (pysqlite and psycopg2 are known to work,
-        MySQL and SQL Server ODBC drivers do not).
-
-        Therefore in some cases, it may be helpful to disable
-        eager loads, either unconditionally with
-        :meth:`_query.Query.enable_eagerloads`::
-
-            q = sess.query(Object).yield_per(100).enable_eagerloads(False)
-
-        Or more selectively using :func:`.lazyload`; such as with
-        an asterisk to specify the default loader scheme::
-
-            q = sess.query(Object).yield_per(100).\
-                options(lazyload('*'), joinedload(Object.some_related))
-
-        .. warning::
-
-            Use this method with caution; if the same instance is
-            present in more than one batch of rows, end-user changes
-            to attributes will be overwritten.
-
-            In particular, it's usually impossible to use this setting
-            with eagerly loaded collections (i.e. any lazy='joined' or
-            'subquery') since those collections will be cleared for a
-            new load when encountered in a subsequent result batch.
-            In the case of 'subquery' loading, the full result for all
-            rows is fetched which generally defeats the purpose of
-            :meth:`~sqlalchemy.orm.query.Query.yield_per`.
-
-            Also note that while
-            :meth:`~sqlalchemy.orm.query.Query.yield_per` will set the
-            ``stream_results`` execution option to True, currently
-            this is only understood by
-            :mod:`~sqlalchemy.dialects.postgresql.psycopg2`,
-            :mod:`~sqlalchemy.dialects.mysql.mysqldb` and
-            :mod:`~sqlalchemy.dialects.mysql.pymysql` dialects
-            which will stream results using server side cursors
-            instead of pre-buffer all rows for this query. Other
-            DBAPIs **pre-buffer all rows** before making them
-            available.  The memory use of raw database rows is much less
-            than that of an ORM-mapped object, but should still be taken into
-            consideration when benchmarking.
-
-        .. seealso::
-
-            :ref:`engine_stream_results`
+        As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
+        equvalent to using the ``yield_per`` execution option at the ORM level.
+        See the section :ref:`orm_queryguide_yield_per` for further background
+        on this option.
 
         """
         self.load_options += {"_yield_per": count}
-        self._execution_options = self._execution_options.union(
-            {"stream_results": True, "max_row_buffer": count}
-        )
 
     @util.deprecated_20(
         ":meth:`_orm.Query.get`",
         alternative="The method is now available as :meth:`_orm.Session.get`",
+        becomes_legacy=True,
     )
     def get(self, ident):
         """Return an instance based on the given primary key identifier,
@@ -983,10 +935,10 @@ class Query(
     def autoflush(self, setting):
         """Return a Query with a specific 'autoflush' setting.
 
-        Note that a Session with autoflush=False will
-        not autoflush, even if this flag is set to True at the
-        Query level.  Therefore this flag is usually used only
-        to disable autoflush for a specific Query.
+        As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
+        is equvalent to using the ``autoflush`` execution option at the
+        ORM level. See the section :ref:`orm_queryguide_autoflush` for
+        further background on this option.
 
         """
         self.load_options += {"_autoflush": setting}
@@ -997,22 +949,10 @@ class Query(
         that will expire and refresh all instances
         as they are loaded, or reused from the current :class:`.Session`.
 
-        :meth:`.populate_existing` does not improve behavior when
-        the ORM is used normally - the :class:`.Session` object's usual
-        behavior of maintaining a transaction and expiring all attributes
-        after rollback or commit handles object state automatically.
-        This method is not intended for general use.
-
-        .. versionadded:: 1.4
-
-            The :meth:`.populate_existing` method is equivalent to passing the
-            ``populate_existing=True`` option to the
-            :meth:`_orm.Query.execution_options` method.
-
-        .. seealso::
-
-            :ref:`session_expire` - in the ORM :class:`_orm.Session`
-            documentation
+        As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
+        is equvalent to using the ``populate_existing`` execution option at the
+        ORM level. See the section :ref:`orm_queryguide_populate_existing` for
+        further background on this option.
 
         """
         self.load_options += {"_populate_existing": True}
@@ -1031,6 +971,7 @@ class Query(
     @util.deprecated_20(
         ":meth:`_orm.Query.with_parent`",
         alternative="Use the :func:`_orm.with_parent` standalone construct.",
+        becomes_legacy=True,
     )
     @util.preload_module("sqlalchemy.orm.relationships")
     def with_parent(self, instance, property=None, from_entity=None):  # noqa
diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index af70de101..2fc2ad68c 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -957,18 +957,8 @@ class Session(_SessionClassMethods):
 
                 :ref:`session_committing`
 
-
-        :param future: if True, use 2.0 style behavior for the
-          :meth:`_orm.Session.execute` method.   Future mode includes the
-          following behaviors:
-
-          * The :class:`_engine.Result` object returned by the
-            :meth:`_orm.Session.execute` method will return new-style tuple
-            :class:`_engine.Row` objects
-
-          * The :meth:`_orm.Session.execute` method will invoke ORM style
-            queries given objects like :class:`_sql.Select`,
-            :class:`_sql.Update` and :class:`_sql.Delete` against ORM entities
+        :param future: if True, use 2.0 style transactional and engine
+          behavior.  Future mode includes the following behaviors:
 
           * The :class:`_orm.Session` will not use "bound" metadata in order
             to locate an :class:`_engine.Engine`; the engine or engines in use
@@ -984,9 +974,6 @@ class Session(_SessionClassMethods):
             flag on a :func:`_orm.relationship` will always assume
             "False" behavior.
 
-          The "future" flag is also available on a per-execution basis
-          using the :paramref:`_orm.Session.execute.future` flag.
-
           .. versionadded:: 1.4
 
           .. seealso::
@@ -1929,7 +1916,9 @@ class Session(_SessionClassMethods):
 
     def query(self, *entities, **kwargs):
         """Return a new :class:`_query.Query` object corresponding to this
-        :class:`.Session`."""
+        :class:`_orm.Session`.
+
+        """
 
         return self._query_cls(entities, self, **kwargs)
 
diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py
index c923bf651..dc2aacbea 100644
--- a/lib/sqlalchemy/sql/dml.py
+++ b/lib/sqlalchemy/sql/dml.py
@@ -582,14 +582,6 @@ class ValuesBase(UpdateBase):
 
               :meth:`_expression.Update.ordered_values`
 
-        .. seealso::
-
-            :ref:`inserts_and_updates` - SQL Expression
-            Language Tutorial
-
-            :func:`_expression.insert` - produce an ``INSERT`` statement
-
-            :func:`_expression.update` - produce an ``UPDATE`` statement
 
         """
         if self._select_names:
@@ -777,9 +769,7 @@ class Insert(ValuesBase):
     The :class:`_expression.Insert` object is created using the
     :func:`_expression.insert()` function.
 
-    .. seealso::
-
-        :ref:`coretutorial_insert_expressions`
+    .. note - the __init__() method delivers the docstring for this object
 
     """
 
@@ -834,10 +824,26 @@ class Insert(ValuesBase):
     ):
         """Construct an :class:`_expression.Insert` object.
 
+        E.g.::
+
+            from sqlalchemy import insert
+
+            stmt = (
+                insert(user_table).
+                values(name='username', fullname='Full Username')
+            )
+
         Similar functionality is available via the
         :meth:`_expression.TableClause.insert` method on
         :class:`_schema.Table`.
 
+        .. seealso::
+
+            :ref:`coretutorial_insert_expressions` - in the 1.x tutorial
+
+            :ref:`tutorial_core_insert` - in the 2.0 tutorial
+
+
         :param table: :class:`_expression.TableClause`
          which is the subject of the
          insert.
@@ -976,15 +982,15 @@ class DMLWhereBase(object):
     _where_criteria = ()
 
     @_generative
-    def where(self, whereclause):
-        """Return a new construct with the given expression added to
+    def where(self, *whereclause):
+        """Return a new construct with the given expression(s) added to
         its WHERE clause, joined to the existing clause via AND, if any.
 
         """
 
-        self._where_criteria += (
-            coercions.expect(roles.WhereHavingRole, whereclause),
-        )
+        for criterion in list(whereclause):
+            where_criteria = coercions.expect(roles.WhereHavingRole, criterion)
+            self._where_criteria += (where_criteria,)
 
     def filter(self, *criteria):
         """A synonym for the :meth:`_dml.DMLWhereBase.where` method.
@@ -1032,9 +1038,7 @@ class DMLWhereBase(object):
 class Update(DMLWhereBase, ValuesBase):
     """Represent an Update construct.
 
-    The :class:`_expression.Update`
-    object is created using the :func:`update()`
-    function.
+    .. note - the __init__() method delivers the docstring for this object
 
     """
 
@@ -1090,16 +1094,23 @@ class Update(DMLWhereBase, ValuesBase):
 
             from sqlalchemy import update
 
-            stmt = update(users).where(users.c.id==5).\
-                    values(name='user #5')
+            stmt = (
+                update(user_table).
+                where(user_table.c.id == 5).
+                values(name='user #5')
+            )
 
         Similar functionality is available via the
         :meth:`_expression.TableClause.update` method on
-        :class:`_schema.Table`::
+        :class:`_schema.Table`.
+
+        .. seealso::
+
+            :ref:`inserts_and_updates` - in the 1.x tutorial
+
+            :ref:`tutorial_core_update_delete` - in the 2.0 tutorial
+
 
-            stmt = users.update().\
-                        where(users.c.id==5).\
-                        values(name='user #5')
 
         :param table: A :class:`_schema.Table`
          object representing the database
@@ -1279,9 +1290,7 @@ class Update(DMLWhereBase, ValuesBase):
 class Delete(DMLWhereBase, UpdateBase):
     """Represent a DELETE construct.
 
-    The :class:`_expression.Delete`
-    object is created using the :func:`delete()`
-    function.
+    .. note - the __init__() method delivers the docstring for this object
 
     """
 
@@ -1317,10 +1326,26 @@ class Delete(DMLWhereBase, UpdateBase):
     ):
         r"""Construct :class:`_expression.Delete` object.
 
+        E.g.::
+
+            from sqlalchemy import delete
+
+            stmt = (
+                delete(user_table).
+                where(user_table.c.id == 5)
+            )
+
         Similar functionality is available via the
         :meth:`_expression.TableClause.delete` method on
         :class:`_schema.Table`.
 
+        .. seealso::
+
+            :ref:`inserts_and_updates` - in the 1.x tutorial
+
+            :ref:`tutorial_core_update_delete` - in the 2.0 tutorial
+
+
         :param table: The table to delete rows from.
 
         :param whereclause: A :class:`_expression.ClauseElement`
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index e268abc8a..550cbea24 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1655,7 +1655,6 @@ class TextClause(
 
             :ref:`sqlexpression_text` - in the Core tutorial
 
-            :ref:`orm_tutorial_literal_sql` - in the ORM tutorial
 
         """
         return TextClause(text, bind=bind)
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index d764002a6..ccb1dd7e9 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -638,13 +638,14 @@ class Table(DialectKWArgs, SchemaItem, TableClause):
             )
 
         insp = inspection.inspect(autoload_with)
-        insp.reflect_table(
-            self,
-            include_columns,
-            exclude_columns,
-            resolve_fks,
-            _extend_on=_extend_on,
-        )
+        with insp._inspection_context() as conn_insp:
+            conn_insp.reflect_table(
+                self,
+                include_columns,
+                exclude_columns,
+                resolve_fks,
+                _extend_on=_extend_on,
+            )
 
     @property
     def _sorted_constraints(self):
@@ -2824,7 +2825,16 @@ class DefaultClause(FetchedValue):
 
 
 class Constraint(DialectKWArgs, SchemaItem):
-    """A table-level SQL constraint."""
+    """A table-level SQL constraint.
+
+    :class:`_schema.Constraint` serves as the base class for the series of
+    constraint objects that can be associated with :class:`_schema.Table`
+    objects, including :class:`_schema.PrimaryKeyConstraint`,
+    :class:`_schema.ForeignKeyConstraint`
+    :class:`_schema.UniqueConstraint`, and
+    :class:`_schema.CheckConstraint`.
+
+    """
 
     __visit_name__ = "constraint"
 
@@ -2856,28 +2866,18 @@ class Constraint(DialectKWArgs, SchemaItem):
 
             .. versionadded:: 1.0.0
 
-        :param _create_rule:
-          a callable which is passed the DDLCompiler object during
-          compilation. Returns True or False to signal inline generation of
-          this Constraint.
-
-          The AddConstraint and DropConstraint DDL constructs provide
-          DDLElement's more comprehensive "conditional DDL" approach that is
-          passed a database connection when DDL is being issued. _create_rule
-          is instead called during any CREATE TABLE compilation, where there
-          may not be any transaction/connection in progress. However, it
-          allows conditional compilation of the constraint even for backends
-          which do not support addition of constraints through ALTER TABLE,
-          which currently includes SQLite.
-
-          _create_rule is used by some types to create constraints.
-          Currently, its call signature is subject to change at any time.
-
         :param \**dialect_kw:  Additional keyword arguments are dialect
             specific, and passed in the form ``<dialectname>_<argname>``.  See
             the documentation regarding an individual dialect at
             :ref:`dialect_toplevel` for detail on documented arguments.
 
+        :param _create_rule:
+          used internally by some datatypes that also create constraints.
+
+        :param _type_bound:
+          used internally to indicate that this constraint is associated with
+          a specific datatype.
+
         """
 
         self.name = name
@@ -4158,7 +4158,10 @@ class MetaData(SchemaItem):
     """
 
     def __repr__(self):
-        return "MetaData(bind=%r)" % self.bind
+        if self.bind:
+            return "MetaData(bind=%r)" % self.bind
+        else:
+            return "MetaData()"
 
     def __contains__(self, table_or_key):
         if not isinstance(table_or_key, util.string_types):
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index fd8832400..895a4532b 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -700,8 +700,7 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable):
     @util.memoized_property
     def columns(self):
         """A named-based collection of :class:`_expression.ColumnElement`
-        objects
-        maintained by this :class:`_expression.FromClause`.
+        objects maintained by this :class:`_expression.FromClause`.
 
         The :attr:`.columns`, or :attr:`.c` collection, is the gateway
         to the construction of SQL expressions using table-bound or
@@ -709,6 +708,8 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable):
 
             select(mytable).where(mytable.c.somecolumn == 5)
 
+        :return: a :class:`.ColumnCollection` object.
+
         """
 
         if "_columns" not in self.__dict__:
@@ -734,8 +735,12 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable):
 
     @util.memoized_property
     def primary_key(self):
-        """Return the collection of :class:`_schema.Column` objects
-        which comprise the primary key of this FromClause.
+        """Return the iterable collection of :class:`_schema.Column` objects
+        which comprise the primary key of this :class:`_selectable.FromClause`.
+
+        For a :class:`_schema.Table` object, this collection is represented
+        by the :class:`_schema.PrimaryKeyConstraint` which itself is an
+        iterable collection of :class:`_schema.Column` objects.
 
         """
         self._init_collections()
@@ -771,7 +776,16 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable):
 
     c = property(
         attrgetter("columns"),
-        doc="An alias for the :attr:`.columns` attribute.",
+        doc="""
+        A named-based collection of :class:`_expression.ColumnElement`
+        objects maintained by this :class:`_expression.FromClause`.
+
+        The :attr:`_sql.FromClause.c` attribute is an alias for the
+        :attr:`_sql.FromClause.columns` atttribute.
+
+        :return: a :class:`.ColumnCollection`
+
+        """,
     )
     _select_iterable = property(attrgetter("columns"))
 
@@ -1227,7 +1241,9 @@ class Join(roles.DMLTableRole, FromClause):
     )
     def bind(self):
         """Return the bound engine associated with either the left or right
-        side of this :class:`_sql.Join`."""
+        side of this :class:`_sql.Join`.
+
+        """
 
         return self.left.bind or self.right.bind
 
@@ -1441,10 +1457,14 @@ class AliasedReturnsRows(NoInit, FromClause):
 
     @property
     def description(self):
+        name = self.name
+        if isinstance(name, _anonymous_label):
+            name = "anon_1"
+
         if util.py3k:
-            return self.name
+            return name
         else:
-            return self.name.encode("ascii", "backslashreplace")
+            return name.encode("ascii", "backslashreplace")
 
     @property
     def original(self):
@@ -1693,8 +1713,18 @@ class CTE(Generative, HasPrefixes, HasSuffixes, AliasedReturnsRows):
     """Represent a Common Table Expression.
 
     The :class:`_expression.CTE` object is obtained using the
-    :meth:`_expression.SelectBase.cte` method from any selectable.
-    See that method for complete examples.
+    :meth:`_sql.SelectBase.cte` method from any SELECT statement. A less often
+    available syntax also allows use of the :meth:`_sql.HasCTE.cte` method
+    present on :term:`DML` constructs such as :class:`_sql.Insert`,
+    :class:`_sql.Update` and
+    :class:`_sql.Delete`.   See the :meth:`_sql.HasCTE.cte` method for
+    usage details on CTEs.
+
+    .. seealso::
+
+        :ref:`tutorial_subqueries_ctes` - in the 2.0 tutorial
+
+        :meth:`_sql.HasCTE.cte` - examples of calling styles
 
     """
 
@@ -1955,7 +1985,7 @@ class HasCTE(roles.HasCTERole):
 
         .. seealso::
 
-            :meth:`.orm.query.Query.cte` - ORM version of
+            :meth:`_orm.Query.cte` - ORM version of
             :meth:`_expression.HasCTE.cte`.
 
         """
@@ -2546,10 +2576,29 @@ class SelectBase(
     def as_scalar(self):
         return self.scalar_subquery()
 
+    def exists(self):
+        """Return an :class:`_sql.Exists` representation of this selectable,
+        which can be used as a column expression.
+
+        The returned object is an instance of :class:`_sql.Exists`.
+
+        .. seealso::
+
+            :func:`_sql.exists`
+
+            :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
+
+        .. versionadded:: 1.4
+
+        """
+        return Exists(self)
+
     def scalar_subquery(self):
         """Return a 'scalar' representation of this selectable, which can be
         used as a column expression.
 
+        The returned object is an instance of :class:`_sql.ScalarSelect`.
+
         Typically, a select statement which has only one column in its columns
         clause is eligible to be used as a scalar expression.  The scalar
         subquery can then be used in the WHERE clause or columns clause of
@@ -2563,6 +2612,12 @@ class SelectBase(
         .. versionchanged: 1.4 - the ``.as_scalar()`` method was renamed to
            :meth:`_expression.SelectBase.scalar_subquery`.
 
+        .. seealso::
+
+            :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
+
+            :ref:`scalar_selects` - in the 1.x tutorial
+
         """
         if self._label_style is not LABEL_STYLE_NONE:
             self = self._set_label_style(LABEL_STYLE_NONE)
@@ -3777,12 +3832,12 @@ class SelectState(util.MemoizedSlots, CompileState):
 
             if not len(froms):
                 raise exc.InvalidRequestError(
-                    "Select statement '%s"
+                    "Select statement '%r"
                     "' returned no FROM clauses "
                     "due to auto-correlation; "
                     "specify correlate(<tables>) "
                     "to control correlation "
-                    "manually." % self
+                    "manually." % self.statement
                 )
 
         return froms
@@ -3994,7 +4049,9 @@ class Select(
 
         :func:`_sql.select`
 
-        :ref:`coretutorial_selecting` - in the Core tutorial
+        :ref:`coretutorial_selecting` - in the 1.x tutorial
+
+        :ref:`tutorial_selecting_data` - in the 2.0 tutorial
 
     """
 
@@ -4491,8 +4548,8 @@ class Select(
 
         .. seealso::
 
-            :ref:`orm_tutorial_literal_sql` - usage examples in the
-            ORM tutorial
+            :ref:`orm_queryguide_selecting_text` - usage examples in the
+            ORM Querying Guide
 
         """
         meth = SelectState.get_plugin_class(self).from_statement
@@ -4548,6 +4605,10 @@ class Select(
 
         .. seealso::
 
+            :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
+
+            :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
+
             :meth:`_expression.Select.join_from`
 
             :meth:`_expression.Select.outerjoin`
@@ -4599,6 +4660,10 @@ class Select(
 
         .. seealso::
 
+            :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
+
+            :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
+
             :meth:`_expression.Select.join`
 
         """  # noqa: E501
@@ -4648,6 +4713,10 @@ class Select(
 
         .. seealso::
 
+            :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`
+
+            :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`
+
             :meth:`_expression.Select.join`
 
         """
@@ -4903,7 +4972,7 @@ class Select(
     _whereclause = whereclause
 
     @_generative
-    def where(self, whereclause):
+    def where(self, *whereclause):
         """Return a new :func:`_expression.select` construct with
         the given expression added to
         its WHERE clause, joined to the existing clause via AND, if any.
@@ -4911,9 +4980,10 @@ class Select(
         """
 
         assert isinstance(self._where_criteria, tuple)
-        self._where_criteria += (
-            coercions.expect(roles.WhereHavingRole, whereclause),
-        )
+
+        for criterion in list(whereclause):
+            where_criteria = coercions.expect(roles.WhereHavingRole, criterion)
+            self._where_criteria += (where_criteria,)
 
     @_generative
     def having(self, having):
@@ -5400,6 +5470,24 @@ class Select(
 
 
 class ScalarSelect(roles.InElementRole, Generative, Grouping):
+    """Represent a scalar subquery.
+
+
+    A :class:`_sql.ScalarSubquery` is created by invoking the
+    :meth:`_sql.SelectBase.scalar_subquery` method.   The object
+    then participates in other SQL expressions as a SQL column expression
+    within the :class:`_sql.ColumnElement` hierarchy.
+
+    .. seealso::
+
+        :meth:`_sql.SelectBase.scalar_subquery`
+
+        :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
+
+        :ref:`scalar_selects` - in the 1.x tutorial
+
+    """
+
     _from_objects = []
     _is_from_container = True
     _is_implicitly_boolean = False
@@ -5430,9 +5518,79 @@ class ScalarSelect(roles.InElementRole, Generative, Grouping):
     def self_group(self, **kwargs):
         return self
 
+    @_generative
+    def correlate(self, *fromclauses):
+        r"""Return a new :class:`_expression.ScalarSelect`
+        which will correlate the given FROM
+        clauses to that of an enclosing :class:`_expression.Select`.
+
+        This method is mirrored from the :meth:`_sql.Select.correlate` method
+        of the underlying :class:`_sql.Select`.  The method applies the
+        :meth:_sql.Select.correlate` method, then returns a new
+        :class:`_sql.ScalarSelect` against that statement.
+
+        .. versionadded:: 1.4 Previously, the
+           :meth:`_sql.ScalarSelect.correlate`
+           method was only available from :class:`_sql.Select`.
+
+        :param \*fromclauses: a list of one or more
+         :class:`_expression.FromClause`
+         constructs, or other compatible constructs (i.e. ORM-mapped
+         classes) to become part of the correlate collection.
+
+        .. seealso::
+
+            :meth:`_expression.ScalarSelect.correlate_except`
+
+            :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
+
+            :ref:`correlated_subqueries` - in the 1.x tutorial
+
+
+        """
+        self.element = self.element.correlate(*fromclauses)
+
+    @_generative
+    def correlate_except(self, *fromclauses):
+        r"""Return a new :class:`_expression.ScalarSelect`
+        which will omit the given FROM
+        clauses from the auto-correlation process.
+
+        This method is mirrored from the
+        :meth:`_sql.Select.correlate_except` method of the underlying
+        :class:`_sql.Select`.  The method applies the
+        :meth:_sql.Select.correlate_except` method, then returns a new
+        :class:`_sql.ScalarSelect` against that statement.
+
+        .. versionadded:: 1.4 Previously, the
+           :meth:`_sql.ScalarSelect.correlate_except`
+           method was only available from :class:`_sql.Select`.
+
+        :param \*fromclauses: a list of one or more
+         :class:`_expression.FromClause`
+         constructs, or other compatible constructs (i.e. ORM-mapped
+         classes) to become part of the correlate-exception collection.
+
+        .. seealso::
+
+            :meth:`_expression.ScalarSelect.correlate`
+
+            :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial
+
+            :ref:`correlated_subqueries` - in the 1.x tutorial
+
+
+        """
+
+        self.element = self.element.correlate_except(*fromclauses)
+
 
 class Exists(UnaryExpression):
-    """Represent an ``EXISTS`` clause."""
+    """Represent an ``EXISTS`` clause.
+
+    See :func:`_sql.exists` for a description of usage.
+
+    """
 
     _from_objects = []
     inherit_cache = True
@@ -5440,12 +5598,23 @@ class Exists(UnaryExpression):
     def __init__(self, *args, **kwargs):
         """Construct a new :class:`_expression.Exists` construct.
 
-        The modern form of :func:`.exists` is to invoke with no arguments,
-        which will produce an ``"EXISTS *"`` construct.   A WHERE clause
-        is then added using the :meth:`.Exists.where` method::
+        The :func:`_sql.exists` can be invoked by itself to produce an
+        :class:`_sql.Exists` construct, which will accept simple WHERE
+        criteria::
 
             exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
 
+        However, for greater flexibility in constructing the SELECT, an
+        existing :class:`_sql.Select` construct may be converted to an
+        :class:`_sql.Exists`, most conveniently by making use of the
+        :meth:`_sql.SelectBase.exists` method::
+
+            exists_criteria = (
+                select(table2.c.col2).
+                where(table1.c.col1 == table2.c.col2).
+                exists()
+            )
+
         The EXISTS criteria is then used inside of an enclosing SELECT::
 
             stmt = select(table1.c.col1).where(exists_criteria)
@@ -5453,9 +5622,13 @@ class Exists(UnaryExpression):
         The above statement will then be of the form::
 
             SELECT col1 FROM table1 WHERE EXISTS
-            (SELECT * FROM table2 WHERE table2.col2 = table1.col1)
+            (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
 
-        """
+        .. seealso::
+
+            :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
+
+        """  # noqa E501
         if args and isinstance(args[0], (SelectBase, ScalarSelect)):
             s = args[0]
         else:
@@ -5524,6 +5697,13 @@ class Exists(UnaryExpression):
         return Select._create_select_from_fromclause(self, [self], **kwargs)
 
     def correlate(self, *fromclause):
+        """Apply correlation to the subquery noted by this :class:`_sql.Exists`.
+
+        .. seealso::
+
+            :meth:`_sql.ScalarSelect.correlate`
+
+        """
         e = self._clone()
         e.element = self._regroup(
             lambda element: element.correlate(*fromclause)
@@ -5531,6 +5711,14 @@ class Exists(UnaryExpression):
         return e
 
     def correlate_except(self, *fromclause):
+        """Apply correlation to the subquery noted by this :class:`_sql.Exists`.
+
+        .. seealso::
+
+            :meth:`_sql.ScalarSelect.correlate_except`
+
+        """
+
         e = self._clone()
         e.element = self._regroup(
             lambda element: element.correlate_except(*fromclause)
@@ -5544,6 +5732,11 @@ class Exists(UnaryExpression):
         method of the select
         statement contained.
 
+        .. note:: it is typically preferable to build a :class:`_sql.Select`
+           statement first, including the desired WHERE clause, then use the
+           :meth:`_sql.SelectBase.exists` method to produce an
+           :class:`_sql.Exists` object at once.
+
         """
         e = self._clone()
         e.element = self._regroup(lambda element: element.select_from(*froms))
@@ -5554,6 +5747,12 @@ class Exists(UnaryExpression):
         given expression added to
         its WHERE clause, joined to the existing clause via AND, if any.
 
+
+        .. note:: it is typically preferable to build a :class:`_sql.Select`
+           statement first, including the desired WHERE clause, then use the
+           :meth:`_sql.SelectBase.exists` method to produce an
+           :class:`_sql.Exists` object at once.
+
         """
         e = self._clone()
         e.element = self._regroup(lambda element: element.where(clause))
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index 614b70a41..bca6e9020 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -456,6 +456,28 @@ class TypeEngine(Traversible):
         else:
             return self.__class__
 
+    @classmethod
+    def _is_generic_type(cls):
+        n = cls.__name__
+        return n.upper() != n
+
+    def _generic_type_affinity(self):
+
+        for t in self.__class__.__mro__:
+            if (
+                t.__module__
+                in (
+                    "sqlalchemy.sql.sqltypes",
+                    "sqlalchemy.sql.type_api",
+                )
+                and t._is_generic_type()
+            ):
+                if t in (TypeEngine, UserDefinedType):
+                    return NULLTYPE.__class__
+                return t
+        else:
+            return self.__class__
+
     def dialect_impl(self, dialect):
         """Return a dialect-specific implementation for this
         :class:`.TypeEngine`.
diff --git a/lib/sqlalchemy/testing/suite/test_unicode_ddl.py b/lib/sqlalchemy/testing/suite/test_unicode_ddl.py
index 6c6518011..af6b382ae 100644
--- a/lib/sqlalchemy/testing/suite/test_unicode_ddl.py
+++ b/lib/sqlalchemy/testing/suite/test_unicode_ddl.py
@@ -188,7 +188,7 @@ class UnicodeSchemaTest(fixtures.TablesTest):
             eq_(
                 repr(t),
                 (
-                    "Table('\\u6e2c\\u8a66', MetaData(bind=None), "
+                    "Table('\\u6e2c\\u8a66', MetaData(), "
                     "Column('\\u6e2c\\u8a66_id', Integer(), "
                     "table=<\u6e2c\u8a66>), "
                     "schema=None)"
@@ -198,7 +198,7 @@ class UnicodeSchemaTest(fixtures.TablesTest):
             eq_(
                 repr(t),
                 (
-                    "Table('測試', MetaData(bind=None), "
+                    "Table('測試', MetaData(), "
                     "Column('測試_id', Integer(), "
                     "table=<測試>), "
                     "schema=None)"
diff --git a/lib/sqlalchemy/util/deprecations.py b/lib/sqlalchemy/util/deprecations.py
index 9f0ca0b1a..f46374601 100644
--- a/lib/sqlalchemy/util/deprecations.py
+++ b/lib/sqlalchemy/util/deprecations.py
@@ -81,10 +81,18 @@ def deprecated_cls(version, message, constructor="__init__"):
     return decorate
 
 
-def deprecated_20_cls(clsname, alternative=None, constructor="__init__"):
+def deprecated_20_cls(
+    clsname, alternative=None, constructor="__init__", becomes_legacy=False
+):
     message = (
         ".. deprecated:: 1.4 The %s class is considered legacy as of the "
-        "1.x series of SQLAlchemy and will be removed in 2.0." % clsname
+        "1.x series of SQLAlchemy and %s in 2.0."
+        % (
+            clsname,
+            "will be removed"
+            if not becomes_legacy
+            else "becomes a legacy construct",
+        )
     )
 
     if alternative:
@@ -161,7 +169,7 @@ def moved_20(message, **kw):
     )
 
 
-def deprecated_20(api_name, alternative=None, **kw):
+def deprecated_20(api_name, alternative=None, becomes_legacy=False, **kw):
     type_reg = re.match("^:(attr|func|meth):", api_name)
     if type_reg:
         type_ = {"attr": "attribute", "func": "function", "meth": "method"}[
@@ -171,8 +179,14 @@ def deprecated_20(api_name, alternative=None, **kw):
         type_ = "construct"
     message = (
         "The %s %s is considered legacy as of the "
-        "1.x series of SQLAlchemy and will be removed in 2.0."
-        % (api_name, type_)
+        "1.x series of SQLAlchemy and %s in 2.0."
+        % (
+            api_name,
+            type_,
+            "will be removed"
+            if not becomes_legacy
+            else "becomes a legacy construct",
+        )
     )
 
     if alternative: