sqlalchemy-redshift · Tin-Nguyen · Oct 3, 2023 · Oct 3, 2023 · Oct 4, 2023 · Oct 4, 2023
diff --git a/sqlalchemy_redshift/dialect.py b/sqlalchemy_redshift/dialect.py
@@ -3,6 +3,7 @@
 import re
 from collections import defaultdict, namedtuple
 from logging import getLogger
+from typing import Any, Optional
 
 import pkg_resources
 import sqlalchemy as sa
@@ -438,9 +439,9 @@ def __new__(cls, name, schema=None, connection=None):
 
     def __str__(self):
         if self.schema is None:
-            return self.name
+            return RelationKey._unquote(self.name)
         else:
-            return self.schema + "." + self.name
+            return RelationKey._unquote(self.schema) + "." + RelationKey._unquote(self.name)
 
     @staticmethod
     def _unquote(part):
@@ -654,6 +655,9 @@ def visit_HLLSKETCH(self, type_, **kw):
 class RedshiftIdentifierPreparer(PGIdentifierPreparer):
     reserved_words = RESERVED_WORDS
 
+    def quote_schema(self, schema: Any, force: Optional[bool] = ...) -> str:
+        return schema
+
 
 class RedshiftDialectMixin(DefaultDialect):
     """
@@ -670,6 +674,7 @@ class RedshiftDialectMixin(DefaultDialect):
     statement_compiler = RedshiftCompiler
     ddl_compiler = RedshiftDDLCompiler
     preparer = RedshiftIdentifierPreparer
+    identifier_preparer = RedshiftIdentifierPreparer
     type_compiler = RedshiftTypeCompiler
     construct_arguments = [
         (sa.schema.Index, {
@@ -793,12 +798,12 @@ def get_check_constraints(self, connection, table_name, schema=None, **kw):
     def get_table_oid(self, connection, table_name, schema=None, **kw):
         """Fetch the oid for schema.table_name.
         Return null if not found (external table does not have table oid)"""
-        schema_field = '"{schema}".'.format(schema=schema) if schema else ""
+        schema_field = '{schema}.'.format(schema=schema) if schema else ""
 
         result = connection.execute(
             sa.text(
                 """
-                select '{schema_field}"{table_name}"'::regclass::oid;
+                select '{schema_field}{table_name}'::regclass::oid;
                 """.format(
                     schema_field=schema_field,
                     table_name=table_name
@@ -857,8 +862,8 @@ def get_foreign_keys(self, connection, table_name, schema=None, **kw):
             fkey_d = {
                 'name': conname,
                 'constrained_columns': constrained_columns,
-                'referred_schema': referred_schema,
-                'referred_table': referred_table,
+                'referred_schema': self.unquote(referred_schema),
+                'referred_table': self.unquote(referred_table),
                 'referred_columns': referred_columns,
             }
             fkeys.append(fkey_d)
@@ -908,6 +913,16 @@ def get_indexes(self, connection, table_name, schema, **kw):
         """
         return []
 
+    @staticmethod
+    def unquote(text):
+        if text is None:
+            return None
+
+        if text.startswith('"') and text.endswith('"'):
+            return text[1:-1]
+
+        return text
+
     @reflection.cache
     def get_unique_constraints(self, connection, table_name,
                                schema=None, **kw):
@@ -977,7 +992,7 @@ def _get_table_or_view_names(self, relkind, connection, schema=None, **kw):
         relation_names = []
         for key, relation in all_relations.items():
             if key.schema == schema and relation.relkind == relkind:
-                relation_names.append(key.name)
+                relation_names.append(self.unquote(key.name))
         return relation_names
 
     def _get_column_info(self, *args, **kwargs):
@@ -1110,6 +1125,7 @@ def _get_all_relation_info(self, connection, **kw):
     @reflection.cache
     def _get_schema_column_info(self, connection, **kw):
         schema = kw.get('schema', None)
+        schema = self.unquote(schema)
         schema_clause = (
             "AND schema = '{schema}'".format(schema=schema) if schema else ""
         )

diff --git a/tests/rs_sqla_test_utils/models.py b/tests/rs_sqla_test_utils/models.py
@@ -32,6 +32,17 @@ class BasicInOtherSchema(Base):
     col1 = sa.Column(sa.Integer(), primary_key=True)
 
 
+class BasicInIncludingDotSchema(Base):
+    __tablename__ = 'basic'
+    __table_args__ = (
+        {'schema': 'dotted.schema',
+         'redshift_diststyle': 'KEY',
+         'redshift_distkey': 'col1',
+         'redshift_sortkey': 'col1'}
+    )
+    col1 = sa.Column(sa.Integer(), primary_key=True)
+
+
 class ReflectionDistKey(Base):
     __tablename__ = 'reflection_distkey'
     col1 = sa.Column(sa.Integer(), primary_key=True)

diff --git a/tests/test_compiler.py b/tests/test_compiler.py
@@ -1,8 +1,17 @@
 from sqlalchemy import func, select
 
+from tests.rs_sqla_test_utils import models
+
 
 def test_func_now(stub_redshift_dialect):
     dialect = stub_redshift_dialect
     s = select([func.NOW().label("time")])
     compiled = s.compile(dialect=dialect)
     assert str(compiled) == "SELECT SYSDATE AS time"
+
+def test_unquoting_schema(stub_redshift_dialect):
+    dialect = stub_redshift_dialect
+    s = select(models.BasicInIncludingDotSchema.col1).where(models.BasicInIncludingDotSchema.col1 == 1)
+    compiled = s.compile(dialect=dialect)
+    assert "dotted.schema" in str(compiled)
+    assert "\"dotted.schema\"" not in str(compiled)