pep484 ORM / SQL result support

after some experimentation it seems mypy is more amenable
to the generic types being fully integrated rather than
having separate spin-off types.   so key structures
like Result, Row, Select become generic.  For DML
Insert, Update, Delete, these are spun into type-specific
subclasses ReturningInsert, ReturningUpdate, ReturningDelete,
which is fine since the "row-ness" of these constructs
doesn't happen until returning() is called in any case.

a Tuple based model is then integrated so that these
objects can carry along information about their return
types.  Overloads at the .execute() level carry through
the Tuple from the invoked object to the result.

To suit the issue of AliasedClass generating attributes
that are dynamic, experimented with a custom subclass
AsAliased, but then just settled on having aliased()
lie to the type checker and return `Type[_O]`, essentially.
will need some type-related accessors for with_polymorphic()
also.

Additionally, identified an issue in Update when used
"mysql style" against a join(), it basically doesn't work
if asked to UPDATE two tables on the same column name.
added an error message to the specific condition where
it happens with a very non-specific error message that we
hit a thing we can't do right now, suggest multi-table
update as a possible cause.

Change-Id: I5eff7eefe1d6166ee74160b2785c5e6a81fa8b95

1 files changed, 24 insertions, 12 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 34d5127ab..a29561291 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -54,6 +54,7 @@ from .base import _clone
 from .base import _generative
 from .base import _NoArg
 from .base import Executable
+from .base import Generative
 from .base import HasMemoized
 from .base import Immutable
 from .base import NO_ARG
@@ -94,10 +95,7 @@ if typing.TYPE_CHECKING:
     from .selectable import _SelectIterable
     from .selectable import FromClause
     from .selectable import NamedFromClause
-    from .selectable import ReturnsRows
     from .selectable import Select
-    from .selectable import TableClause
-    from .sqltypes import Boolean
     from .sqltypes import TupleType
     from .type_api import TypeEngine
     from .visitors import _CloneCallableType
@@ -122,7 +120,9 @@ _NT = TypeVar("_NT", bound="_NUMERIC")
 _NMT = TypeVar("_NMT", bound="_NUMBER")
 
 
-def literal(value, type_=None):
+def literal(
+    value: Any, type_: Optional[_TypeEngineArgument[_T]] = None
+) -> BindParameter[_T]:
     r"""Return a literal clause, bound to a bind parameter.
 
     Literal clauses are created automatically when non-
@@ -144,7 +144,9 @@ def literal(value, type_=None):
     return coercions.expect(roles.LiteralValueRole, value, type_=type_)
 
 
-def literal_column(text, type_=None):
+def literal_column(
+    text: str, type_: Optional[_TypeEngineArgument[_T]] = None
+) -> ColumnClause[_T]:
     r"""Produce a :class:`.ColumnClause` object that has the
     :paramref:`_expression.column.is_literal` flag set to True.
 
@@ -316,6 +318,7 @@ class ClauseElement(
     is_selectable = False
     is_dml = False
     _is_column_element = False
+    _is_keyed_column_element = False
     _is_table = False
     _is_textual = False
     _is_from_clause = False
@@ -342,7 +345,7 @@ class ClauseElement(
     if typing.TYPE_CHECKING:
 
         def get_children(
-            self, omit_attrs: typing_Tuple[str, ...] = ..., **kw: Any
+            self, *, omit_attrs: typing_Tuple[str, ...] = ..., **kw: Any
         ) -> Iterable[ClauseElement]:
             ...
 
@@ -455,7 +458,7 @@ class ClauseElement(
         connection: Connection,
         distilled_params: _CoreMultiExecuteParams,
         execution_options: _ExecuteOptions,
-    ) -> Result:
+    ) -> Result[Any]:
         if self.supports_execution:
             if TYPE_CHECKING:
                 assert isinstance(self, Executable)
@@ -833,13 +836,13 @@ class SQLCoreOperations(Generic[_T], ColumnOperators, TypingOnly):
 
         def in_(
             self,
-            other: Union[Sequence[Any], BindParameter[Any], Select],
+            other: Union[Sequence[Any], BindParameter[Any], Select[Any]],
         ) -> BinaryExpression[bool]:
             ...
 
         def not_in(
             self,
-            other: Union[Sequence[Any], BindParameter[Any], Select],
+            other: Union[Sequence[Any], BindParameter[Any], Select[Any]],
         ) -> BinaryExpression[bool]:
             ...
 
@@ -1699,6 +1702,14 @@ class ColumnElement(
         return self._anon_label(label, add_hash=idx)
 
 
+class KeyedColumnElement(ColumnElement[_T]):
+    """ColumnElement where ``.key`` is non-None."""
+
+    _is_keyed_column_element = True
+
+    key: str
+
+
 class WrapsColumnExpression(ColumnElement[_T]):
     """Mixin that defines a :class:`_expression.ColumnElement`
     as a wrapper with special
@@ -1760,7 +1771,7 @@ class WrapsColumnExpression(ColumnElement[_T]):
 SelfBindParameter = TypeVar("SelfBindParameter", bound="BindParameter[Any]")
 
 
-class BindParameter(roles.InElementRole, ColumnElement[_T]):
+class BindParameter(roles.InElementRole, KeyedColumnElement[_T]):
     r"""Represent a "bound expression".
 
     :class:`.BindParameter` is invoked explicitly using the
@@ -2073,6 +2084,7 @@ class TextClause(
     roles.FromClauseRole,
     roles.SelectStatementRole,
     roles.InElementRole,
+    Generative,
     Executable,
     DQLDMLClauseElement,
     roles.BinaryElementRole[Any],
@@ -4160,7 +4172,7 @@ class FunctionFilter(ColumnElement[_T]):
         )
 
 
-class NamedColumn(ColumnElement[_T]):
+class NamedColumn(KeyedColumnElement[_T]):
     is_literal = False
     table: Optional[FromClause] = None
     name: str
@@ -4502,7 +4514,7 @@ class ColumnClause(
 
         self.is_literal = is_literal
 
-    def get_children(self, column_tables=False, **kw):
+    def get_children(self, *, column_tables=False, **kw):
         # override base get_children() to not return the Table
         # or selectable that is parent to this column.  Traversals
         # expect the columns of tables and subqueries to be leaf nodes.