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, 188 insertions, 25 deletions

diff --git a/doc/build/changelog/migration_14.rst b/doc/build/changelog/migration_14.rst
index e25d1e8bf..feb6617a8 100644
--- a/doc/build/changelog/migration_14.rst
+++ b/doc/build/changelog/migration_14.rst
@@ -409,6 +409,124 @@ is established as the implementation.
 :ticket:`1390`
 
 
+.. _deprecation_20_mode:
+
+SQLAlchemy 2.0 Deprecations Mode
+---------------------------------
+
+One of the primary goals of the 1.4 release is to provide a "transitional"
+release so that applications may migrate to SQLAlchemy 2.0 gradually.   Towards
+this end, a primary feature in release 1.4 is "2.0 deprecations mode", which is
+a series of deprecation warnings that emit against every detectable API pattern
+which will work differently in version 2.0.   The warnings all make use of the
+:class:`_exc.RemovedIn20Warning` class. As these warnings affect foundational
+patterns including the :func:`_sql.select` and :class:`_engine.Engine` constructs, even
+simple applications can generate a lot of warnings until appropriate API
+changes are made.   The warning mode is therefore turned off by default until
+the developer enables the environment variable ``SQLALCHEMY_WARN_20=1``.
+
+Given the example program below::
+
+  from sqlalchemy import column
+  from sqlalchemy import create_engine
+  from sqlalchemy import select
+  from sqlalchemy import table
+
+
+  engine = create_engine("sqlite://")
+
+  engine.execute("CREATE TABLE foo (id integer)")
+  engine.execute("INSERT INTO foo (id) VALUES (1)")
+
+
+  foo = table("foo", column("id"))
+  result = engine.execute(select([foo.c.id]))
+
+  print(result.fetchall())
+
+The above program uses several patterns that many users will already identify
+as "legacy", namely the use of the :meth:`_engine.Engine.execute` method
+that's part of the :ref:`connectionlesss execution <dbengine_implicit>`
+system.  When we run the above program against 1.4, it returns a single line::
+
+  $ python test3.py
+  [(1,)]
+
+To enable "2.0 deprecations mode", we enable the ``SQLALCHEMY_WARN_20=1``
+variable::
+
+    SQLALCHEMY_WARN_20=1 python test3.py
+
+**IMPORTANT** - older versions of Python may not emit deprecation warnings
+by default.   To guarantee deprecation warnings, use a `warnings filter`_
+that ensures warnings are printed::
+
+    SQLALCHEMY_WARN_20=1 python -W always::DeprecationWarning test3.py
+
+.. _warnings filter: https://docs.python.org/3/library/warnings.html#the-warnings-filter
+
+With warnings turned on, our program now has a lot to say::
+
+  $ SQLALCHEMY_WARN_20=1 python2 -W always::DeprecationWarning test3.py
+  test3.py:9: RemovedIn20Warning: The Engine.execute() function/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. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    engine.execute("CREATE TABLE foo (id integer)")
+  /home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    return connection.execute(statement, *multiparams, **params)
+  /home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit 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. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    self._commit_impl(autocommit=True)
+  test3.py:10: RemovedIn20Warning: The Engine.execute() function/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. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    engine.execute("INSERT INTO foo (id) VALUES (1)")
+  /home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    return connection.execute(statement, *multiparams, **params)
+  /home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit 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. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    self._commit_impl(autocommit=True)
+  /home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/selectable.py:4271: RemovedIn20Warning: The legacy calling style of select() is deprecated and will be removed in SQLAlchemy 2.0.  Please use the new calling style described at select(). (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    return cls.create_legacy_select(*args, **kw)
+  test3.py:14: RemovedIn20Warning: The Engine.execute() function/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. (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: http://sqlalche.me/e/b8d9)
+    result = engine.execute(select([foo.c.id]))
+  [(1,)]
+
+With the above guidance, we can migrate our program to use 2.0 styles, and
+as a bonus our program is much clearer::
+
+  from sqlalchemy import column
+  from sqlalchemy import create_engine
+  from sqlalchemy import select
+  from sqlalchemy import table
+  from sqlalchemy import text
+
+
+  engine = create_engine("sqlite://")
+
+  # don't rely on autocommit for DML and DDL
+  with engine.begin() as connection:
+      # use connection.execute(), not engine.execute()
+      # use the text() construct to execute textual SQL
+      connection.execute(text("CREATE TABLE foo (id integer)"))
+      connection.execute(text("INSERT INTO foo (id) VALUES (1)"))
+
+
+  foo = table("foo", column("id"))
+
+  with engine.connect() as connection:
+      # use connection.execute(), not engine.execute()
+      # select() now accepts column / table expressions positionally
+      result = connection.execute(select(foo.c.id))
+
+  print(result.fetchall())
+
+
+The goal of "2.0 deprecations mode" is that a program which runs with no
+:class:`_exc.RemovedIn20Warning` warnings with "2.0 deprecations mode" turned
+on is then ready to run in SQLAlchemy 2.0.
+
+
+.. seealso::
+
+  :ref:`migration_20_toplevel`
+
+
+
 API and Behavioral Changes - Core
 ==================================
 
diff --git a/doc/build/changelog/migration_20.rst b/doc/build/changelog/migration_20.rst
index 7b3d23c8c..7bb35d422 100644
--- a/doc/build/changelog/migration_20.rst
+++ b/doc/build/changelog/migration_20.rst
@@ -77,15 +77,18 @@ The steps to achieve this are as follows:
   well as providing for the initial real-world adoption of the new
   architectures.
 
-* A new deprecation class :class:`.exc.RemovedIn20Warning` is added, which
-  subclasses :class:`.exc.SADeprecationWarning`.   Applications and their test
+* A new deprecation class :class:`_exc.RemovedIn20Warning` is added, which
+  subclasses :class:`_exc.SADeprecationWarning`.   Applications and their test
   suites can opt to enable or disable reporting of the
-  :class:`.exc.RemovedIn20Warning` warning as needed.   To some extent, the
-  :class:`.exc.RemovedIn20Warning` deprecation class is analogous to the ``-3``
+  :class:`_exc.RemovedIn20Warning` warning as needed, by setting the
+  environment variable ``SQLALCHEMY_WARN_20=1`` **before** the program
+  runs.   To some extent, the
+  :class:`_exc.RemovedIn20Warning` deprecation class is analogous to the ``-3``
   flag available on Python 2 which reports on future Python 3
-  incompatibilities.
+  incompatibilities.   See :ref:`deprecation_20_mode` for background
+  on turning this on.
 
-* APIs which emit :class:`.exc.RemovedIn20Warning` should always feature a new
+* APIs which emit :class:`_exc.RemovedIn20Warning` should always feature a new
   1.4-compatible usage pattern that applications can migrate towards.  This
   pattern will then be fully compatible with SQLAlchemy 2.0.   In this way,
   an application can gradually adjust all of its 1.4-style code to work fully
diff --git a/doc/build/changelog/unreleased_14/4846.rst b/doc/build/changelog/unreleased_14/4846.rst
new file mode 100644
index 000000000..b534f02d2
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/4846.rst
@@ -0,0 +1,19 @@
+.. change::
+    :tags: engine
+    :tickets: 4846
+
+    "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 an
+    :class:`_engine.Engine` will run the operation in a BEGIN block if one is
+    not started already.
+
+    .. seealso::
+
+        :ref:`deprecation_20_mode`
+
diff --git a/doc/build/changelog/unreleased_14/mysql_has_table.rst b/doc/build/changelog/unreleased_14/mysql_has_table.rst
new file mode 100644
index 000000000..09faa6332
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/mysql_has_table.rst
@@ -0,0 +1,12 @@
+.. change::
+    :tags: bug, mysql
+
+    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.
+
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 8d27dd21d..738d4d74e 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -150,15 +150,17 @@ each table first before creating, so it's safe to call multiple times:
 .. sourcecode:: pycon+sql
 
     {sql}>>> metadata.create_all(engine)
-    PRAGMA...
+    BEGIN...
     CREATE TABLE users (
         id INTEGER NOT NULL,
         name VARCHAR,
         fullname VARCHAR,
         PRIMARY KEY (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
-    COMMIT
+    <BLANKLINE>
     CREATE TABLE addresses (
         id INTEGER NOT NULL,
         user_id INTEGER,
@@ -166,6 +168,8 @@ each table first before creating, so it's safe to call multiple times:
         PRIMARY KEY (id),
         FOREIGN KEY(user_id) REFERENCES users (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
     COMMIT
 
diff --git a/doc/build/errors.rst b/doc/build/errors.rst
index ab9e018d9..61f009fad 100644
--- a/doc/build/errors.rst
+++ b/doc/build/errors.rst
@@ -104,13 +104,8 @@ a comprehensive future compatibility system that is to be integrated into the
 unambiguous, and incremental upgrade path in order to migrate applications to
 being fully 2.0 compatible.   The :class:`.exc.RemovedIn20Warning` deprecation
 warning is at the base of this system to provide guidance on what behaviors in
-an existing codebase will need to be modified.
-
-For some occurrences of this warning, an additional recommendation to use an
-API in either the ``sqlalchemy.future`` or  ``sqlalchemy.future.orm`` packages
-may be present.  This refers to two  special future-compatibility packages that
-are part of SQLAlchemy 1.4 and  are there to help migrate an application to the
-2.0 version.
+an existing codebase will need to be modified.  An overview of how to enable
+this warning is at :ref:`deprecation_20_mode`.
 
 .. seealso::
 
@@ -118,6 +113,10 @@ are part of SQLAlchemy 1.4 and  are there to help migrate an application to the
     the 1.x series, as well as the current goals and progress of SQLAlchemy
     2.0.
 
+
+    :ref:`deprecation_20_mode` - specific guidelines on how to use
+    "2.0 deprecations mode" in SQLAlchemy 1.4.
+
 .. _error_c9bf:
 
 A bind was located via legacy bound metadata, but since future=True is set on this Session, this bind is ignored.
diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst
index dbad10b6f..8c148ac32 100644
--- a/doc/build/orm/tutorial.rst
+++ b/doc/build/orm/tutorial.rst
@@ -209,16 +209,16 @@ the actual ``CREATE TABLE`` statement:
 .. sourcecode:: python+sql
 
     >>> Base.metadata.create_all(engine)
-    PRAGMA main.table_info("users")
-    [...] ()
-    PRAGMA temp.table_info("users")
-    [...] ()
+    BEGIN...
     CREATE TABLE users (
-        id INTEGER NOT NULL, name VARCHAR,
+        id INTEGER NOT NULL,
+        name VARCHAR,
         fullname VARCHAR,
         nickname VARCHAR,
         PRIMARY KEY (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
     COMMIT
 
@@ -1215,14 +1215,16 @@ already been created:
 .. sourcecode:: python+sql
 
     {sql}>>> Base.metadata.create_all(engine)
-    PRAGMA...
+    BEGIN...
     CREATE TABLE addresses (
         id INTEGER NOT NULL,
         email_address VARCHAR NOT NULL,
         user_id INTEGER,
         PRIMARY KEY (id),
-         FOREIGN KEY(user_id) REFERENCES users (id)
+        FOREIGN KEY(user_id) REFERENCES users (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
     COMMIT
 
@@ -2080,15 +2082,17 @@ Create new tables:
 .. sourcecode:: python+sql
 
     {sql}>>> Base.metadata.create_all(engine)
-    PRAGMA...
+    BEGIN...
     CREATE TABLE keywords (
         id INTEGER NOT NULL,
         keyword VARCHAR(50) NOT NULL,
         PRIMARY KEY (id),
         UNIQUE (keyword)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
-    COMMIT
+    <BLANKLINE>
     CREATE TABLE posts (
         id INTEGER NOT NULL,
         user_id INTEGER,
@@ -2097,8 +2101,10 @@ Create new tables:
         PRIMARY KEY (id),
         FOREIGN KEY(user_id) REFERENCES users (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
-    COMMIT
+    <BLANKLINE>
     CREATE TABLE post_keywords (
         post_id INTEGER NOT NULL,
         keyword_id INTEGER NOT NULL,
@@ -2106,6 +2112,8 @@ Create new tables:
         FOREIGN KEY(post_id) REFERENCES posts (id),
         FOREIGN KEY(keyword_id) REFERENCES keywords (id)
     )
+    <BLANKLINE>
+    <BLANKLINE>
     [...] ()
     COMMIT