Merge "correct for "autocommit" deprecation warning"

12 files changed, 171 insertions, 165 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/provision.py b/lib/sqlalchemy/dialects/mysql/provision.py
index c1d83bbb7..50b6e3c85 100644
--- a/lib/sqlalchemy/dialects/mysql/provision.py
+++ b/lib/sqlalchemy/dialects/mysql/provision.py
@@ -41,12 +41,13 @@ def generate_driver_url(url, driver, query_str):
 
 @create_db.for_db("mysql", "mariadb")
 def _mysql_create_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         try:
             _mysql_drop_db(cfg, conn, ident)
         except Exception:
             pass
 
+    with eng.begin() as conn:
         conn.exec_driver_sql(
             "CREATE DATABASE %s CHARACTER SET utf8mb4" % ident
         )
@@ -66,7 +67,7 @@ def _mysql_configure_follower(config, ident):
 
 @drop_db.for_db("mysql", "mariadb")
 def _mysql_drop_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         conn.exec_driver_sql("DROP DATABASE %s_test_schema" % ident)
         conn.exec_driver_sql("DROP DATABASE %s_test_schema_2" % ident)
         conn.exec_driver_sql("DROP DATABASE %s" % ident)
diff --git a/lib/sqlalchemy/dialects/oracle/provision.py b/lib/sqlalchemy/dialects/oracle/provision.py
index d19dfc9fe..aadc2c5a9 100644
--- a/lib/sqlalchemy/dialects/oracle/provision.py
+++ b/lib/sqlalchemy/dialects/oracle/provision.py
@@ -17,7 +17,7 @@ def _oracle_create_db(cfg, eng, ident):
     # NOTE: make sure you've run "ALTER DATABASE default tablespace users" or
     # similar, so that the default tablespace is not "system"; reflection will
     # fail otherwise
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         conn.exec_driver_sql("create user %s identified by xe" % ident)
         conn.exec_driver_sql("create user %s_ts1 identified by xe" % ident)
         conn.exec_driver_sql("create user %s_ts2 identified by xe" % ident)
@@ -45,7 +45,7 @@ def _ora_drop_ignore(conn, dbname):
 
 @drop_db.for_db("oracle")
 def _oracle_drop_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         # cx_Oracle seems to occasionally leak open connections when a large
         # suite it run, even if we confirm we have zero references to
         # connection objects.
@@ -65,7 +65,7 @@ def _oracle_update_db_opts(db_url, db_opts):
 def _reap_oracle_dbs(url, idents):
     log.info("db reaper connecting to %r", url)
     eng = create_engine(url)
-    with eng.connect() as conn:
+    with eng.begin() as conn:
 
         log.info("identifiers in file: %s", ", ".join(idents))
 
diff --git a/lib/sqlalchemy/dialects/postgresql/provision.py b/lib/sqlalchemy/dialects/postgresql/provision.py
index 9433ec458..575316c61 100644
--- a/lib/sqlalchemy/dialects/postgresql/provision.py
+++ b/lib/sqlalchemy/dialects/postgresql/provision.py
@@ -13,7 +13,7 @@ from ...testing.provision import temp_table_keyword_args
 def _pg_create_db(cfg, eng, ident):
     template_db = cfg.options.postgresql_templatedb
 
-    with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
+    with eng.execution_options(isolation_level="AUTOCOMMIT").begin() as conn:
         try:
             _pg_drop_db(cfg, conn, ident)
         except Exception:
@@ -51,15 +51,16 @@ def _pg_create_db(cfg, eng, ident):
 @drop_db.for_db("postgresql")
 def _pg_drop_db(cfg, eng, ident):
     with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
-        conn.execute(
-            text(
-                "select pg_terminate_backend(pid) from pg_stat_activity "
-                "where usename=current_user and pid != pg_backend_pid() "
-                "and datname=:dname"
-            ),
-            dname=ident,
-        )
-        conn.exec_driver_sql("DROP DATABASE %s" % ident)
+        with conn.begin():
+            conn.execute(
+                text(
+                    "select pg_terminate_backend(pid) from pg_stat_activity "
+                    "where usename=current_user and pid != pg_backend_pid() "
+                    "and datname=:dname"
+                ),
+                dname=ident,
+            )
+            conn.exec_driver_sql("DROP DATABASE %s" % ident)
 
 
 @temp_table_keyword_args.for_db("postgresql")
diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index 9a5518a96..028af9fbb 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -840,7 +840,15 @@ class Connection(Connectable):
     def _commit_impl(self, autocommit=False):
         assert not self.__branch_from
 
-        if autocommit:
+        # AUTOCOMMIT isolation-level is a dialect-specific concept, however
+        # if a connection has this set as the isolation level, we can skip
+        # the "autocommit" warning as the operation will do "autocommit"
+        # in any case
+        if (
+            autocommit
+            and self._execution_options.get("isolation_level", None)
+            != "AUTOCOMMIT"
+        ):
             util.warn_deprecated_20(
                 "The current statement is being autocommitted using "
                 "implicit autocommit, which will be removed in "
@@ -2687,9 +2695,11 @@ class Engine(Connectable, log.Identified):
         self.pool = self.pool.recreate()
         self.dispatch.engine_disposed(self)
 
-    def _execute_default(self, default):
+    def _execute_default(
+        self, default, multiparams=(), params=util.EMPTY_DICT
+    ):
         with self.connect() as conn:
-            return conn._execute_default(default, (), {})
+            return conn._execute_default(default, multiparams, params)
 
     @contextlib.contextmanager
     def _optional_conn_ctx_manager(self, connection=None):
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 4b19ff02a..b5e45c18d 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2258,10 +2258,10 @@ class DefaultGenerator(SchemaItem):
         "or in the ORM by the :meth:`.Session.execute` method of "
         ":class:`.Session`.",
     )
-    def execute(self, bind=None, **kwargs):
+    def execute(self, bind=None):
         if bind is None:
             bind = _bind_or_error(self)
-        return bind.execute(self, **kwargs)
+        return bind._execute_default(self, (), util.EMPTY_DICT)
 
     def _execute_on_connection(
         self, connection, multiparams, params, execution_options
diff --git a/lib/sqlalchemy/testing/suite/test_cte.py b/lib/sqlalchemy/testing/suite/test_cte.py
index 4addca009..a94ee55dc 100644
--- a/lib/sqlalchemy/testing/suite/test_cte.py
+++ b/lib/sqlalchemy/testing/suite/test_cte.py
@@ -1,4 +1,3 @@
-from .. import config
 from .. import fixtures
 from ..assertions import eq_
 from ..schema import Column
@@ -48,164 +47,158 @@ class CTETest(fixtures.TablesTest):
             ],
         )
 
-    def test_select_nonrecursive_round_trip(self):
+    def test_select_nonrecursive_round_trip(self, connection):
         some_table = self.tables.some_table
 
-        with config.db.connect() as conn:
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte")
-            )
-            result = conn.execute(
-                select(cte.c.data).where(cte.c.data.in_(["d4", "d5"]))
-            )
-            eq_(result.fetchall(), [("d4",)])
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte")
+        )
+        result = connection.execute(
+            select(cte.c.data).where(cte.c.data.in_(["d4", "d5"]))
+        )
+        eq_(result.fetchall(), [("d4",)])
 
-    def test_select_recursive_round_trip(self):
+    def test_select_recursive_round_trip(self, connection):
         some_table = self.tables.some_table
 
-        with config.db.connect() as conn:
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte", recursive=True)
-            )
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte", recursive=True)
+        )
 
-            cte_alias = cte.alias("c1")
-            st1 = some_table.alias()
-            # note that SQL Server requires this to be UNION ALL,
-            # can't be UNION
-            cte = cte.union_all(
-                select(st1).where(st1.c.id == cte_alias.c.parent_id)
-            )
-            result = conn.execute(
-                select(cte.c.data)
-                .where(cte.c.data != "d2")
-                .order_by(cte.c.data.desc())
-            )
-            eq_(
-                result.fetchall(),
-                [("d4",), ("d3",), ("d3",), ("d1",), ("d1",), ("d1",)],
-            )
+        cte_alias = cte.alias("c1")
+        st1 = some_table.alias()
+        # note that SQL Server requires this to be UNION ALL,
+        # can't be UNION
+        cte = cte.union_all(
+            select(st1).where(st1.c.id == cte_alias.c.parent_id)
+        )
+        result = connection.execute(
+            select(cte.c.data)
+            .where(cte.c.data != "d2")
+            .order_by(cte.c.data.desc())
+        )
+        eq_(
+            result.fetchall(),
+            [("d4",), ("d3",), ("d3",), ("d1",), ("d1",), ("d1",)],
+        )
 
