From 71ca494f518658676b532afaf84a4cc93025dbbb Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 20 Aug 2014 20:14:20 -0400 Subject: - The INSERT...FROM SELECT construct now implies ``inline=True`` on :class:`.Insert`. This helps to fix a bug where an INSERT...FROM SELECT construct would inadvertently be compiled as "implicit returning" on supporting backends, which would cause breakage in the case of an INSERT that inserts zero rows (as implicit returning expects a row), as well as arbitrary return data in the case of an INSERT that inserts multiple rows (e.g. only the first row of many). A similar change is also applied to an INSERT..VALUES with multiple parameter sets; implicit RETURNING will no longer emit for this statement either. As both of these constructs deal with varible numbers of rows, the :attr:`.ResultProxy.inserted_primary_key` accessor does not apply. Previously, there was a documentation note that one may prefer ``inline=True`` with INSERT..FROM SELECT as some databases don't support returning and therefore can't do "implicit" returning, but there's no reason an INSERT...FROM SELECT needs implicit returning in any case. Regular explicit :meth:`.Insert.returning` should be used to return variable numbers of result rows if inserted data is needed. fixes #3169 --- test/sql/test_insert.py | 109 +++++++++++++++++++++++++++++++++++++++++++++++- test/sql/test_query.py | 7 ++++ 2 files changed, 114 insertions(+), 2 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_insert.py b/test/sql/test_insert.py index d59d79d89..d2fba5862 100644 --- a/test/sql/test_insert.py +++ b/test/sql/test_insert.py @@ -17,7 +17,7 @@ class _InsertTestBase(object): Column('name', String(30)), Column('description', String(30))) Table('myothertable', metadata, - Column('otherid', Integer), + Column('otherid', Integer, primary_key=True), Column('othername', String(30))) @@ -138,6 +138,23 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): dialect=default.DefaultDialect() ) + def test_insert_from_select_returning(self): + table1 = self.tables.mytable + sel = select([table1.c.myid, table1.c.name]).where( + table1.c.name == 'foo') + ins = self.tables.myothertable.insert().\ + from_select(("otherid", "othername"), sel).returning( + self.tables.myothertable.c.otherid + ) + self.assert_compile( + ins, + "INSERT INTO myothertable (otherid, othername) " + "SELECT mytable.myid, mytable.name FROM mytable " + "WHERE mytable.name = %(name_1)s RETURNING myothertable.otherid", + checkparams={"name_1": "foo"}, + dialect="postgresql" + ) + def test_insert_from_select_select(self): table1 = self.tables.mytable sel = select([table1.c.myid, table1.c.name]).where( @@ -230,7 +247,7 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ) ins = mytable.insert().\ from_select( - [mytable.c.name, mytable.c.description], sel) + [mytable.c.name, mytable.c.description], sel) self.assert_compile( ins, "INSERT INTO mytable (name, description) " @@ -254,6 +271,94 @@ class InsertTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): ) +class InsertImplicitReturningTest( + _InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): + __dialect__ = postgresql.dialect(implicit_returning=True) + + def test_insert_select(self): + table1 = self.tables.mytable + sel = select([table1.c.myid, table1.c.name]).where( + table1.c.name == 'foo') + ins = self.tables.myothertable.insert().\ + from_select(("otherid", "othername"), sel) + self.assert_compile( + ins, + "INSERT INTO myothertable (otherid, othername) " + "SELECT mytable.myid, mytable.name FROM mytable " + "WHERE mytable.name = %(name_1)s", + checkparams={"name_1": "foo"} + ) + + def test_insert_select_return_defaults(self): + table1 = self.tables.mytable + sel = select([table1.c.myid, table1.c.name]).where( + table1.c.name == 'foo') + ins = self.tables.myothertable.insert().\ + from_select(("otherid", "othername"), sel).\ + return_defaults(self.tables.myothertable.c.otherid) + self.assert_compile( + ins, + "INSERT INTO myothertable (otherid, othername) " + "SELECT mytable.myid, mytable.name FROM mytable " + "WHERE mytable.name = %(name_1)s", + checkparams={"name_1": "foo"} + ) + + def test_insert_multiple_values(self): + ins = self.tables.myothertable.insert().values([ + {"othername": "foo"}, + {"othername": "bar"}, + ]) + self.assert_compile( + ins, + "INSERT INTO myothertable (othername) " + "VALUES (%(othername_0)s), " + "(%(othername_1)s)", + checkparams={ + 'othername_1': 'bar', + 'othername_0': 'foo'} + ) + + def test_insert_multiple_values_return_defaults(self): + # TODO: not sure if this should raise an + # error or what + ins = self.tables.myothertable.insert().values([ + {"othername": "foo"}, + {"othername": "bar"}, + ]).return_defaults(self.tables.myothertable.c.otherid) + self.assert_compile( + ins, + "INSERT INTO myothertable (othername) " + "VALUES (%(othername_0)s), " + "(%(othername_1)s)", + checkparams={ + 'othername_1': 'bar', + 'othername_0': 'foo'} + ) + + def test_insert_single_list_values(self): + ins = self.tables.myothertable.insert().values([ + {"othername": "foo"}, + ]) + self.assert_compile( + ins, + "INSERT INTO myothertable (othername) " + "VALUES (%(othername_0)s)", + checkparams={'othername_0': 'foo'} + ) + + def test_insert_single_element_values(self): + ins = self.tables.myothertable.insert().values( + {"othername": "foo"}, + ) + self.assert_compile( + ins, + "INSERT INTO myothertable (othername) " + "VALUES (%(othername)s) RETURNING myothertable.otherid", + checkparams={'othername': 'foo'} + ) + + class EmptyTest(_InsertTestBase, fixtures.TablesTest, AssertsCompiledSQL): __dialect__ = 'default' diff --git a/test/sql/test_query.py b/test/sql/test_query.py index a475b899f..2075bcecf 100644 --- a/test/sql/test_query.py +++ b/test/sql/test_query.py @@ -276,6 +276,13 @@ class QueryTest(fixtures.TestBase): r = t6.insert().values(manual_id=id).execute() eq_(r.inserted_primary_key, [12, 1]) + def test_implicit_id_insert_select(self): + stmt = users.insert().from_select( + (users.c.user_id, users.c.user_name), + users.select().where(users.c.user_id == 20)) + + testing.db.execute(stmt) + def test_row_iteration(self): users.insert().execute( {'user_id': 7, 'user_name': 'jack'}, -- cgit v1.2.1