Emit v2.0 deprecation warning for "implicit autocommit"

"Implicit autocommit", which is the COMMIT that occurs when a DML or DDL
statement is emitted on a connection, is deprecated and won't be part of
SQLAlchemy 2.0.   A 2.0-style warning is emitted when autocommit takes
effect, so that the calling code may be adjusted to use an explicit
transaction.

As part of this change, DDL methods such as
:meth:`_schema.MetaData.create_all` when used against a
:class:`_engine.Engine` or :class:`_engine.Connection` will run the
operation in a BEGIN block if one is not started already.

The MySQL and MariaDB dialects now query from the information_schema.tables
system view in order to determine if a particular table exists or not.
Previously, the "DESCRIBE" command was used with an exception catch to
detect non-existent,  which would have the undesirable effect of emitting a
ROLLBACK on the connection. There appeared to be legacy encoding issues
which prevented the use of "SHOW TABLES", for this, but as MySQL support is
now at 5.0.2  or above due to :ticket:`4189`, the information_schema tables
are now available in all cases.

Fixes: #4846
Change-Id: I733a7e0e17477a63607fb9931c87c393bbd7ac57

1 files changed, 30 insertions, 11 deletions

diff --git a/test/dialect/mssql/test_reflection.py b/test/dialect/mssql/test_reflection.py
index 6e4038eb4..0bd8f7a5a 100644
--- a/test/dialect/mssql/test_reflection.py
+++ b/test/dialect/mssql/test_reflection.py
@@ -12,6 +12,7 @@ from sqlalchemy import schema
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy import types
+from sqlalchemy import types as sqltypes
 from sqlalchemy import util
 from sqlalchemy.dialects import mssql
 from sqlalchemy.dialects.mssql import base
@@ -143,20 +144,38 @@ class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL):
         eq_(table2.c["col1"].dialect_options["mssql"]["identity_start"], 2)
         eq_(table2.c["col1"].dialect_options["mssql"]["identity_increment"], 3)
 
-    @testing.emits_warning("Did not recognize")
     @testing.provide_metadata
-    def test_skip_types(self):
-        metadata = self.metadata
-        with testing.db.connect() as c:
-            c.exec_driver_sql(
-                "create table foo (id integer primary key, data xml)"
-            )
+    def test_skip_types(self, connection):
+        connection.exec_driver_sql(
+            "create table foo (id integer primary key, data xml)"
+        )
         with mock.patch.object(
-            testing.db.dialect, "ischema_names", {"int": mssql.INTEGER}
+            connection.dialect, "ischema_names", {"int": mssql.INTEGER}
         ):
-            t1 = Table("foo", metadata, autoload=True)
-        assert isinstance(t1.c.id.type, Integer)
-        assert isinstance(t1.c.data.type, types.NullType)
+            with testing.expect_warnings(
+                "Did not recognize type 'xml' of column 'data'"
+            ):
+                eq_(
+                    inspect(connection).get_columns("foo"),
+                    [
+                        {
+                            "name": "id",
+                            "type": testing.eq_type_affinity(sqltypes.INTEGER),
+                            "nullable": False,
+                            "default": None,
+                            "autoincrement": False,
+                        },
+                        {
+                            "name": "data",
+                            "type": testing.eq_type_affinity(
+                                sqltypes.NullType
+                            ),
+                            "nullable": True,
+                            "default": None,
+                            "autoincrement": False,
+                        },
+                    ],
+                )
 
     @testing.provide_metadata
     def test_cross_schema_fk_pk_name_overlaps(self):