Deprecate plain string in execute and introduce `exec_driver_sql`

Execution of literal sql string is deprecated in the
:meth:`.Connection.execute` and a warning is raised when used stating
that it will be coerced to :func:`.text` in a future release.
To execute a raw sql string the new connection method
:meth:`.Connection.exec_driver_sql` was added, that will retain the previous
behavior, passing the string to the DBAPI driver unchanged.
Usage of scalar or tuple positional parameters in :meth:`.Connection.execute`
is also deprecated.

Fixes: #4848
Fixes: #5178
Change-Id: I2830181054327996d594f7f0d59c157d477c3aa9

3 files changed, 21 insertions, 21 deletions

diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index b1a83bf92..a63ce0033 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -518,14 +518,14 @@ to filter these out::
     eng = create_engine("sqlite://")
     conn = eng.connect()
 
-    conn.execute("create table x (a integer, b integer)")
-    conn.execute("insert into x (a, b) values (1, 1)")
-    conn.execute("insert into x (a, b) values (2, 2)")
+    conn.exec_driver_sql("create table x (a integer, b integer)")
+    conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
+    conn.exec_driver_sql("insert into x (a, b) values (2, 2)")
 
-    result = conn.execute("select x.a, x.b from x")
+    result = conn.exec_driver_sql("select x.a, x.b from x")
     assert result.keys() == ["a", "b"]
 
-    result = conn.execute('''
+    result = conn.exec_driver_sql('''
         select x.a, x.b from x where a=1
         union
         select x.a, x.b from x where a=2
@@ -553,7 +553,7 @@ contain dots, and the functionality of :meth:`.ResultProxy.keys` and
 the ``sqlite_raw_colnames`` execution option may be provided, either on a
 per-:class:`.Connection` basis::
 
-    result = conn.execution_options(sqlite_raw_colnames=True).execute('''
+    result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql('''
         select x.a, x.b from x where a=1
         union
         select x.a, x.b from x where a=2
@@ -1588,7 +1588,7 @@ class SQLiteDialect(default.DefaultDialect):
     @reflection.cache
     def get_schema_names(self, connection, **kw):
         s = "PRAGMA database_list"
-        dl = connection.execute(s)
+        dl = connection.exec_driver_sql(s)
 
         return [db[1] for db in dl if db[1] != "temp"]
 
@@ -1602,7 +1602,7 @@ class SQLiteDialect(default.DefaultDialect):
         s = ("SELECT name FROM %s " "WHERE type='table' ORDER BY name") % (
             master,
         )
-        rs = connection.execute(s)
+        rs = connection.exec_driver_sql(s)
         return [row[0] for row in rs]
 
     @reflection.cache
@@ -1611,7 +1611,7 @@ class SQLiteDialect(default.DefaultDialect):
             "SELECT name FROM sqlite_temp_master "
             "WHERE type='table' ORDER BY name "
         )
-        rs = connection.execute(s)
+        rs = connection.exec_driver_sql(s)
 
         return [row[0] for row in rs]
 
@@ -1621,7 +1621,7 @@ class SQLiteDialect(default.DefaultDialect):
             "SELECT name FROM sqlite_temp_master "
             "WHERE type='view' ORDER BY name "
         )
-        rs = connection.execute(s)
+        rs = connection.exec_driver_sql(s)
 
         return [row[0] for row in rs]
 
@@ -1641,7 +1641,7 @@ class SQLiteDialect(default.DefaultDialect):
         s = ("SELECT name FROM %s " "WHERE type='view' ORDER BY name") % (
             master,
         )
-        rs = connection.execute(s)
+        rs = connection.exec_driver_sql(s)
 
         return [row[0] for row in rs]
 
@@ -1654,7 +1654,7 @@ class SQLiteDialect(default.DefaultDialect):
                 master,
                 view_name,
             )
-            rs = connection.execute(s)
+            rs = connection.exec_driver_sql(s)
         else:
             try:
                 s = (
@@ -1664,13 +1664,13 @@ class SQLiteDialect(default.DefaultDialect):
                     "WHERE name = '%s' "
                     "AND type='view'"
                 ) % view_name
-                rs = connection.execute(s)
+                rs = connection.exec_driver_sql(s)
             except exc.DBAPIError:
                 s = (
                     "SELECT sql FROM sqlite_master WHERE name = '%s' "
                     "AND type='view'"
                 ) % view_name
-                rs = connection.execute(s)
+                rs = connection.exec_driver_sql(s)
 
         result = rs.fetchall()
         if result:
@@ -2070,7 +2070,7 @@ class SQLiteDialect(default.DefaultDialect):
                 "AND type = 'table'"
                 % {"schema": schema_expr, "table": table_name}
             )
-            rs = connection.execute(s)
+            rs = connection.exec_driver_sql(s)
         except exc.DBAPIError:
             s = (
                 "SELECT sql FROM %(schema)ssqlite_master "
@@ -2078,7 +2078,7 @@ class SQLiteDialect(default.DefaultDialect):
                 "AND type = 'table'"
                 % {"schema": schema_expr, "table": table_name}
             )
-            rs = connection.execute(s)
+            rs = connection.exec_driver_sql(s)
         return rs.scalar()
 
     def _get_table_pragma(self, connection, pragma, table_name, schema=None):
@@ -2095,7 +2095,7 @@ class SQLiteDialect(default.DefaultDialect):
         qtable = quote(table_name)
         for statement in statements:
             statement = "%s%s(%s)" % (statement, pragma, qtable)
-            cursor = connection.execute(statement)
+            cursor = connection.exec_driver_sql(statement)
             if not cursor._soft_closed:
                 # work around SQLite issue whereby cursor.description
                 # is blank when PRAGMA returns no rows:
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlcipher.py b/lib/sqlalchemy/dialects/sqlite/pysqlcipher.py
index f8236dea9..a1243f271 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlcipher.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlcipher.py
@@ -112,10 +112,10 @@ class SQLiteDialect_pysqlcipher(SQLiteDialect_pysqlite):
         conn = super(SQLiteDialect_pysqlcipher, self).connect(
             *cargs, **cparams
         )
-        conn.execute('pragma key="%s"' % passphrase)
+        conn.exec_driver_sql('pragma key="%s"' % passphrase)
         for prag, value in pragmas.items():
             if value is not None:
-                conn.execute('pragma %s="%s"' % (prag, value))
+                conn.exec_driver_sql('pragma %s="%s"' % (prag, value))
 
         return conn
 
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 4485631ce..b8a42a506 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -322,7 +322,7 @@ ourselves. This is achieved using two event listeners::
     @event.listens_for(engine, "begin")
     def do_begin(conn):
         # emit our own BEGIN
-        conn.execute("BEGIN")
+        conn.exec_driver_sql("BEGIN")
 
 Above, we intercept a new pysqlite connection and disable any transactional
 integration.   Then, at the point at which SQLAlchemy knows that transaction
@@ -335,7 +335,7 @@ by adding the desired locking mode to our ``"BEGIN"``::
 
     @event.listens_for(engine, "begin")
     def do_begin(conn):
-        conn.execute("BEGIN EXCLUSIVE")
+        conn.exec_driver_sql("BEGIN EXCLUSIVE")
 
 .. seealso::