-    def test_insert_from_select_round_trip(self):
+    def test_insert_from_select_round_trip(self, connection):
         some_table = self.tables.some_table
         some_other_table = self.tables.some_other_table
 
-        with config.db.connect() as conn:
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte")
-            )
-            conn.execute(
-                some_other_table.insert().from_select(
-                    ["id", "data", "parent_id"], select(cte)
-                )
-            )
-            eq_(
-                conn.execute(
-                    select(some_other_table).order_by(some_other_table.c.id)
-                ).fetchall(),
-                [(2, "d2", 1), (3, "d3", 1), (4, "d4", 3)],
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte")
+        )
+        connection.execute(
+            some_other_table.insert().from_select(
+                ["id", "data", "parent_id"], select(cte)
             )
+        )
+        eq_(
+            connection.execute(
+                select(some_other_table).order_by(some_other_table.c.id)
+            ).fetchall(),
+            [(2, "d2", 1), (3, "d3", 1), (4, "d4", 3)],
+        )
 
     @testing.requires.ctes_with_update_delete
     @testing.requires.update_from
-    def test_update_from_round_trip(self):
+    def test_update_from_round_trip(self, connection):
         some_table = self.tables.some_table
         some_other_table = self.tables.some_other_table
 
-        with config.db.connect() as conn:
-            conn.execute(
-                some_other_table.insert().from_select(
-                    ["id", "data", "parent_id"], select(some_table)
-                )
+        connection.execute(
+            some_other_table.insert().from_select(
+                ["id", "data", "parent_id"], select(some_table)
             )
+        )
 
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte")
-            )
-            conn.execute(
-                some_other_table.update()
-                .values(parent_id=5)
-                .where(some_other_table.c.data == cte.c.data)
-            )
-            eq_(
-                conn.execute(
-                    select(some_other_table).order_by(some_other_table.c.id)
-                ).fetchall(),
-                [
-                    (1, "d1", None),
-                    (2, "d2", 5),
-                    (3, "d3", 5),
-                    (4, "d4", 5),
-                    (5, "d5", 3),
-                ],
-            )
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte")
+        )
+        connection.execute(
+            some_other_table.update()
+            .values(parent_id=5)
+            .where(some_other_table.c.data == cte.c.data)
+        )
+        eq_(
+            connection.execute(
+                select(some_other_table).order_by(some_other_table.c.id)
+            ).fetchall(),
+            [
+                (1, "d1", None),
+                (2, "d2", 5),
+                (3, "d3", 5),
+                (4, "d4", 5),
+                (5, "d5", 3),
+            ],
+        )
 
     @testing.requires.ctes_with_update_delete
     @testing.requires.delete_from
-    def test_delete_from_round_trip(self):
+    def test_delete_from_round_trip(self, connection):
         some_table = self.tables.some_table
         some_other_table = self.tables.some_other_table
 
-        with config.db.connect() as conn:
-            conn.execute(
-                some_other_table.insert().from_select(
-                    ["id", "data", "parent_id"], select(some_table)
-                )
+        connection.execute(
+            some_other_table.insert().from_select(
+                ["id", "data", "parent_id"], select(some_table)
             )
+        )
 
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte")
-            )
-            conn.execute(
-                some_other_table.delete().where(
-                    some_other_table.c.data == cte.c.data
-                )
-            )
-            eq_(
-                conn.execute(
-                    select(some_other_table).order_by(some_other_table.c.id)
-                ).fetchall(),
-                [(1, "d1", None), (5, "d5", 3)],
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte")
+        )
+        connection.execute(
+            some_other_table.delete().where(
+                some_other_table.c.data == cte.c.data
             )
+        )
+        eq_(
+            connection.execute(
+                select(some_other_table).order_by(some_other_table.c.id)
+            ).fetchall(),
+            [(1, "d1", None), (5, "d5", 3)],
+        )
 
     @testing.requires.ctes_with_update_delete
