Merge "disable "bytes" handler for all drivers other than psycopg2" into main

11 files changed, 38 insertions, 5 deletions

diff --git a/doc/build/changelog/unreleased_20/9680.rst b/doc/build/changelog/unreleased_20/9680.rst
new file mode 100644
index 000000000..e64d649db
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/9680.rst
@@ -0,0 +1,9 @@
+.. change::
+    :tags: performance, sql
+    :tickets: 9680
+
+    Improved row processing performance for "binary" datatypes by making the
+    "bytes" handler conditional on a per driver basis.  As a result, the
+    "bytes" result handler has been disabled for nearly all drivers other than
+    psycopg2, all of which in modern forms support returning Python "bytes"
+    directly.  Pull request courtesy J. Nick Koston.
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 4a7e48ab8..0afd726fd 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -1347,7 +1347,8 @@ class TIMESTAMP(sqltypes._Binary):
         if self.convert_int:
 
             def process(value):
-                value = super_(value)
+                if super_:
+                    value = super_(value)
                 if value is not None:
                     # https://stackoverflow.com/a/30403242/34549
                     value = int(codecs.encode(value, "hex"), 16)
@@ -2974,6 +2975,8 @@ class MSDialect(default.DefaultDialect):
     supports_empty_insert = False
     favor_returning_over_lastrowid = True
 
+    returns_native_bytes = True
+
     supports_comments = True
     supports_default_metavalue = False
     """dialect supports INSERT... VALUES (DEFAULT) syntax -
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index eb9ccc606..2ed2bbc7a 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -2398,6 +2398,8 @@ class MySQLDialect(default.DefaultDialect):
 
     supports_native_enum = True
 
+    returns_native_bytes = True
+
     supports_sequences = False  # default for MySQL ...
     # ... may be updated to True for MariaDB 10.3+ in initialize()
 
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 08ab35bea..a3e724cbe 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1405,6 +1405,7 @@ class OracleDialect(default.DefaultDialect):
 
     supports_simple_order_by_label = False
     cte_follows_insert = True
+    returns_native_bytes = True
 
     supports_sequences = True
     sequences_optional = False
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index ad5e346b7..8e9994293 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -2912,6 +2912,8 @@ class PGDialect(default.DefaultDialect):
     postfetch_lastrowid = False
     use_insertmanyvalues = True
 
+    returns_native_bytes = True
+
     insertmanyvalues_implicit_sentinel = (
         InsertmanyvaluesSentinelOpts.ANY_AUTOINCREMENT
         | InsertmanyvaluesSentinelOpts.USE_INSERT_FROM_SELECT
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index e28bd8fda..5cdd34183 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -600,6 +600,8 @@ class PGDialect_psycopg2(_PGDialect_common_psycopg):
     psycopg2_version = (0, 0)
     use_insertmanyvalues_wo_returning = True
 
+    returns_native_bytes = False
+
     _has_native_hstore = True
 
     colspecs = util.update_copy(
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index a40e3d256..71da4d0ef 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -486,6 +486,7 @@ class _SQLite_pysqliteDate(DATE):
 class SQLiteDialect_pysqlite(SQLiteDialect):
     default_paramstyle = "qmark"
     supports_statement_cache = True
+    returns_native_bytes = True
 
     colspecs = util.update_copy(
         SQLiteDialect.colspecs,
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 8992334ee..d60428287 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -157,6 +157,8 @@ class DefaultDialect(Dialect):
     supports_native_enum = False
     supports_native_boolean = False
     supports_native_uuid = False
+    returns_native_bytes = False
+
     non_native_boolean_check_constraint = True
 
     supports_simple_order_by_label = True
diff --git a/lib/sqlalchemy/engine/interfaces.py b/lib/sqlalchemy/engine/interfaces.py
index 0216c155d..e4914551c 100644
--- a/lib/sqlalchemy/engine/interfaces.py
+++ b/lib/sqlalchemy/engine/interfaces.py
@@ -1055,6 +1055,14 @@ class Dialect(EventTarget):
 
     """
 
+    returns_native_bytes: bool
+    """indicates if Python bytes() objects are returned natively by the
+    driver for SQL "binary" datatypes.
+
+    .. versionadded:: 2.0.11
+
+    """
+
     construct_arguments: Optional[
         List[Tuple[Type[Union[SchemaItem, ClauseElement]], Mapping[str, Any]]]
     ] = None
diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py
index 4e7514e38..ce579cca2 100644
--- a/lib/sqlalchemy/sql/sqltypes.py
+++ b/lib/sqlalchemy/sql/sqltypes.py
@@ -934,6 +934,9 @@ class _Binary(TypeEngine[bytes]):
     # both sqlite3 and pg8000 seem to return it,
     # psycopg2 as of 2.5 returns 'memoryview'
     def result_processor(self, dialect, coltype):
+        if dialect.returns_native_bytes:
+            return None
+
         def process(value):
             if value is not None:
                 value = bytes(value)
diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index 3ba7f8e89..72f1e8c10 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -279,7 +279,6 @@ class ArrayTest(_LiteralRoundTripFixture, fixtures.TablesTest):
 
 
 class BinaryTest(_LiteralRoundTripFixture, fixtures.TablesTest):
-    __requires__ = ("binary_literals",)
     __backend__ = True
 
     @classmethod
@@ -294,14 +293,15 @@ class BinaryTest(_LiteralRoundTripFixture, fixtures.TablesTest):
             Column("pickle_data", PickleType),
         )
 
-    def test_binary_roundtrip(self, connection):
+    @testing.combinations(b"this is binary", b"7\xe7\x9f", argnames="data")
+    def test_binary_roundtrip(self, connection, data):
         binary_table = self.tables.binary_table
 
         connection.execute(
-            binary_table.insert(), {"id": 1, "binary_data": b"this is binary"}
+            binary_table.insert(), {"id": 1, "binary_data": data}
         )
         row = connection.execute(select(binary_table.c.binary_data)).first()
-        eq_(row, (b"this is binary",))
+        eq_(row, (data,))
 
     def test_pickle_roundtrip(self, connection):
         binary_table = self.tables.binary_table