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

1 files changed, 49 insertions, 0 deletions

diff --git a/lib/sqlalchemy/testing/suite/test_dialect.py b/lib/sqlalchemy/testing/suite/test_dialect.py
index 8709aca9d..a236b1076 100644
--- a/lib/sqlalchemy/testing/suite/test_dialect.py
+++ b/lib/sqlalchemy/testing/suite/test_dialect.py
@@ -1,5 +1,6 @@
 #! coding: utf-8
 
+from . import testing
 from .. import assert_raises
 from .. import config
 from .. import engines
@@ -11,6 +12,7 @@ from ..config import requirements
 from ..provision import set_default_schema_on_connection
 from ..schema import Column
 from ..schema import Table
+from ... import bindparam
 from ... import event
 from ... import exc
 from ... import Integer
@@ -308,3 +310,50 @@ class FutureWeCanSetDefaultSchemaWEventsTest(
     fixtures.FutureEngineMixin, WeCanSetDefaultSchemaWEventsTest
 ):
     pass
+
+
+class DifficultParametersTest(fixtures.TestBase):
+    __backend__ = True
+
+    @testing.combinations(
+        ("boring",),
+        ("per cent",),
+        ("per % cent",),
+        ("%percent",),
+        ("par(ens)",),
+        ("percent%(ens)yah",),
+        ("col:ons",),
+        ("more :: %colons%",),
+        ("/slashes/",),
+        ("more/slashes",),
+        ("q?marks",),
+        ("1param",),
+        ("1col:on",),
+        argnames="name",
+    )
+    def test_round_trip(self, name, connection, metadata):
+        t = Table(
+            "t",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column(name, String(50), nullable=False),
+        )
+
+        # table is created
+        t.create(connection)
+
+        # automatic param generated by insert
+        connection.execute(t.insert().values({"id": 1, name: "some name"}))
+
+        # automatic param generated by criteria, plus selecting the column
+        stmt = select(t.c[name]).where(t.c[name] == "some name")
+
+        eq_(connection.scalar(stmt), "some name")
+
+        # use the name in a param explicitly
+        stmt = select(t.c[name]).where(t.c[name] == bindparam(name))
+
+        row = connection.execute(stmt, {name: "some name"}).first()
+
+        # name works as the key from cursor.description
+        eq_(row._mapping[name], "some name")