From c6b1d24fe71c22e4d2117d084f06df3597671985 Mon Sep 17 00:00:00 2001 From: Gord Thompson Date: Sun, 25 Jul 2021 13:16:31 -0600 Subject: [PATCH] Modernize tests - dml_whereclause Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled. Fixes: #6812 Change-Id: I0a031e728527a1c3382848b6ddc793939362b128 --- doc/build/changelog/unreleased_14/6812.rst | 8 ++++++++ lib/sqlalchemy/orm/dependency.py | 2 +- lib/sqlalchemy/testing/suite/test_rowcount.py | 13 ++++++++---- lib/sqlalchemy/testing/warnings.py | 2 -- test/dialect/mssql/test_compiler.py | 10 +++++----- test/dialect/mssql/test_deprecations.py | 20 +------------------ test/dialect/mssql/test_query.py | 2 +- test/dialect/postgresql/test_types.py | 8 ++++++-- test/orm/test_expire.py | 8 +++++--- test/orm/test_transaction.py | 4 +++- test/sql/test_cte.py | 2 +- test/sql/test_returning.py | 12 ++++++----- 12 files changed, 47 insertions(+), 44 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/6812.rst diff --git a/doc/build/changelog/unreleased_14/6812.rst b/doc/build/changelog/unreleased_14/6812.rst new file mode 100644 index 0000000000..d91db2e90e --- /dev/null +++ b/doc/build/changelog/unreleased_14/6812.rst @@ -0,0 +1,8 @@ +.. change:: + :tags: bug, orm + :tickets: 6812 + + Fixed issue where the unit of work would internally use a 2.0-deprecated + SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 + were enabled. + diff --git a/lib/sqlalchemy/orm/dependency.py b/lib/sqlalchemy/orm/dependency.py index eae50a6dbe..27919050ec 100644 --- a/lib/sqlalchemy/orm/dependency.py +++ b/lib/sqlalchemy/orm/dependency.py @@ -1216,7 +1216,7 @@ class ManyToManyDP(DependencyProcessor): if secondary_update: associationrow = secondary_update[0] - statement = self.secondary.update( + statement = self.secondary.update().where( sql.and_( *[ c == sql.bindparam("old_" + c.key, type_=c.type) diff --git a/lib/sqlalchemy/testing/suite/test_rowcount.py b/lib/sqlalchemy/testing/suite/test_rowcount.py index 504ac13a5a..82e831f496 100644 --- a/lib/sqlalchemy/testing/suite/test_rowcount.py +++ b/lib/sqlalchemy/testing/suite/test_rowcount.py @@ -69,7 +69,8 @@ class RowCountTest(fixtures.TablesTest): # WHERE matches 3, 3 rows changed department = employees_table.c.department r = connection.execute( - employees_table.update(department == "C"), {"department": "Z"} + employees_table.update().where(department == "C"), + {"department": "Z"}, ) assert r.rowcount == 3 @@ -80,7 +81,8 @@ class RowCountTest(fixtures.TablesTest): department = employees_table.c.department r = connection.execute( - employees_table.update(department == "C"), {"department": "C"} + employees_table.update().where(department == "C"), + {"department": "C"}, ) eq_(r.rowcount, 3) @@ -90,7 +92,8 @@ class RowCountTest(fixtures.TablesTest): department = employees_table.c.department stmt = ( - employees_table.update(department == "C") + employees_table.update() + .where(department == "C") .values(name=employees_table.c.department + "Z") .return_defaults() ) @@ -117,7 +120,9 @@ class RowCountTest(fixtures.TablesTest): # WHERE matches 3, 3 rows deleted department = employees_table.c.department - r = connection.execute(employees_table.delete(department == "C")) + r = connection.execute( + employees_table.delete().where(department == "C") + ) eq_(r.rowcount, 3) @testing.requires.sane_multi_rowcount diff --git a/lib/sqlalchemy/testing/warnings.py b/lib/sqlalchemy/testing/warnings.py index 54cf7589f2..39dc7da58d 100644 --- a/lib/sqlalchemy/testing/warnings.py +++ b/lib/sqlalchemy/testing/warnings.py @@ -76,8 +76,6 @@ def setup_filters(): # # DML # - r"The (?:update|delete).whereclause parameter will be removed in " - "SQLAlchemy 2.0.", r"The (?:insert|update).values parameter will be removed in " "SQLAlchemy 2.0.", r"The update.preserve_parameter_order parameter will be removed in " diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index b0434115d6..c512ae4418 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -137,7 +137,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): def test_update(self): t = table("sometable", column("somecolumn")) self.assert_compile( - t.update(t.c.somecolumn == 7), + t.update().where(t.c.somecolumn == 7), "UPDATE sometable SET somecolumn=:somecolum" "n WHERE sometable.somecolumn = " ":somecolumn_1", @@ -703,7 +703,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): schema="paj", ) self.assert_compile( - tbl.delete(tbl.c.id == 1), + tbl.delete().where(tbl.c.id == 1), "DELETE FROM paj.test WHERE paj.test.id = " ":id_1", ) s = select(tbl.c.id).where(tbl.c.id == 1) @@ -723,7 +723,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): schema="banana.paj", ) self.assert_compile( - tbl.delete(tbl.c.id == 1), + tbl.delete().where(tbl.c.id == 1), "DELETE FROM banana.paj.test WHERE " "banana.paj.test.id = :id_1", ) s = select(tbl.c.id).where(tbl.c.id == 1) @@ -744,7 +744,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): schema="banana split.paj", ) self.assert_compile( - tbl.delete(tbl.c.id == 1), + tbl.delete().where(tbl.c.id == 1), "DELETE FROM [banana split].paj.test WHERE " "[banana split].paj.test.id = :id_1", ) @@ -767,7 +767,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): schema="banana split.paj with a space", ) self.assert_compile( - tbl.delete(tbl.c.id == 1), + tbl.delete().where(tbl.c.id == 1), "DELETE FROM [banana split].[paj with a " "space].test WHERE [banana split].[paj " "with a space].test.id = :id_1", diff --git a/test/dialect/mssql/test_deprecations.py b/test/dialect/mssql/test_deprecations.py index 354107e811..aecb813fa1 100644 --- a/test/dialect/mssql/test_deprecations.py +++ b/test/dialect/mssql/test_deprecations.py @@ -212,24 +212,6 @@ class LegacySchemaAliasingBackendTest( conn.execute(tbl.insert(), {"id": 1}) eq_(conn.scalar(tbl.select()), 1) - @testing.provide_metadata - def test_delete_schema(self, connection): - meta = self.metadata - - is_(connection.dialect.legacy_schema_aliasing, False) - - tbl = Table( - "test", - meta, - Column("id", Integer, primary_key=True), - schema=testing.config.test_schema, - ) - tbl.create(connection) - connection.execute(tbl.insert(), {"id": 1}) - eq_(connection.scalar(tbl.select()), 1) - connection.execute(tbl.delete(tbl.c.id == 1)) - eq_(connection.scalar(tbl.select()), None) - @testing.provide_metadata def test_delete_schema_legacy(self): meta = self.metadata @@ -249,5 +231,5 @@ class LegacySchemaAliasingBackendTest( tbl.create(conn) conn.execute(tbl.insert(), {"id": 1}) eq_(conn.scalar(tbl.select()), 1) - conn.execute(tbl.delete(tbl.c.id == 1)) + conn.execute(tbl.delete().where(tbl.c.id == 1)) eq_(conn.scalar(tbl.select()), None) diff --git a/test/dialect/mssql/test_query.py b/test/dialect/mssql/test_query.py index 6952b26a90..e5e3cd3ad2 100644 --- a/test/dialect/mssql/test_query.py +++ b/test/dialect/mssql/test_query.py @@ -380,7 +380,7 @@ class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase): tbl.create(connection) connection.execute(tbl.insert(), {"id": 1}) eq_(connection.scalar(tbl.select()), 1) - connection.execute(tbl.delete(tbl.c.id == 1)) + connection.execute(tbl.delete().where(tbl.c.id == 1)) eq_(connection.scalar(tbl.select()), None) @testing.provide_metadata diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py index f667a0223e..4f87603f3e 100644 --- a/test/dialect/postgresql/test_types.py +++ b/test/dialect/postgresql/test_types.py @@ -1019,7 +1019,9 @@ class TimezoneTest(fixtures.TablesTest): row[0].tzinfo.utcoffset(row[0]), ) result = connection.execute( - tztable.update(tztable.c.id == 1).returning(tztable.c.date), + tztable.update() + .where(tztable.c.id == 1) + .returning(tztable.c.date), dict( name="newname", ), @@ -1043,7 +1045,9 @@ class TimezoneTest(fixtures.TablesTest): eq_(row[0], somedate) eq_(row[0].tzinfo, None) result = connection.execute( - notztable.update(notztable.c.id == 1).returning(notztable.c.date), + notztable.update() + .where(notztable.c.id == 1) + .returning(notztable.c.date), dict( name="newname", ), diff --git a/test/orm/test_expire.py b/test/orm/test_expire.py index 0e356fe783..497dbdb037 100644 --- a/test/orm/test_expire.py +++ b/test/orm/test_expire.py @@ -68,7 +68,9 @@ class ExpireTest(_fixtures.FixtureTest): u.name = "foo" sess.flush() # change the value in the DB - sess.execute(users.update(users.c.id == 7, values=dict(name="jack"))) + sess.execute( + users.update(values=dict(name="jack")).where(users.c.id == 7) + ) sess.expire(u) # object isn't refreshed yet, using dict to bypass trigger assert u.__dict__.get("name") != "jack" @@ -936,7 +938,7 @@ class ExpireTest(_fixtures.FixtureTest): sess.expire(u) assert "name" not in u.__dict__ - sess.execute(users.update(users.c.id == 7), dict(name="jack2")) + sess.execute(users.update().where(users.c.id == 7), dict(name="jack2")) assert u.name == "jack2" assert u.uname == "jack2" assert "name" in u.__dict__ @@ -961,7 +963,7 @@ class ExpireTest(_fixtures.FixtureTest): assert attributes.instance_state(o).dict["isopen"] == 1 sess.execute( - orders.update(orders.c.id == 3), + orders.update().where(orders.c.id == 3), dict(description="order 3 modified"), ) diff --git a/test/orm/test_transaction.py b/test/orm/test_transaction.py index 25f5d22ce8..ad57a80316 100644 --- a/test/orm/test_transaction.py +++ b/test/orm/test_transaction.py @@ -1945,7 +1945,9 @@ class AccountingFlagsTest(_LocalFixture): sess.add(u1) sess.commit() - sess.execute(users.update(users.c.name == "ed").values(name="edward")) + sess.execute( + users.update().where(users.c.name == "ed").values(name="edward") + ) assert u1.name == "ed" sess.expire_all() diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index f1d27aa8f1..77905cd896 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -1504,7 +1504,7 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): ) ) cte = q.cte("deldup") - stmt = delete(cte, text("RN > 1")) + stmt = delete(cte).where(text("RN > 1")) eq_(stmt.compile().execution_options["autocommit"], True) self.assert_compile( diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py index a0d69e782c..10bf3beb6f 100644 --- a/test/sql/test_returning.py +++ b/test/sql/test_returning.py @@ -189,9 +189,10 @@ class ReturningTest(fixtures.TablesTest, AssertsExecutionResults): ) result = connection.execute( - table.update(table.c.persons > 4, dict(full=True)).returning( - table.c.id - ) + table.update() + .values(dict(full=True)) + .where(table.c.persons > 4) + .returning(table.c.id) ) eq_(result.fetchall(), [(1,)]) @@ -291,7 +292,8 @@ class ReturningTest(fixtures.TablesTest, AssertsExecutionResults): ) result = connection.execute( - table.update(table.c.persons > 2) + table.update() + .where(table.c.persons > 2) .values(full=True) .returning(table.c.id, table.c.full) ) @@ -369,7 +371,7 @@ class ReturningTest(fixtures.TablesTest, AssertsExecutionResults): ) result = connection.execute( - table.delete(table.c.persons > 4).returning(table.c.id) + table.delete().where(table.c.persons > 4).returning(table.c.id) ) eq_(result.fetchall(), [(1,)]) -- 2.47.2