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

7 files changed, 75 insertions, 36 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 0c9859e79..1003eeca6 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -887,6 +887,7 @@ from collections import defaultdict
 import re
 
 from sqlalchemy import literal_column
+from sqlalchemy import text
 from sqlalchemy.sql import visitors
 from . import reflection as _reflection
 from .enumerated import ENUM
@@ -938,6 +939,7 @@ from ...sql import compiler
 from ...sql import elements
 from ...sql import roles
 from ...sql import util as sql_util
+from ...sql.sqltypes import Unicode
 from ...types import BINARY
 from ...types import BLOB
 from ...types import BOOLEAN
@@ -2708,39 +2710,24 @@ class MySQLDialect(default.DefaultDialect):
         return connection.exec_driver_sql("SELECT DATABASE()").scalar()
 
     def has_table(self, connection, table_name, schema=None):
-        # SHOW TABLE STATUS LIKE and SHOW TABLES LIKE do not function properly
-        # on macosx (and maybe win?) with multibyte table names.
-        #
-        # TODO: if this is not a problem on win, make the strategy swappable
-        # based on platform.  DESCRIBE is slower.
-
-        # [ticket:726]
-        # full_name = self.identifier_preparer.format_table(table,
-        #                                                   use_schema=True)
+        if schema is None:
+            schema = self.default_schema_name
 
-        full_name = ".".join(
-            self.identifier_preparer._quote_free_identifiers(
-                schema, table_name
-            )
+        rs = connection.execute(
+            text(
+                "SELECT * FROM information_schema.tables WHERE "
+                "table_schema = :table_schema AND "
+                "table_name = :table_name"
+            ).bindparams(
+                sql.bindparam("table_schema", type_=Unicode),
+                sql.bindparam("table_name", type_=Unicode),
+            ),
+            {
+                "table_schema": util.text_type(schema),
+                "table_name": util.text_type(table_name),
+            },
         )
-
-        st = "DESCRIBE %s" % full_name
-        rs = None
-        try:
-            try:
-                rs = connection.execution_options(
-                    skip_user_error_events=True
-                ).exec_driver_sql(st)
-                have = rs.fetchone() is not None
-                rs.close()
-                return have
-            except exc.DBAPIError as e:
-                if self._extract_error_code(e.orig) == 1146:
-                    return False
-                raise
-        finally:
-            if rs:
-                rs.close()
+        return bool(rs.scalar())
 
     def has_sequence(self, connection, sequence_name, schema=None):
         if not self.supports_sequences:
diff --git a/lib/sqlalchemy/engine/base.py b/lib/sqlalchemy/engine/base.py
index 91fff4549..afab8e7b4 100644
--- a/lib/sqlalchemy/engine/base.py
+++ b/lib/sqlalchemy/engine/base.py
@@ -829,6 +829,15 @@ class Connection(Connectable):
     def _commit_impl(self, autocommit=False):
         assert not self.__branch_from
 
+        if autocommit:
+            util.warn_deprecated_20(
+                "The current statement is being autocommitted using "
+                "implicit autocommit, which will be removed in "
+                "SQLAlchemy 2.0. "
+                "Use the .begin() method of Engine or Connection in order to "
+                "use an explicit transaction for DML and DDL statements."
+            )
+
         if self._has_events or self.engine._has_events:
             self.dispatch.commit(self)
 
@@ -2814,7 +2823,7 @@ class Engine(Connectable, log.Identified):
             return conn.run_callable(callable_, *args, **kwargs)
 
     def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
-        with self.connect() as conn:
+        with self.begin() as conn:
             conn._run_ddl_visitor(visitorcallable, element, **kwargs)
 
     @util.deprecated_20(
diff --git a/lib/sqlalchemy/exc.py b/lib/sqlalchemy/exc.py
index 491dde7b2..a17bb5cec 100644
--- a/lib/sqlalchemy/exc.py
+++ b/lib/sqlalchemy/exc.py
@@ -636,6 +636,8 @@ class RemovedIn20Warning(SADeprecationWarning):
 
         :ref:`error_b8d9`.
 
+        :ref:`deprecation_20_mode`
+
     """
 
     deprecated_since = "1.4"
diff --git a/lib/sqlalchemy/testing/__init__.py b/lib/sqlalchemy/testing/__init__.py
index 9b1164874..45cc7ea2a 100644
--- a/lib/sqlalchemy/testing/__init__.py
+++ b/lib/sqlalchemy/testing/__init__.py
@@ -55,6 +55,7 @@ from .exclusions import only_if  # noqa
 from .exclusions import only_on  # noqa
 from .exclusions import skip  # noqa
 from .exclusions import skip_if  # noqa
+from .schema import eq_type_affinity  # noqa
 from .util import adict  # noqa
 from .util import fail  # noqa
 from .util import flag_combinations  # noqa
diff --git a/lib/sqlalchemy/testing/assertions.py b/lib/sqlalchemy/testing/assertions.py
index f78ebf496..67ef38a25 100644
--- a/lib/sqlalchemy/testing/assertions.py
+++ b/lib/sqlalchemy/testing/assertions.py
@@ -32,7 +32,7 @@ from ..util import decorator
 def expect_warnings(*messages, **kw):
     """Context manager which expects one or more warnings.
 
-    With no arguments, squelches all SAWarnings emitted via
+    With no arguments, squelches all SAWarning and RemovedIn20Warning emitted via
     sqlalchemy.util.warn and sqlalchemy.util.warn_limited.   Otherwise
     pass string expressions that will match selected warnings via regex;
     all non-matching warnings are sent through.
@@ -41,8 +41,10 @@ def expect_warnings(*messages, **kw):
 
     Note that the test suite sets SAWarning warnings to raise exceptions.
 
-    """
-    return _expect_warnings(sa_exc.SAWarning, messages, **kw)
+    """  # noqa
+    return _expect_warnings(
+        (sa_exc.SAWarning, sa_exc.RemovedIn20Warning), messages, **kw
+    )
 
 
 @contextlib.contextmanager
diff --git a/lib/sqlalchemy/testing/schema.py b/lib/sqlalchemy/testing/schema.py
index f5bd1f7a2..8e26d2eaf 100644
--- a/lib/sqlalchemy/testing/schema.py
+++ b/lib/sqlalchemy/testing/schema.py
@@ -9,6 +9,7 @@ from . import config
 from . import exclusions
 from .. import event
 from .. import schema
+from .. import types as sqltypes
 
 
 __all__ = ["Table", "Column"]
@@ -115,6 +116,43 @@ def Column(*args, **kw):
     return col
 
 
+class eq_type_affinity(object):
+    """Helper to compare types inside of datastructures based on affinity.
+
+    E.g.::
+
+        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,
+                },
+            ],
+        )
+
+    """
+
+    def __init__(self, target):
+        self.target = sqltypes.to_instance(target)
+
+    def __eq__(self, other):
+        return self.target._type_affinity is other._type_affinity
+
+    def __ne__(self, other):
+        return self.target._type_affinity is not other._type_affinity
+
+
 def _truncate_name(dialect, name):
     if len(name) > dialect.max_identifier_length:
         return (
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 9a2fdf95a..8c6543700 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -57,7 +57,7 @@ class _LiteralRoundTripFixture(object):
         t = Table("t", self.metadata, Column("x", type_))
         t.create()
 
-        with testing.db.connect() as conn:
+        with testing.db.begin() as conn:
             for value in input_:
                 ins = (
                     t.insert()