diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/ext/compiler.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 51 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/requirements.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/testing/suite/test_insert.py | 28 |
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' |