Merge "Improve array support on pg8000" into main

5 files changed, 36 insertions, 11 deletions

diff --git a/doc/build/changelog/unreleased_14/6023.rst b/doc/build/changelog/unreleased_14/6023.rst
new file mode 100644
index 000000000..88d9777ba
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/6023.rst
@@ -0,0 +1,6 @@
+.. change::
+    :tags: postgresql, pg8000
+    :tickets: 7167
+
+    Improve array handling when using PostgreSQL with the
+    pg8000 dialect.
diff --git a/lib/sqlalchemy/dialects/postgresql/array.py b/lib/sqlalchemy/dialects/postgresql/array.py
index 9659d31b9..0cb574dac 100644
--- a/lib/sqlalchemy/dialects/postgresql/array.py
+++ b/lib/sqlalchemy/dialects/postgresql/array.py
@@ -375,7 +375,7 @@ class ARRAY(sqltypes.ARRAY):
                 if value is None:
                     return value
                 # isinstance(value, util.string_types) is required to handle
-                # the # case where a TypeDecorator for and Array of Enum is
+                # the case where a TypeDecorator for and Array of Enum is
                 # used like was required in sa < 1.3.17
                 return super_rp(
                     handle_raw_string(value)
diff --git a/lib/sqlalchemy/dialects/postgresql/pg8000.py b/lib/sqlalchemy/dialects/postgresql/pg8000.py
index d42dd9560..a94f9dcdb 100644
--- a/lib/sqlalchemy/dialects/postgresql/pg8000.py
+++ b/lib/sqlalchemy/dialects/postgresql/pg8000.py
@@ -93,6 +93,8 @@ import decimal
 import re
 from uuid import UUID as _python_UUID
 
+from .array import ARRAY as PGARRAY
+from .base import _ColonCast
 from .base import _DECIMAL_TYPES
 from .base import _FLOAT_TYPES
 from .base import _INT_TYPES
@@ -256,6 +258,11 @@ class _PGBoolean(sqltypes.Boolean):
         return dbapi.BOOLEAN
 
 
+class _PGARRAY(PGARRAY):
+    def bind_expression(self, bindvalue):
+        return _ColonCast(bindvalue, self)
+
+
 _server_side_id = util.counter()
 
 
@@ -384,6 +391,7 @@ class PGDialect_pg8000(PGDialect):
             sqltypes.SmallInteger: _PGSmallInteger,
             sqltypes.BigInteger: _PGBigInteger,
             sqltypes.Enum: _PGEnum,
+            sqltypes.ARRAY: _PGARRAY,
         },
     )
 
diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py
index acf88f0da..2a4688bcc 100644
--- a/lib/sqlalchemy/sql/type_api.py
+++ b/lib/sqlalchemy/sql/type_api.py
@@ -633,7 +633,8 @@ class TypeEngine(Traversible):
         try:
             return dialect._type_memos[self]["impl"]
         except KeyError:
-            return self._dialect_info(dialect)["impl"]
+            pass
+        return self._dialect_info(dialect)["impl"]
 
     def _unwrapped_dialect_impl(self, dialect):
         """Return the 'unwrapped' dialect impl for this type.
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index dd0a1be0f..d1c0361e4 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -1443,7 +1443,6 @@ class ArrayRoundTripTest(object):
 
     __only_on__ = "postgresql"
     __backend__ = True
-    __unsupported_on__ = ("postgresql+pg8000",)
 
     ARRAY = postgresql.ARRAY
 
@@ -1962,14 +1961,8 @@ class ArrayRoundTripTest(object):
             (sqltypes.Unicode, unicode_values),
             (postgresql.JSONB, json_values),
             (sqltypes.Boolean, lambda x: [False] + [True] * x),
-            (
-                sqltypes.LargeBinary,
-                binary_values,
-            ),
-            (
-                postgresql.BYTEA,
-                binary_values,
-            ),
+            (sqltypes.LargeBinary, binary_values),
+            (postgresql.BYTEA, binary_values),
             (
                 postgresql.INET,
                 lambda x: [
@@ -2047,6 +2040,7 @@ class ArrayRoundTripTest(object):
             (postgresql.ENUM(AnEnum), enum_values),
             (sqltypes.Enum(AnEnum, native_enum=True), enum_values),
             (sqltypes.Enum(AnEnum, native_enum=False), enum_values),
+            (postgresql.ENUM(AnEnum, native_enum=True), enum_values),
         ]
 
         if not exclude_json:
@@ -2057,6 +2051,22 @@ class ArrayRoundTripTest(object):
                 ]
             )
 
+        _pg8000_skip_types = {
+            postgresql.HSTORE,  # return not parsed returned as string
+        }
+        for i in range(len(elements)):
+            elem = elements[i]
+            if (
+                elem[0] in _pg8000_skip_types
+                or type(elem[0]) in _pg8000_skip_types
+            ):
+                elem += (
+                    testing.skip_if(
+                        "postgresql+pg8000", "type not supported by pg8000"
+                    ),
+                )
+                elements[i] = elem
+
         return testing.combinations_list(
             elements, argnames="type_,gen", id_="na"
         )