pep484: schema API

implement strict typing for schema.py

this module has lots of public API, lots of old decisions
and very hard to follow construction sequences in many
cases, and is also where we get a lot of new feature requests,
so strict typing should help keep things clean.

among improvements here, fixed the pool .info getters
and also figured out how to get ColumnCollection and
related to be covariant so that we may set them up
as returning Column or ColumnClause without any conflicts.

DDL was affected, noting that superclasses of DDLElement
(_DDLCompiles, added recently) can now be passed into
"ddl_if" callables; reorganized ddl into ExecutableDDLElement
as a new name for DDLElement and _DDLCompiles renamed to
BaseDDLElement.

setting up strict also located an API use case that
is completely broken, which is connection.execute(some_default)
returns a scalar value.   This case has been deprecated
and new paths have been set up so that connection.scalar()
may be used.  This likely wasn't possible in previous
versions because scalar() would assume a CursorResult.

The scalar() change also impacts Session as we have explicit
support (since someone had reported it as a regression)
for session.execute(Sequence()) to work.  They will get the
same deprecation message (which omits the word "Connection",
just uses ".execute()" and ".scalar()") and they can then
use Session.scalar() as well.  Getting this to type
correctly while still supporting ORM use cases required
some refactoring, and I also set up a keyword only delimeter
for Session.execute() and related as execution_options /
bind_arguments should always be keyword only, applied these
changes to AsyncSession as well.

Additionally simpify Table __init__ now that we are Python
3 only, we can have positional plus explicit kwargs finally.
Simplify Column.__init__ as well again taking advantage
of kw only arguments.

Fill in most/all __init__ methods in sqltypes.py as
the constructor for types is most of the API.   should
likely do this for dialect-specific types as well.

Apply _InfoType for all info attributes as should have been
done originally and update descriptor decorators.

Change-Id: I3f9f8ff3f1c8858471ff4545ac83d68c88107527

7 files changed, 51 insertions, 21 deletions

diff --git a/lib/sqlalchemy/ext/associationproxy.py b/lib/sqlalchemy/ext/associationproxy.py
index 5ea268a2d..2e6c6b422 100644
--- a/lib/sqlalchemy/ext/associationproxy.py
+++ b/lib/sqlalchemy/ext/associationproxy.py
@@ -65,6 +65,7 @@ if typing.TYPE_CHECKING:
     from ..orm.interfaces import MapperProperty
     from ..orm.interfaces import PropComparator
     from ..orm.mapper import Mapper
+    from ..sql._typing import _InfoType
 
 _T = TypeVar("_T", bound=Any)
 _T_co = TypeVar("_T_co", bound=Any, covariant=True)
@@ -221,8 +222,8 @@ class _AssociationProxyProtocol(Protocol[_T]):
     proxy_factory: Optional[_ProxyFactoryProtocol]
     proxy_bulk_set: Optional[_ProxyBulkSetProtocol]
 
