From a0e0f4c289b46c0c9a051c08d7f9a1929e0e30ce Mon Sep 17 00:00:00 2001 From: Ilya Pekelny Date: Thu, 24 Jul 2014 19:27:18 +0300 Subject: Public inspector method to load enum list Provide opportunity to get enums list via an inspector instance public interface. --- lib/sqlalchemy/dialects/postgresql/base.py | 12 ++++++++++-- 1 file changed, 10 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 5ff2f7c61..3356e9d4b 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1574,6 +1574,12 @@ class PGInspector(reflection.Inspector): return self.dialect.get_table_oid(self.bind, table_name, schema, info_cache=self.info_cache) + def load_enums(self, conn, schema=None): + """Return a enums list. + + A per-database and per-schema enums list.""" + schema = schema or self.default_schema_name + return self.dialect._load_enums(conn, schema) class CreateEnumType(schema._CreateDropBase): @@ -2424,7 +2430,8 @@ class PGDialect(default.DefaultDialect): for name, uc in uniques.items() ] - def _load_enums(self, connection): + def _load_enums(self, connection, schema=None): + schema = schema or self.default_schema_name if not self.supports_native_enum: return {} @@ -2440,8 +2447,9 @@ class PGDialect(default.DefaultDialect): LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid WHERE t.typtype = 'e' + AND n.nspname = '%s' ORDER BY "name", e.oid -- e.oid gives us label order - """ + """ % schema s = sql.text(SQL_ENUMS, typemap={ 'attname': sqltypes.Unicode, -- cgit v1.2.1 From f39767ad727fcc9493d41451d7112d4f3459e9c4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 13 Aug 2014 17:42:33 -0400 Subject: - public method name is get_enums() - return a list of dicts like other methods do - don't combine 'schema' with 'name', leave them separate - support '*' argument so that we can retrieve cross-schema if needed - remove "conn" argument - use bound parameters for 'schema' in SQL - order by schema, name, label - adapt _load_enums changes to column reflection - changelog - module docs for get_enums() - add drop of enums to --dropfirst --- lib/sqlalchemy/dialects/postgresql/base.py | 93 +++++++++++++++++++++--------- 1 file changed, 67 insertions(+), 26 deletions(-) (limited to 'lib/sqlalchemy/dialects/postgresql/base.py') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 3356e9d4b..c2b1d66f4 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -401,6 +401,23 @@ The value passed to the keyword argument will be simply passed through to the underlying CREATE INDEX command, so it *must* be a valid index type for your version of PostgreSQL. +Special Reflection Options +-------------------------- + +The :class:`.Inspector` used for the Postgresql backend is an instance +of :class:`.PGInspector`, which offers additional methods:: + + from sqlalchemy import create_engine, inspect + + engine = create_engine("postgresql+psycopg2://localhost/test") + insp = inspect(engine) # will be a PGInspector + + print(insp.get_enums()) + +.. autoclass:: PGInspector + :members: + + """ from collections import defaultdict import re @@ -1570,16 +1587,31 @@ class PGInspector(reflection.Inspector): reflection.Inspector.__init__(self, conn) def get_table_oid(self, table_name, schema=None): - """Return the oid from `table_name` and `schema`.""" + """Return the OID for the given table name.""" return self.dialect.get_table_oid(self.bind, table_name, schema, info_cache=self.info_cache) - def load_enums(self, conn, schema=None): - """Return a enums list. - A per-database and per-schema enums list.""" + def get_enums(self, schema=None): + """Return a list of ENUM objects. + + Each member is a dictionary containing these fields: + + * name - name of the enum + * schema - the schema name for the enum. + * visible - boolean, whether or not this enum is visible + in the default search path. + * labels - a list of string labels that apply to the enum. + + :param schema: schema name. If None, the default schema + (typically 'public') is used. May also be set to '*' to + indicate load enums for all schemas. + + .. versionadded:: 1.0.0 + + """ schema = schema or self.default_schema_name - return self.dialect._load_enums(conn, schema) + return self.dialect._load_enums(self.bind, schema) class CreateEnumType(schema._CreateDropBase): @@ -2045,7 +2077,12 @@ class PGDialect(default.DefaultDialect): c = connection.execute(s, table_oid=table_oid) rows = c.fetchall() domains = self._load_domains(connection) - enums = self._load_enums(connection) + enums = dict( + ( + "%s.%s" % (rec['schema'], rec['name']) + if not rec['visible'] else rec['name'], rec) for rec in + self._load_enums(connection, schema='*') + ) # format columns columns = [] @@ -2119,10 +2156,9 @@ class PGDialect(default.DefaultDialect): elif attype in enums: enum = enums[attype] coltype = ENUM - if "." in attype: - kwargs['schema'], kwargs['name'] = attype.split('.') - else: - kwargs['name'] = attype + kwargs['name'] = enum['name'] + if not enum['visible']: + kwargs['schema'] = enum['schema'] args = tuple(enum['labels']) break elif attype in domains: @@ -2447,32 +2483,37 @@ class PGDialect(default.DefaultDialect): LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace LEFT JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid WHERE t.typtype = 'e' - AND n.nspname = '%s' - ORDER BY "name", e.oid -- e.oid gives us label order - """ % schema + """ + + if schema != '*': + SQL_ENUMS += "AND n.nspname = :schema " + + # e.oid gives us label order within an enum + SQL_ENUMS += 'ORDER BY "schema", "name", e.oid' s = sql.text(SQL_ENUMS, typemap={ 'attname': sqltypes.Unicode, 'label': sqltypes.Unicode}) + + if schema != '*': + s = s.bindparams(schema=schema) + c = connection.execute(s) - enums = {} + enums = [] + enum_by_name = {} for enum in c.fetchall(): - if enum['visible']: - # 'visible' just means whether or not the enum is in a - # schema that's on the search path -- or not overridden by - # a schema with higher precedence. If it's not visible, - # it will be prefixed with the schema-name when it's used. - name = enum['name'] - else: - name = "%s.%s" % (enum['schema'], enum['name']) - - if name in enums: - enums[name]['labels'].append(enum['label']) + key = (enum['schema'], enum['name']) + if key in enum_by_name: + enum_by_name[key]['labels'].append(enum['label']) else: - enums[name] = { + enum_by_name[key] = enum_rec = { + 'name': enum['name'], + 'schema': enum['schema'], + 'visible': enum['visible'], 'labels': [enum['label']], } + enums.append(enum_rec) return enums -- cgit v1.2.1