Documentation improvements

Also remove deprecated usage:
- load_only does not accept strings
- case.whens is positional only

Ref #6712
Ref #5994
Ref #6121
Ref #6785
Ref https://groups.google.com/g/sqlalchemy/c/-cnhThEu3kk

Change-Id: I5db49a075b9d3d332518b9d189a24b13b502e2af

5 files changed, 85 insertions, 6 deletions

diff --git a/doc/build/orm/extensions/asyncio.rst b/doc/build/orm/extensions/asyncio.rst
index 6aca1762d..93efd728f 100644
--- a/doc/build/orm/extensions/asyncio.rst
+++ b/doc/build/orm/extensions/asyncio.rst
@@ -62,6 +62,9 @@ to deliver a streaming server-side :class:`_asyncio.AsyncResult`::
 
             print(result.fetchall())
 
+        # for AsyncEngine created in function scope, close and
+        # clean-up pooled connections
+        await engine.dispose()
 
     asyncio.run(async_main())
 
@@ -69,6 +72,18 @@ Above, the :meth:`_asyncio.AsyncConnection.run_sync` method may be used to
 invoke special DDL functions such as :meth:`_schema.MetaData.create_all` that
 don't include an awaitable hook.
 
+.. tip:: It's advisable to invoke the :meth:`_asyncio.AsyncEngine.dispose` method
+   using ``await`` when using the :class:`_asyncio.AsyncEngine` object in a
+   scope that will go out of context and be garbage collected, as illustrated in the
+   ``async_main`` function in the above example.  This ensures that any
+   connections held open by the connection pool will be properly disposed
+   within an awaitable context.   Unlike when using blocking IO, SQLAlchemy
+   cannot properly dispose of these connections within methods like ``__del__``
+   or weakref finalizers as there is no opportunity to invoke ``await``.
+   Failing to explicitly dispose of the engine when it falls out of scope
+   may result in warnings emitted to standard out resembling the form
+   ``RuntimeError: Event loop is closed`` within garbage collection.
+
 The :class:`_asyncio.AsyncConnection` also features a "streaming" API via
 the :meth:`_asyncio.AsyncConnection.stream` method that returns an
 :class:`_asyncio.AsyncResult` object.  This result object uses a server-side
@@ -179,6 +194,10 @@ illustrates a complete example including mapper and session configuration::
             # expire_on_commit=False allows
             print(a1.data)
 
+        # for AsyncEngine created in function scope, close and
+        # clean-up pooled connections
+        await engine.dispose()
+
 
     asyncio.run(async_main())
 
@@ -369,6 +388,10 @@ attribute accesses within a separate function::
 
             await session.commit()
 
+        # for AsyncEngine created in function scope, close and
+        # clean-up pooled connections
+        await engine.dispose()
+
     asyncio.run(async_main())
 
 The above approach of running certain functions within a "sync" runner
@@ -457,6 +480,44 @@ the usual ``await`` keywords are necessary, including for the
 .. currentmodule:: sqlalchemy.ext.asyncio
 
 
+Using the Inspector to inspect schema objects
+---------------------------------------------------
+
+SQLAlchemy does not yet offer an asyncio version of the
+:class:`_reflection.Inspector` (introduced at :ref:`metadata_reflection_inspector`),
+however the existing interface may be used in an asyncio context by
+leveraging the :meth:`_asyncio.AsyncConnection.run_sync` method of
+:class:`_asyncio.AsyncConnection`::
+
+    import asyncio
+
+    from sqlalchemy.ext.asyncio import create_async_engine
+    from sqlalchemy.ext.asyncio import AsyncSession
+    from sqlalchemy import inspect
+
+    engine = create_async_engine(
+      "postgresql+asyncpg://scott:tiger@localhost/test"
+    )
+
+    def use_inspector(conn):
+        inspector = inspect(conn)
+        # use the inspector
+        print(inspector.get_view_names())
+        # return any value to the caller
+        return inspector.get_table_names()
+
+    async def async_main():
+        async with engine.connect() as conn:
+            tables = await conn.run_sync(use_inspector)
+
+    asyncio.run(async_main())
+
+.. seealso::
+
+    :ref:`metadata_reflection`
+
+    :ref:`inspection_toplevel`
+
 Engine API Documentation
 -------------------------
 
