diff options
| -rw-r--r-- | doc/build/changelog/changelog_11.rst | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/engine/default.py | 6 | ||||
| -rw-r--r-- | test/dialect/postgresql/test_on_conflict.py | 109 |
3 files changed, 111 insertions, 13 deletions
diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst index c5be9c7c2..afdade444 100644 --- a/doc/build/changelog/changelog_11.rst +++ b/doc/build/changelog/changelog_11.rst @@ -32,6 +32,15 @@ mechanism is invoked at all. .. change:: + :tags: bug, postgresql + :tickets: 3813 + + An adjustment to ON CONFLICT such that the "inserted_primary_key" + logic is able to accommodate the case where there's no INSERT or + UPDATE and there's no net change. The value comes out as None + in this case, rather than failing on an exception. + + .. change:: :tags: bug, orm :tickets: 3811 diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 733a89076..891103ee0 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -502,6 +502,7 @@ class DefaultExecutionContext(interfaces.ExecutionContext): compiled = None statement = None result_column_struct = None + returned_defaults = None _is_implicit_returning = False _is_explicit_returning = False @@ -917,10 +918,13 @@ class DefaultExecutionContext(interfaces.ExecutionContext): ] def _setup_ins_pk_from_implicit_returning(self, row): + if row is None: + self.inserted_primary_key = None + return + key_getter = self.compiled._key_getters_for_crud_column[2] table = self.compiled.statement.table compiled_params = self.compiled_parameters[0] - self.inserted_primary_key = [ row[col] if value is None else value for col, value in [ diff --git a/test/dialect/postgresql/test_on_conflict.py b/test/dialect/postgresql/test_on_conflict.py index 201287d62..eb0298bc2 100644 --- a/test/dialect/postgresql/test_on_conflict.py +++ b/test/dialect/postgresql/test_on_conflict.py @@ -59,14 +59,20 @@ class OnConflictTest(fixtures.TablesTest): users = self.tables.users with testing.db.connect() as conn: - conn.execute( + result = conn.execute( insert(users).on_conflict_do_nothing(), dict(id=1, name='name1') ) - conn.execute( + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) + + result = conn.execute( insert(users).on_conflict_do_nothing(), dict(id=1, name='name2') ) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) + eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), [(1, 'name1')] @@ -77,18 +83,24 @@ class OnConflictTest(fixtures.TablesTest): users = self.tables.users with testing.db.connect() as conn: - conn.execute( + result = conn.execute( insert(users) .on_conflict_do_nothing( index_elements=users.primary_key.columns), dict(id=1, name='name1') ) - conn.execute( + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) + + result = conn.execute( insert(users) .on_conflict_do_nothing( index_elements=users.primary_key.columns), dict(id=1, name='name2') ) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) + eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), [(1, 'name1')] @@ -104,7 +116,10 @@ class OnConflictTest(fixtures.TablesTest): i = i.on_conflict_do_update( index_elements=[users.c.id], set_=dict(name=i.excluded.name)) - conn.execute(i, dict(id=1, name='name1')) + result = conn.execute(i, dict(id=1, name='name1')) + + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), @@ -123,7 +138,10 @@ class OnConflictTest(fixtures.TablesTest): set_=dict(id=i.excluded.id, name=i.excluded.name) ) - conn.execute(i, dict(id=1, name='name2')) + result = conn.execute(i, dict(id=1, name='name2')) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) + eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), [(1, 'name2')] @@ -140,7 +158,9 @@ class OnConflictTest(fixtures.TablesTest): index_elements=users.primary_key.columns, set_=dict(name=i.excluded.name) ) - conn.execute(i, dict(id=1, name='name3')) + result = conn.execute(i, dict(id=1, name='name3')) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), @@ -159,7 +179,9 @@ class OnConflictTest(fixtures.TablesTest): set_=dict(id=i.excluded.id, name=i.excluded.name) ).values(id=1, name='name4') - conn.execute(i) + result = conn.execute(i) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), @@ -178,7 +200,9 @@ class OnConflictTest(fixtures.TablesTest): set_=dict(id=10, name="I'm a name") ).values(id=1, name='name4') - conn.execute(i) + result = conn.execute(i) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) eq_( conn.execute( @@ -222,10 +246,12 @@ class OnConflictTest(fixtures.TablesTest): name=i.excluded.name, login_email=i.excluded.login_email) ) - conn.execute(i, dict( + result = conn.execute(i, dict( id=1, name='name2', login_email='name1@gmail.com', lets_index_this='not') ) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, None) eq_( conn.execute(users.select().where(users.c.id == 1)).fetchall(), @@ -246,10 +272,12 @@ class OnConflictTest(fixtures.TablesTest): login_email=i.excluded.login_email) ) # note: lets_index_this value totally ignored in SET clause. - conn.execute(i, dict( + result = conn.execute(i, dict( id=42, name='nameunique', login_email='name2@gmail.com', lets_index_this='unique') ) + eq_(result.inserted_primary_key, [42]) + eq_(result.returned_defaults, None) eq_( conn.execute( @@ -275,10 +303,12 @@ class OnConflictTest(fixtures.TablesTest): ) # note: lets_index_this value totally ignored in SET clause. - conn.execute(i, dict( + result = conn.execute(i, dict( id=43, name='nameunique2', login_email='name2@gmail.com', lets_index_this='unique') ) + eq_(result.inserted_primary_key, [43]) + eq_(result.returned_defaults, None) eq_( conn.execute( @@ -288,6 +318,36 @@ class OnConflictTest(fixtures.TablesTest): [(43, 'nameunique2', 'name2@gmail.com', 'not')] ) + def test_on_conflict_do_update_exotic_targets_four_no_pk(self): + users = self.tables.users_xtra + + with testing.db.connect() as conn: + self._exotic_targets_fixture(conn) + # try unique constraint by name: cause an + # upsert on target login_email, not id + i = insert(users) + i = i.on_conflict_do_update( + index_elements=[users.c.login_email], + set_=dict( + id=i.excluded.id, name=i.excluded.name, + login_email=i.excluded.login_email) + ) + + result = conn.execute(i, dict( + name='name3', + login_email='name1@gmail.com') + ) + eq_(result.inserted_primary_key, [1]) + eq_(result.returned_defaults, (1, )) + + eq_( + conn.execute(users.select().order_by(users.c.id)).fetchall(), + [ + (1, 'name3', 'name1@gmail.com', 'not'), + (2, 'name2', 'name2@gmail.com', 'not') + ] + ) + def test_on_conflict_do_update_exotic_targets_five(self): users = self.tables.users_xtra @@ -310,3 +370,28 @@ class OnConflictTest(fixtures.TablesTest): id=1, name='namebogus', login_email='bogus@gmail.com', lets_index_this='bogus') ) + + def test_on_conflict_do_update_no_row_actually_affected(self): + users = self.tables.users_xtra + + with testing.db.connect() as conn: + self._exotic_targets_fixture(conn) + i = insert(users) + i = i.on_conflict_do_update( + index_elements=[users.c.login_email], + set_=dict(name='new_name'), + where=(i.excluded.name == 'other_name') + ) + result = conn.execute( + i, dict(name='name2', login_email='name1@gmail.com')) + + eq_(result.returned_defaults, None) + eq_(result.inserted_primary_key, None) + + eq_( + conn.execute(users.select()).fetchall(), + [ + (1, 'name1', 'name1@gmail.com', 'not'), + (2, 'name2', 'name2@gmail.com', 'not') + ] + ) |