-    def test_delete_scalar_subq_round_trip(self):
+    def test_delete_scalar_subq_round_trip(self, connection):
 
         some_table = self.tables.some_table
         some_other_table = self.tables.some_other_table
 
-        with config.db.connect() as conn:
-            conn.execute(
-                some_other_table.insert().from_select(
-                    ["id", "data", "parent_id"], select(some_table)
-                )
+        connection.execute(
+            some_other_table.insert().from_select(
+                ["id", "data", "parent_id"], select(some_table)
             )
+        )
 
-            cte = (
-                select(some_table)
-                .where(some_table.c.data.in_(["d2", "d3", "d4"]))
-                .cte("some_cte")
-            )
-            conn.execute(
-                some_other_table.delete().where(
-                    some_other_table.c.data
-                    == select(cte.c.data)
-                    .where(cte.c.id == some_other_table.c.id)
-                    .scalar_subquery()
-                )
-            )
-            eq_(
-                conn.execute(
-                    select(some_other_table).order_by(some_other_table.c.id)
-                ).fetchall(),
-                [(1, "d1", None), (5, "d5", 3)],
+        cte = (
+            select(some_table)
+            .where(some_table.c.data.in_(["d2", "d3", "d4"]))
+            .cte("some_cte")
+        )
+        connection.execute(
+            some_other_table.delete().where(
+                some_other_table.c.data
+                == select(cte.c.data)
+                .where(cte.c.id == some_other_table.c.id)
+                .scalar_subquery()
             )
+        )
+        eq_(
+            connection.execute(
+                select(some_other_table).order_by(some_other_table.c.id)
+            ).fetchall(),
+            [(1, "d1", None), (5, "d5", 3)],
+        )
diff --git a/lib/sqlalchemy/testing/suite/test_dialect.py b/lib/sqlalchemy/testing/suite/test_dialect.py
index f860a321b..6a5f2d91b 100644
--- a/lib/sqlalchemy/testing/suite/test_dialect.py
+++ b/lib/sqlalchemy/testing/suite/test_dialect.py
@@ -145,7 +145,7 @@ class IsolationLevelTest(fixtures.TestBase):
                 )
 
 
-class AutocommitTest(fixtures.TablesTest):
+class AutocommitIsolationTest(fixtures.TablesTest):
 
     run_deletes = "each"
 
@@ -175,7 +175,8 @@ class AutocommitTest(fixtures.TablesTest):
             1 if autocommit else None,
         )
 
-        conn.execute(self.tables.some_table.delete())
+        with conn.begin():
+            conn.execute(self.tables.some_table.delete())
 
     def test_autocommit_on(self):
         conn = config.db.connect()
@@ -192,7 +193,7 @@ class AutocommitTest(fixtures.TablesTest):
 
     def test_turn_autocommit_off_via_default_iso_level(self):
         conn = config.db.connect()