diff --git a/lib/sqlalchemy/engine/result.py b/lib/sqlalchemy/engine/result.py
index 119bf4a9e..60474c0ed 100644
--- a/lib/sqlalchemy/engine/result.py
+++ b/lib/sqlalchemy/engine/result.py
@@ -708,6 +708,15 @@ class Result(_WithKeys, ResultInternal):
        :class:`.ResultProxy` interface.   When using the ORM, a higher level
        object called :class:`.ChunkedIteratorResult` is normally used.
 
+    .. note:: In SQLAlchemy 1.4 and above, this object is
+       used for ORM results returned by :meth:`_orm.Session.execute`, which can
+       yield instances of ORM mapped objects either individually or within
+       tuple-like rows. Note that the :class:`_result.Result` object does not
+       deduplicate instances or rows automatically as is the case with the
+       legacy :class:`_orm.Query` object. For in-Python de-duplication of
+       instances or rows, use the :meth:`_result.Result.unique` modifier
+       method.
+
     .. seealso::
 
         :ref:`tutorial_fetching_rows` - in the :doc:`/tutorial/index`
diff --git a/lib/sqlalchemy/ext/asyncio/result.py b/lib/sqlalchemy/ext/asyncio/result.py
index 2fdaec741..dff87a569 100644
--- a/lib/sqlalchemy/ext/asyncio/result.py
+++ b/lib/sqlalchemy/ext/asyncio/result.py
@@ -29,6 +29,15 @@ class AsyncResult(AsyncCommon):
     :meth:`_asyncio.AsyncConnection.stream` and
     :meth:`_asyncio.AsyncSession.stream` methods.
 
+    .. note:: As is the case with :class:`_engine.Result`, this object is
+       used for ORM results returned by :meth:`_asyncio.AsyncSession.execute`,
+       which can yield instances of ORM mapped objects either individually or
+       within tuple-like rows.  Note that these result objects do not
+       deduplicate instances or rows automatically as is the case with the
+       legacy :class:`_orm.Query` object. For in-Python de-duplication of
+       instances or rows, use the :meth:`_asyncio.AsyncResult.unique` modifier
+       method.
+
     .. versionadded:: 1.4
 
     """
diff --git a/lib/sqlalchemy/orm/strategy_options.py b/lib/sqlalchemy/orm/strategy_options.py
index 043ecfed3..399e33b89 100644
--- a/lib/sqlalchemy/orm/strategy_options.py
+++ b/lib/sqlalchemy/orm/strategy_options.py
@@ -414,7 +414,7 @@ class Load(Generative, LoaderOption):
             query = session.query(Author)
             query = query.options(
                         joinedload(Author.book).options(
-                            load_only("summary", "excerpt"),
+                            load_only(Book.summary, Book.excerpt),
                             joinedload(Book.citations).options(
                                 joinedload(Citation.author)
                             )
@@ -1152,14 +1152,14 @@ def load_only(loadopt, *attrs):
     Example - given a class ``User``, load only the ``name`` and ``fullname``
     attributes::
 
-        session.query(User).options(load_only("name", "fullname"))
+        session.query(User).options(load_only(User.name, User.fullname))
 
     Example - given a relationship ``User.addresses -> Address``, specify
     subquery loading for the ``User.addresses`` collection, but on each
     ``Address`` object load only the ``email_address`` attribute::
 
         session.query(User).options(
-                subqueryload("addresses").load_only("email_address")
+                subqueryload(User.addresses).load_only(Address.email_address)
         )
 
     For a :class:`_query.Query` that has multiple entities,
@@ -1167,8 +1167,8 @@ def load_only(loadopt, *attrs):
     specifically referred to using the :class:`_orm.Load` constructor::
 
         session.query(User, Address).join(User.addresses).options(
-                    Load(User).load_only("name", "fullname"),
-                    Load(Address).load_only("email_address")
+                    Load(User).load_only(User.name, User.fullname),
+                    Load(Address).load_only(Address.email_address)
                 )
 
      .. note:: This method will still load a :class:`_schema.Column` even
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 173314abe..ef3eb82cd 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2732,7 +2732,7 @@ class Case(ColumnElement):
             stmt = select(users_table).\
                         where(
                             case(
-                                whens={"wendy": "W", "jack": "J"},
+                                {"wendy": "W", "jack": "J"},
                                 value=users_table.c.name,
                                 else_='E'
                             )