expand and further generalize bound parameter translate

Continued with the improvement made as part of :ticket:`5653` to further
support bound parameter names, including those generated against column
names, for names that include colons, parenthesis, and question marks, as
well as improved test support, so that bound parameter names even if they
are auto-derived from column names should have no problem including for
parenthesis in psycopg2's "pyformat" style.

As part of this change, the format used by the asyncpg DBAPI adapter (which
is local to SQLAlchemy's asyncpg diaelct) has been changed from using
"qmark" paramstyle to "format", as there is a standard and internally
supported SQL string escaping style for names that use percent signs with
"format" style (i.e. to double percent signs), as opposed to names that use
question marks with "qmark" style (where an escaping system is not defined
by pep-249 or Python).

Fixes: #5941
Change-Id: Id86f5af81903d7063a8e3505e60df56490f85358

2 files changed, 15 insertions, 73 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index 424ed0d50..7ef5e441c 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -100,7 +100,6 @@ To disable the prepared statement cache, use a value of zero::
 
 import collections
 import decimal
-import itertools
 import json as _py_json
 import re
 import time
@@ -357,12 +356,12 @@ class AsyncAdapt_asyncpg_cursor:
     def _handle_exception(self, error):
         self._adapt_connection._handle_exception(error)
 
-    def _parameters(self):
+    def _parameter_placeholders(self, params):
         if not self._inputsizes:
-            return ("$%d" % idx for idx in itertools.count(1))
+            return tuple("$%d" % idx for idx, _ in enumerate(params, 1))
         else:
 
-            return (
+            return tuple(
                 "$%d::%s" % (idx, typ) if typ else "$%d" % idx
                 for idx, typ in enumerate(
                     (_pg_types.get(typ) for typ in self._inputsizes), 1
@@ -374,11 +373,10 @@ class AsyncAdapt_asyncpg_cursor:
         if not self._adapt_connection._started:
             await self._adapt_connection._start_transaction()
 
-        params = self._parameters()
-
-        # TODO: would be nice to support the dollar numeric thing
-        # directly, this is much easier for now
-        operation = re.sub(r"\?", lambda m: next(params), operation)
+        if parameters is not None:
+            operation = operation % self._parameter_placeholders(parameters)
+        else:
+            parameters = ()
 
         try:
             prepared_stmt, attributes = await self._adapt_connection._prepare(
@@ -409,7 +407,7 @@ class AsyncAdapt_asyncpg_cursor:
         except Exception as error:
             self._handle_exception(error)
 
-    def execute(self, operation, parameters=()):
+    def execute(self, operation, parameters=None):
         try:
             self._adapt_connection.await_(
                 self._prepare_and_execute(operation, parameters)
@@ -429,8 +427,10 @@ class AsyncAdapt_asyncpg_cursor:
         if not adapt_connection._started:
             adapt_connection.await_(adapt_connection._start_transaction())
 
-        params = self._parameters()
-        operation = re.sub(r"\?", lambda m: next(params), operation)
+        operation = operation % self._parameter_placeholders(
+            seq_of_parameters[0]
+        )
+
         try:
             return adapt_connection.await_(
                 self._connection.executemany(operation, seq_of_parameters)
@@ -706,7 +706,7 @@ class AsyncAdaptFallback_asyncpg_connection(AsyncAdapt_asyncpg_connection):
 class AsyncAdapt_asyncpg_dbapi:
     def __init__(self, asyncpg):
         self.asyncpg = asyncpg
-        self.paramstyle = "qmark"
+        self.paramstyle = "format"
 
     def connect(self, *arg, **kw):
         async_fallback = kw.pop("async_fallback", False)
@@ -837,7 +837,7 @@ class PGDialect_asyncpg(PGDialect):
 
     supports_unicode_binds = True
 
-    default_paramstyle = "qmark"
+    default_paramstyle = "format"
     supports_sane_multi_rowcount = False
     execution_ctx_cls = PGExecutionContext_asyncpg
     statement_compiler = PGCompiler_asyncpg
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index b1d27c6fa..a52eacd8b 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -323,57 +323,6 @@ on the way in and coerce from bytes on the way back, using the value of the
 SQLAlchemy's own unicode encode/decode functionality is steadily becoming
 obsolete as most DBAPIs now support unicode fully.
 
-Bound Parameter Styles
-----------------------
-
-The default parameter style for the psycopg2 dialect is "pyformat", where
-SQL is rendered using ``%(paramname)s`` style.   This format has the limitation
-that it does not accommodate the unusual case of parameter names that
-actually contain percent or parenthesis symbols; as SQLAlchemy in many cases
-generates bound parameter names based on the name of a column, the presence
-of these characters in a column name can lead to problems.
-
-There are two solutions to the issue of a :class:`_schema.Column`
-that contains
-one of these characters in its name.  One is to specify the
-:paramref:`.schema.Column.key` for columns that have such names::
-
-    measurement = Table('measurement', metadata,
-        Column('Size (meters)', Integer, key='size_meters')
-    )
-
-Above, an INSERT statement such as ``measurement.insert()`` will use
-``size_meters`` as the parameter name, and a SQL expression such as
-``measurement.c.size_meters > 10`` will derive the bound parameter name
-from the ``size_meters`` key as well.
-
-.. versionchanged:: 1.0.0 - SQL expressions will use
-   :attr:`_schema.Column.key`
-   as the source of naming when anonymous bound parameters are created
-   in SQL expressions; previously, this behavior only applied to
-   :meth:`_schema.Table.insert` and :meth:`_schema.Table.update`
-   parameter names.
-
-The other solution is to use a positional format; psycopg2 allows use of the
-"format" paramstyle, which can be passed to
-:paramref:`_sa.create_engine.paramstyle`::
-
-    engine = create_engine(
-        'postgresql://scott:tiger@localhost:5432/test', paramstyle='format')
-
-With the above engine, instead of a statement like::
-
-    INSERT INTO measurement ("Size (meters)") VALUES (%(Size (meters))s)
-    {'Size (meters)': 1}
-
-we instead see::
-
-    INSERT INTO measurement ("Size (meters)") VALUES (%s)
-    (1, )
-
-Where above, the dictionary style is converted into a tuple with positional
-style.
-
 
 Transactions
 ------------
@@ -648,14 +597,7 @@ class PGExecutionContext_psycopg2(PGExecutionContext):
 
 
 class PGCompiler_psycopg2(PGCompiler):
-    def bindparam_string(self, name, **kw):
-        if "%" in name and not kw.get("post_compile", False):
-            # psycopg2 will not allow a percent sign in a
-            # pyformat parameter name even if it is doubled
-            kw["escaped_from"] = name
-            name = name.replace("%", "P")
-
-        return PGCompiler.bindparam_string(self, name, **kw)
+    pass
 
 
 class PGIdentifierPreparer_psycopg2(PGIdentifierPreparer):