-    @util.memoized_property
-    def info(self) -> Dict[Any, Any]:
+    @util.ro_memoized_property
+    def info(self) -> _InfoType:
         ...
 
     def for_class(
@@ -259,7 +260,7 @@ class AssociationProxy(
         getset_factory: Optional[_GetSetFactoryProtocol] = None,
         proxy_factory: Optional[_ProxyFactoryProtocol] = None,
         proxy_bulk_set: Optional[_ProxyBulkSetProtocol] = None,
-        info: Optional[Dict[Any, Any]] = None,
+        info: Optional[_InfoType] = None,
         cascade_scalar_deletes: bool = False,
     ):
         """Construct a new :class:`.AssociationProxy`.
@@ -338,7 +339,7 @@ class AssociationProxy(
             id(self),
         )
         if info:
-            self.info = info
+            self.info = info  # type: ignore
 
     @overload
     def __get__(
@@ -777,8 +778,8 @@ class AssociationProxyInstance(SQLORMOperations[_T]):
 
             return getter, plain_setter
 
-    @property
-    def info(self) -> Dict[Any, Any]:
+    @util.ro_non_memoized_property
+    def info(self) -> _InfoType:
         return self.parent.info
 
     @overload
diff --git a/lib/sqlalchemy/ext/asyncio/engine.py b/lib/sqlalchemy/ext/asyncio/engine.py
index bb51a4d22..fb05f512e 100644
--- a/lib/sqlalchemy/ext/asyncio/engine.py
+++ b/lib/sqlalchemy/ext/asyncio/engine.py
@@ -48,6 +48,7 @@ if TYPE_CHECKING:
     from ...engine.url import URL
     from ...pool import Pool
     from ...pool import PoolProxiedConnection
+    from ...sql._typing import _InfoType
     from ...sql.base import Executable
 
 
@@ -241,8 +242,8 @@ class AsyncConnection(
 
         return await greenlet_spawn(getattr, self._proxied, "connection")
 
-    @property
-    def info(self) -> Dict[str, Any]:
+    @util.ro_non_memoized_property
+    def info(self) -> _InfoType:
         """Return the :attr:`_engine.Connection.info` dictionary of the
         underlying :class:`_engine.Connection`.
 
@@ -464,7 +465,7 @@ class AsyncConnection(
          * :class:`_expression.TextClause` and
            :class:`_expression.TextualSelect`
          * :class:`_schema.DDL` and objects which inherit from
-           :class:`_schema.DDLElement`
+           :class:`_schema.ExecutableDDLElement`
 
         :param parameters: parameters which will be bound into the statement.
          This may be either a dictionary of parameter names to values,
diff --git a/lib/sqlalchemy/ext/asyncio/result.py b/lib/sqlalchemy/ext/asyncio/result.py
index a9db822a6..d0337554c 100644
--- a/lib/sqlalchemy/ext/asyncio/result.py
+++ b/lib/sqlalchemy/ext/asyncio/result.py
@@ -710,7 +710,14 @@ _RT = TypeVar("_RT", bound="Result")
 
 async def _ensure_sync_result(result: _RT, calling_method: Any) -> _RT:
     cursor_result: CursorResult
-    if not result._is_cursor:
+
+    try:
+        is_cursor = result._is_cursor
+    except AttributeError:
+        # legacy execute(DefaultGenerator) case
+        return result
+
+    if not is_cursor:
         cursor_result = getattr(result, "raw", None)  # type: ignore
     else:
         cursor_result = result  # type: ignore
diff --git a/lib/sqlalchemy/ext/asyncio/scoping.py b/lib/sqlalchemy/ext/asyncio/scoping.py
index 0d6ae92b4..33cf3f745 100644
--- a/lib/sqlalchemy/ext/asyncio/scoping.py
+++ b/lib/sqlalchemy/ext/asyncio/scoping.py
@@ -442,6 +442,7 @@ class async_scoped_session:
         self,
         statement: Executable,
         params: Optional[_CoreAnyExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -914,6 +915,7 @@ class async_scoped_session:
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -944,6 +946,7 @@ class async_scoped_session:
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -980,6 +983,7 @@ class async_scoped_session:
         self,
         statement: Executable,
         params: Optional[_CoreAnyExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -1007,6 +1011,7 @@ class async_scoped_session:
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
diff --git a/lib/sqlalchemy/ext/asyncio/session.py b/lib/sqlalchemy/ext/asyncio/session.py
index 7d63b084c..1422f99a3 100644
--- a/lib/sqlalchemy/ext/asyncio/session.py
+++ b/lib/sqlalchemy/ext/asyncio/session.py
@@ -55,6 +55,7 @@ if TYPE_CHECKING:
     from ...orm.session import _PKIdentityArgument
     from ...orm.session import _SessionBind
     from ...orm.session import _SessionBindKey
+    from ...sql._typing import _InfoType
     from ...sql.base import Executable
     from ...sql.elements import ClauseElement
     from ...sql.selectable import ForUpdateArg
@@ -260,6 +261,7 @@ class AsyncSession(ReversibleProxy[Session]):
         self,
         statement: Executable,
         params: Optional[_CoreAnyExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -294,6 +296,7 @@ class AsyncSession(ReversibleProxy[Session]):
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -306,19 +309,28 @@ class AsyncSession(ReversibleProxy[Session]):
 
         """
 
-        result = await self.execute(
+        if execution_options:
+            execution_options = util.immutabledict(execution_options).union(
+                _EXECUTE_OPTIONS
+            )
+        else:
+            execution_options = _EXECUTE_OPTIONS
+
+        result = await greenlet_spawn(
+            self.sync_session.scalar,
             statement,
             params=params,
             execution_options=execution_options,
             bind_arguments=bind_arguments,
             **kw,
         )
-        return result.scalar()
+        return result
 
     async def scalars(
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -383,6 +395,7 @@ class AsyncSession(ReversibleProxy[Session]):
         self,
         statement: Executable,
         params: Optional[_CoreAnyExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -414,6 +427,7 @@ class AsyncSession(ReversibleProxy[Session]):
         self,
         statement: Executable,
         params: Optional[_CoreSingleExecuteParams] = None,
+        *,
         execution_options: _ExecuteOptionsParameter = util.EMPTY_DICT,
         bind_arguments: Optional[_BindArguments] = None,
         **kw: Any,
@@ -1277,7 +1291,7 @@ class async_sessionmaker:
         class_: Type[AsyncSession] = AsyncSession,
         autoflush: bool = True,
         expire_on_commit: bool = True,
-        info: Optional[Dict[Any, Any]] = None,
+        info: Optional[_InfoType] = None,
         **kw: Any,
     ):
         r"""Construct a new :class:`.async_sessionmaker`.
diff --git a/lib/sqlalchemy/ext/compiler.py b/lib/sqlalchemy/ext/compiler.py
index b8265e88e..b74761fe4 100644
--- a/lib/sqlalchemy/ext/compiler.py
+++ b/lib/sqlalchemy/ext/compiler.py
@@ -229,18 +229,19 @@ A synopsis is as follows:
               raise TypeError("coalesce only supports two arguments on Oracle")
           return "nvl(%s)" % compiler.process(element.clauses, **kw)
 
-* :class:`.DDLElement` - The root of all DDL expressions,
-  like CREATE TABLE, ALTER TABLE, etc. Compilation of :class:`.DDLElement`
-  subclasses is issued by a :class:`.DDLCompiler` instead of a
-  :class:`.SQLCompiler`. :class:`.DDLElement` can also be used as an event hook
-  in conjunction with event hooks like :meth:`.DDLEvents.before_create` and
+* :class:`.ExecutableDDLElement` - The root of all DDL expressions,
+  like CREATE TABLE, ALTER TABLE, etc. Compilation of
+  :class:`.ExecutableDDLElement` subclasses is issued by a
+  :class:`.DDLCompiler` instead of a :class:`.SQLCompiler`.
+  :class:`.ExecutableDDLElement` can also be used as an event hook in
+  conjunction with event hooks like :meth:`.DDLEvents.before_create` and
   :meth:`.DDLEvents.after_create`, allowing the construct to be invoked
   automatically during CREATE TABLE and DROP TABLE sequences.
 
   .. seealso::
 
     :ref:`metadata_ddl_toplevel` - contains examples of associating
-    :class:`.DDL` objects (which are themselves :class:`.DDLElement`
+    :class:`.DDL` objects (which are themselves :class:`.ExecutableDDLElement`
     instances) with :class:`.DDLEvents` event hooks.
 
 * :class:`~sqlalchemy.sql.expression.Executable` - This is a mixin which
diff --git a/lib/sqlalchemy/ext/hybrid.py b/lib/sqlalchemy/ext/hybrid.py
index a0c7905d8..be872804e 100644
--- a/lib/sqlalchemy/ext/hybrid.py
+++ b/lib/sqlalchemy/ext/hybrid.py
@@ -808,7 +808,6 @@ from __future__ import annotations
 from typing import Any
 from typing import Callable
 from typing import cast
-from typing import Dict
 from typing import Generic
 from typing import List
 from typing import Optional
@@ -837,9 +836,11 @@ if TYPE_CHECKING:
     from ..sql._typing import _ColumnExpressionArgument
     from ..sql._typing import _DMLColumnArgument
     from ..sql._typing import _HasClauseElement
+    from ..sql._typing import _InfoType
     from ..sql.operators import OperatorType
     from ..sql.roles import ColumnsClauseRole
 
+
 _T = TypeVar("_T", bound=Any)
 _T_co = TypeVar("_T_co", bound=Any, covariant=True)
 _T_con = TypeVar("_T_con", bound=Any, contravariant=True)
@@ -1325,7 +1326,7 @@ class ExprComparator(Comparator[_T]):
         return getattr(self.expression, key)
 
     @util.non_memoized_property
-    def info(self) -> Dict[Any, Any]:
+    def info(self) -> _InfoType:
         return self.hybrid.info
 
     def _bulk_update_tuples(