Replace use of Executable.execute method

Resolve the following RemovedIn20Warning warning:

  The Executable.execute() method is considered legacy as of the 1.x
  series of SQLAlchemy and will be removed in 2.0. All statement
  execution in SQLAlchemy 2.0 is performed by the Connection.execute()
  method of Connection, or in the ORM by the Session.execute() method of
  Session.

Change-Id: Ie0acba4a315c85ec7236e44a22449e0ad920ca9b
Signed-off-by: Stephen Finucane <stephenfin@redhat.com>

2 files changed, 83 insertions, 80 deletions

diff --git a/oslo_db/sqlalchemy/utils.py b/oslo_db/sqlalchemy/utils.py
index 2ab7188..180b07f 100644
--- a/oslo_db/sqlalchemy/utils.py
+++ b/oslo_db/sqlalchemy/utils.py
@@ -569,11 +569,12 @@ def change_deleted_column_type_to_boolean(engine, table_name,
     finally:
         table.metadata.bind = None
 
-    engine.execute(
-        table.update().
-        where(table.c.deleted == table.c.id).
-        values(old_deleted=True)
-    )
+    with engine.connect() as conn:
+        conn.execute(
+            table.update().where(
+                table.c.deleted == table.c.id
+            ).values(old_deleted=True)
+        )
 
     table.metadata.bind = engine
     try:
@@ -607,39 +608,42 @@ def _change_deleted_column_type_to_boolean_sqlite(engine, table_name,
     # figure out how else to copy an arbitrary column schema
     constraints = [constraint._copy() for constraint in table.constraints]
 
-    meta = table.metadata
-    new_table = Table(table_name + "__tmp__", meta,
-                      *(columns + constraints))
-    new_table.create(engine)
+    with engine.connect() as conn:
+        meta = table.metadata
+        new_table = Table(
+            table_name + "__tmp__", meta,
+            *(columns + constraints))
+        new_table.create(conn)
 
-    indexes = []
-    for index in get_indexes(engine, table_name):
-        column_names = [new_table.c[c] for c in index['column_names']]
-        indexes.append(Index(index["name"], *column_names,
-                             unique=index["unique"]))
-
-    c_select = []
-    for c in table.c:
-        if c.name != "deleted":
-            c_select.append(c)
-        else:
-            c_select.append(table.c.deleted == table.c.id)
+        indexes = []
+        for index in get_indexes(engine, table_name):
+            column_names = [new_table.c[c] for c in index['column_names']]
+            indexes.append(
+                Index(index["name"], *column_names, unique=index["unique"])
+            )
+
+        c_select = []
+        for c in table.c:
+            if c.name != "deleted":
+                c_select.append(c)
+            else:
+                c_select.append(table.c.deleted == table.c.id)
 
-    table.drop(engine)
-    for index in indexes:
-        index.create(engine)
+        table.drop(conn)
+        for index in indexes:
+            index.create(conn)
 
-    table.metadata.bind = engine
-    try:
-        new_table.rename(table_name)
-    finally:
-        table.metadata.bind = None
-
-    engine.execute(
-        new_table.update().
-        where(new_table.c.deleted == new_table.c.id).
-        values(deleted=True)
-    )
+        table.metadata.bind = engine
+        try:
+            new_table.rename(table_name)
+        finally:
+            table.metadata.bind = None
+
+        conn.execute(
+            new_table.update().where(
+                new_table.c.deleted == new_table.c.id
+            ).values(deleted=True)
+        )
 
 
 @debtcollector.removals.remove(
@@ -664,21 +668,22 @@ def change_deleted_column_type_to_id_type(engine, table_name,
     finally:
         table.metadata.bind = None
 
-    deleted = True  # workaround for pyflakes
-    engine.execute(
-        table.update().
-        where(table.c.deleted == deleted).
-        values(new_deleted=table.c.id)
-    )
     table.metadata.bind = engine
     try:
-        table.c.deleted.drop()
-        table.c.new_deleted.alter(name="deleted")
+        with engine.connect() as conn:
+            deleted = True  # workaround for pyflakes
+            conn.execute(
+                table.update().where(
+                    table.c.deleted == deleted
+                ).values(new_deleted=table.c.id)
+            )
+            table.c.deleted.drop()
+            table.c.new_deleted.alter(name="deleted")
+
+            _restore_indexes_on_deleted_columns(conn, table_name, indexes)
     finally:
         table.metadata.bind = None
 
-    _restore_indexes_on_deleted_columns(engine, table_name, indexes)
-
 
 def _is_deleted_column_constraint(constraint):
     # NOTE(boris-42): There is no other way to check is CheckConstraint
@@ -731,40 +736,43 @@ def _change_deleted_column_type_to_id_type_sqlite(engine, table_name,
             # figure out how else to copy an arbitrary constraint schema
             constraints.append(constraint._copy())
 
-    new_table = Table(table_name + "__tmp__", meta,
-                      *(columns + constraints))
-    new_table.create(engine)
+    with engine.connect() as conn:
+        new_table = Table(
+            table_name + "__tmp__", meta,
+            *(columns + constraints))
+        new_table.create(conn)
 
-    indexes = []
-    for index in get_indexes(engine, table_name):
-        column_names = [new_table.c[c] for c in index['column_names']]
-        indexes.append(Index(index["name"], *column_names,
-                             unique=index["unique"]))
-
-    table.drop(engine)
-    for index in indexes:
-        index.create(engine)
+        indexes = []
+        for index in get_indexes(engine, table_name):
+            column_names = [new_table.c[c] for c in index['column_names']]
+            indexes.append(
+                Index(index["name"], *column_names, unique=index["unique"])
+            )
 
-    new_table.metadata.bind = engine
-    try:
-        new_table.rename(table_name)
-    finally:
-        new_table.metadata.bind = None
+        table.drop(conn)
+        for index in indexes:
+            index.create(conn)
 
-    deleted = True  # workaround for pyflakes
-    engine.execute(
-        new_table.update().
-        where(new_table.c.deleted == deleted).
-        values(deleted=new_table.c.id)
-    )
+        new_table.metadata.bind = engine
+        try:
+            new_table.rename(table_name)
+        finally:
+            new_table.metadata.bind = None
+
+        deleted = True  # workaround for pyflakes
+        conn.execute(
+            new_table.update().where(
+                new_table.c.deleted == deleted
+            ).values(deleted=new_table.c.id)
+        )
 
-    # NOTE(boris-42): Fix value of deleted column: False -> "" or 0.
-    deleted = False  # workaround for pyflakes
-    engine.execute(
-        new_table.update().
-        where(new_table.c.deleted == deleted).
-        values(deleted=default_deleted_value)
-    )
+        # NOTE(boris-42): Fix value of deleted column: False -> "" or 0.
+        deleted = False  # workaround for pyflakes
+        conn.execute(
+            new_table.update().where(
+                new_table.c.deleted == deleted
+            ).values(deleted=default_deleted_value)
+        )
 
 
 def get_db_connection_info(conn_pieces):
diff --git a/oslo_db/tests/fixtures.py b/oslo_db/tests/fixtures.py
index f14bc1c..561ebc5 100644
--- a/oslo_db/tests/fixtures.py
+++ b/oslo_db/tests/fixtures.py
@@ -54,11 +54,6 @@ class WarningsFixture(fixtures.Fixture):
 
         warnings.filterwarnings(
             'once',
-            message=r'The Executable.execute\(\) method is considered .*',
-            category=sqla_exc.SADeprecationWarning)
-
-        warnings.filterwarnings(
-            'once',
             message=r'The connection.execute\(\) method in SQLAlchemy 2.0 .*',
             category=sqla_exc.SADeprecationWarning)