standard_conforming_strings prior to PG 8.2
SQLAlchemy 9.1 does not support connecting to RedShfit because of the change in dialects/postgresql/base.py:
class PGDialect(default.DefaultDialect): ... def initialize(self, connection): ... self._backslash_escapes = connection.scalar( "show standard_conforming_strings" ) == 'off'
This crashes because there is no standard_conforming_string defined in redshift. I think this check needs to be added "self.server_version_info >= (8, 2)", as the documentation for postgres starts mentioning this in 8.2. http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html
This was working on older versions of SQLAlchemy but I need to also connect to a postgres 9.3 db in my app, so I needed to upgrade.
For now I've made a simple work around:
Register the dialect in my app:
from sqlalchemy.dialects import registry registry.register("redshift", "myapp.dialects.redshift", "RedShiftDialect")
Define a new dialect in my app:
from sqlalchemy.dialects.postgresql.base import ENUM from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect_psycopg2 from sqlalchemy import types as sqltypes class RedShiftDialect(PGDialect_psycopg2): def initialize(self, connection): # Don't call super, it will crash, manually copying code that super would call #super(PGDialect, self).initialize(connection) self.implicit_returning = self.server_version_info > (8, 2) and \ self.__dict__.get('implicit_returning', True) self.supports_native_enum = self.server_version_info >= (8, 3) if not self.supports_native_enum: self.colspecs = self.colspecs.copy() # pop base Enum type self.colspecs.pop(sqltypes.Enum, None) # psycopg2, others may have placed ENUM here as well self.colspecs.pop(ENUM, None) # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689 self.supports_smallserial = self.server_version_info >= (9, 2) # This breaks redshift, if a version check was here or exception # protection the RedShiftDialect would not be needed ###Suggest Adding Check: if self.server_version_info >= (8, 2): ### #self._backslash_escapes = connection.scalar( # "show standard_conforming_strings" # ) == 'off' # Don't call super, it will crash, manually copying code that super would call #super(PGDialect_psycopg2, self).initialize(connection) self._has_native_hstore = self.use_native_hstore and \ self._hstore_oids(connection.connection) is not None self._has_native_json = self.psycopg2_version >= (2, 5)