-        conn.execution_options(isolation_level="AUTOCOMMIT")
+        conn = conn.execution_options(isolation_level="AUTOCOMMIT")
         self._test_conn_autocommits(conn, True)
 
         conn.execution_options(
diff --git a/lib/sqlalchemy/testing/suite/test_results.py b/lib/sqlalchemy/testing/suite/test_results.py
index f31c7c137..e0fdbe47a 100644
--- a/lib/sqlalchemy/testing/suite/test_results.py
+++ b/lib/sqlalchemy/testing/suite/test_results.py
@@ -356,7 +356,7 @@ class ServerSideCursorsTest(
             Column("data", String(50)),
         )
 
-        with engine.connect() as connection:
+        with engine.begin() as connection:
             test_table.create(connection, checkfirst=True)
             connection.execute(test_table.insert(), dict(data="data1"))
             connection.execute(test_table.insert(), dict(data="data2"))
@@ -397,7 +397,7 @@ class ServerSideCursorsTest(
             Column("data", String(50)),
         )
 
-        with engine.connect() as connection:
+        with engine.begin() as connection:
             test_table.create(connection, checkfirst=True)
             connection.execute(
                 test_table.insert(),
diff --git a/lib/sqlalchemy/testing/suite/test_rowcount.py b/lib/sqlalchemy/testing/suite/test_rowcount.py
index 06945ff2a..f3f902abd 100644
--- a/lib/sqlalchemy/testing/suite/test_rowcount.py
+++ b/lib/sqlalchemy/testing/suite/test_rowcount.py
@@ -58,12 +58,14 @@ class RowCountTest(fixtures.TablesTest):
 
         assert len(r) == len(self.data)
 
-    def test_update_rowcount1(self):
+    def test_update_rowcount1(self, connection):
         employees_table = self.tables.employees
 
         # WHERE matches 3, 3 rows changed
         department = employees_table.c.department
-        r = employees_table.update(department == "C").execute(department="Z")
+        r = connection.execute(
+            employees_table.update(department == "C"), {"department": "Z"}
+        )
         assert r.rowcount == 3
 
     def test_update_rowcount2(self, connection):
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index da01aa484..21d2e8942 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -340,7 +340,7 @@ class _DateFixture(_LiteralRoundTripFixture, fixtures.TestBase):
         # passing NULL for an expression that needs to be interpreted as
         # a certain type, does the DBAPI have the info it needs to do this.
         date_table = self.tables.date_table
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             result = conn.execute(
                 date_table.insert(), {"date_data": self.data}
             )
@@ -702,7 +702,7 @@ class BooleanTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         # testing "WHERE <column>" renders a compatible expression
         boolean_table = self.tables.boolean_table
 
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 boolean_table.insert(),
                 [
@@ -817,7 +817,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_index_typed_access(self, datatype, value):
         data_table = self.tables.data_table
         data_element = {"key1": value}
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 data_table.insert(),
                 {
@@ -841,7 +841,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_index_typed_comparison(self, datatype, value):
         data_table = self.tables.data_table
         data_element = {"key1": value}
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 data_table.insert(),
                 {
@@ -864,7 +864,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_path_typed_comparison(self, datatype, value):
         data_table = self.tables.data_table
         data_element = {"key1": {"subkey1": value}}
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 data_table.insert(),
                 {
@@ -900,7 +900,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_single_element_round_trip(self, element):
         data_table = self.tables.data_table
         data_element = element
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 data_table.insert(),
                 {
@@ -928,7 +928,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
         # support sqlite :memory: database...
         data_table.create(engine, checkfirst=True)
-        with engine.connect() as conn:
+        with engine.begin() as conn:
             conn.execute(
                 data_table.insert(), {"name": "row1", "data": data_element}
             )
@@ -978,7 +978,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
     def test_round_trip_none_as_json_null(self):
         col = self.tables.data_table.c["data"]
 
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 self.tables.data_table.insert(), {"name": "r1", "data": None}
             )
@@ -996,7 +996,7 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
     def test_unicode_round_trip(self):
         # note we include Unicode supplementary characters as well
-        with config.db.connect() as conn:
+        with config.db.begin() as conn:
             conn.execute(
                 self.tables.data_table.insert(),
                 {
diff --git a/lib/sqlalchemy/testing/util.py b/lib/sqlalchemy/testing/util.py
index c52dc4a19..c6626b9e0 100644
--- a/lib/sqlalchemy/testing/util.py
+++ b/lib/sqlalchemy/testing/util.py
@@ -370,7 +370,7 @@ def drop_all_tables(engine, inspector, schema=None, include_names=None):
     if include_names is not None:
         include_names = set(include_names)
 
-    with engine.connect() as conn:
+    with engine.begin() as conn:
         for tname, fkcs in reversed(
             inspector.get_sorted_table_and_fkc_names(schema=schema)
         ):
diff --git a/lib/sqlalchemy/testing/warnings.py b/lib/sqlalchemy/testing/warnings.py
index 34a968aff..735fb82e4 100644
--- a/lib/sqlalchemy/testing/warnings.py
+++ b/lib/sqlalchemy/testing/warnings.py
@@ -56,13 +56,11 @@ def setup_filters():
         # Core execution
         #
         r"The (?:Executable|Engine)\.(?:execute|scalar)\(\) method",
-        r"The current statement is being autocommitted using implicit "
-        "autocommit,",
         r"The connection.execute\(\) method in SQLAlchemy 2.0 will accept "
         "parameters as a single dictionary or a single sequence of "
         "dictionaries only.",
         r"The Connection.connect\(\) method is considered legacy",
-        r".*DefaultGenerator.execute\(\)",
+        #        r".*DefaultGenerator.execute\(\)",
         #
         # bound metadaa
         #