- Added new method to the :func:`.insert` construct

:meth:`.Insert.from_select`.  Given a list of columns and
a selectable, renders ``INSERT INTO (table) (columns) SELECT ..``.
While this feature is highlighted as part of 0.9 it is also
backported to 0.8.3. [ticket:722]
- The :func:`.update`, :func:`.insert`, and :func:`.delete` constructs
will now interpret ORM entities as FROM clauses to be operated upon,
in the same way that select() already does.  Also in 0.8.3.

5 files changed, 92 insertions, 3 deletions

diff --git a/lib/sqlalchemy/ext/compiler.py b/lib/sqlalchemy/ext/compiler.py
index 002b2c037..703475de7 100644
--- a/lib/sqlalchemy/ext/compiler.py
+++ b/lib/sqlalchemy/ext/compiler.py
@@ -96,6 +96,12 @@ Produces::
 
 .. note::
 
+    The above ``InsertFromSelect`` construct is only an example, this actual
+    functionality is already available using the
+    :meth:`.Insert.from_select` method.
+
+.. note::
+
    The above ``InsertFromSelect`` construct probably wants to have "autocommit"
    enabled.  See :ref:`enabling_compiled_autocommit` for this step.
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 7770c7fc7..93dc3fc4d 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1546,7 +1546,9 @@ class SQLCompiler(engine.Compiled):
             if self.returning_precedes_values:
                 text += " " + returning_clause
 
-        if not colparams and supports_default_values:
+        if insert_stmt.select is not None:
+            text += " %s" % self.process(insert_stmt.select, **kw)
+        elif not colparams and supports_default_values:
             text += " DEFAULT VALUES"
         elif insert_stmt._has_multi_parameters:
             text += " VALUES %s" % (
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 37c0ac65c..6ee110e9c 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1599,6 +1599,14 @@ def _interpret_as_from(element):
         return insp.selectable
     raise exc.ArgumentError("FROM expression expected")
 
+def _interpret_as_select(element):
+    element = _interpret_as_from(element)
+    if isinstance(element, Alias):
+        element = element.original
+    if not isinstance(element, Select):
+        element = element.select()
+    return element
+
 
 def _const_expr(element):
     if isinstance(element, (Null, False_, True_)):
@@ -6237,9 +6245,10 @@ class ValuesBase(UpdateBase):
 
     _supports_multi_parameters = False
     _has_multi_parameters = False
+    select = None
 
     def __init__(self, table, values, prefixes):
-        self.table = table
+        self.table = _interpret_as_from(table)
         self.parameters, self._has_multi_parameters = \
                             self._process_colparams(values)
         if prefixes:
@@ -6338,6 +6347,9 @@ class ValuesBase(UpdateBase):
             :func:`~.expression.update` - produce an ``UPDATE`` statement
 
         """
+        if self.select is not None:
+            raise exc.InvalidRequestError(
+                        "This construct already inserts from a SELECT")
         if self._has_multi_parameters and kwargs:
             raise exc.InvalidRequestError(
                         "This construct already has multiple parameter sets.")
@@ -6418,9 +6430,44 @@ class Insert(ValuesBase):
         else:
             return ()
 
+    @_generative
+    def from_select(self, names, select):
+        """Return a new :class:`.Insert` construct which represents
+        an ``INSERT...FROM SELECT`` statement.
+
+        e.g.::
+
+            sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5)
+            ins = table2.insert().from_select(['a', 'b'], sel)
+
+        :param names: a sequence of string column names or :class:`.Column`
+         objects representing the target columns.
+        :param select: a :func:`.select` construct, :class:`.FromClause`
+         or other construct which resolves into a :class:`.FromClause`,
+         such as an ORM :class:`.Query` object, etc.  The order of
+         columns returned from this FROM clause should correspond to the
+         order of columns sent as the ``names`` parameter;  while this
+         is not checked before passing along to the database, the database
+         would normally raise an exception if these column lists don't
+         correspond.
+
+        .. versionadded:: 0.8.3
+
+        """
+        if self.parameters:
+            raise exc.InvalidRequestError(
+                        "This construct already inserts value expressions")
+
+        self.parameters, self._has_multi_parameters = \
+                self._process_colparams(dict((n, null()) for n in names))
+
+        self.select = _interpret_as_select(select)
+
     def _copy_internals(self, clone=_clone, **kw):
         # TODO: coverage
         self.parameters = self.parameters.copy()
+        if self.select is not None:
+            self.select = _clone(self.select)
 
 
 class Update(ValuesBase):
@@ -6507,7 +6554,7 @@ class Delete(UpdateBase):
             prefixes=None,
             **kwargs):
         self._bind = bind
-        self.table = table
+        self.table = _interpret_as_from(table)
         self._returning = returning
 
         if prefixes:
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 3a299b0db..d301dc69f 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -144,6 +144,12 @@ class SuiteRequirements(Requirements):
                 )
 
     @property
+    def insert_from_select(self):
+        """target platform supports INSERT from a SELECT."""
+
+        return exclusions.open()
+
+    @property
     def returning(self):
         """target platform supports RETURNING."""
 
diff --git a/lib/sqlalchemy/testing/suite/test_insert.py b/lib/sqlalchemy/testing/suite/test_insert.py
index a00fde312..ef05291b5 100644
--- a/lib/sqlalchemy/testing/suite/test_insert.py
+++ b/lib/sqlalchemy/testing/suite/test_insert.py
@@ -121,6 +121,34 @@ class InsertBehaviorTest(fixtures.TablesTest):
 
         assert len(r.fetchall())
 
+    @requirements.insert_from_select
+    def test_insert_from_select(self):
+        table = self.tables.autoinc_pk
+        config.db.execute(
+                table.insert(),
+                [
+                    dict(data="data1"),
+                    dict(data="data2"),
+                    dict(data="data3"),
+                ]
+        )
+
+
+        config.db.execute(
+                table.insert().
+                    from_select(
+                        ("data",), select([table.c.data]).where(
+                                table.c.data.in_(["data2", "data3"]))
+                    ),
+        )
+
+        eq_(
+            config.db.execute(
+                select([table.c.data]).order_by(table.c.data)
+            ).fetchall(),
+            [("data1", ), ("data2", ), ("data2", ),
+                ("data3", ), ("data3", )]
+        )
 
 class ReturningTest(fixtures.TablesTest):
     run_deletes = 'each'