reflecting every schema from postgres DB using SQLAlchemy

Question

I have an existing database that has two schemas, named schools and students, contained in an instance of declarative_base and through two different classes that inherit from that instance

class DirectorioEstablecimiento(Base):
    __table_args__ = {'schema': 'schools'}
    __tablename__ = 'addresses'
    # some Columns are defined here

and

class Matricula(Base):
    __table_args__ = {'schema': 'students'}
    __tablename__ = 'enrollments'
    # some Columns are defined here

I can use the Base instance to as Base.metadata.create_all(bind=engine) to recreate it in a test DB I have in postgres. I can confirm this was done without problems if I query the pg_namespace

In [111]: engine.execute("SELECT * FROM pg_namespace").fetchall()
2017-12-13 18:04:01,006 INFO sqlalchemy.engine.base.Engine SELECT * FROM pg_namespace
2017-12-13 18:04:01,006 INFO sqlalchemy.engine.base.Engine {}

Out[111]: 
[('pg_toast', 10, None),
 ('pg_temp_1', 10, None),
 ('pg_toast_temp_1', 10, None),
 ('pg_catalog', 10, '{postgres=UC/postgres,=U/postgres}'),
 ('public', 10, '{postgres=UC/postgres,=UC/postgres}'),
 ('information_schema', 10, '{postgres=UC/postgres,=U/postgres}'),
 ('schools', 16386, None),
 ('students', 16386, None)]

and from the psql CLI

user# select * from pg_tables;

     schemaname     |          tablename           | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
--------------------+------------------------------+------------+------------+------------+----------+-------------+-------------
 schools            | addresses                    | diego      |            | t          | f        | f           | f
 students           | enrollments                  | diego      |            | t          | f        | f           | f
 pg_catalog         | pg_statistic                 | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_type                      | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_authid                    | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog         | pg_user_mapping              | postgres   |            | t          | f        | f           | f
-- other tables were omitted

However, if I want to reflect that database in some other instance of declarative_base nothing is reflected.

Something like

In [87]: Base.metadata.tables.keys()
Out[87]: dict_keys(['schools.addresses', 'students.enrollments'])

In [88]: new_base = declarative_base()

In [89]: new_base.metadata.reflect(bind=engine)

In [90]: new_base.metadata.tables.keys()
Out[90]: dict_keys([])

I understand that reflect accepts a schema as a parameter but I would like to obtain all of them at once during reflection. For some reason I can achieve this one at a time.

Is there a way to do this?

Answer 1

When you call metadata.reflect() it will only reflect the default schema (the first in your search_path for which you have permissions). So if your search_path is public,students,school it will only reflect the tables in schema public. If you do not have permissions on schema public, public schema will be skipped and will default to reflect only students.

The default schema is retrieved by SELECT current_schema();

In order to reflect other schemas you need to call metadata.reflect() for each schema.

metadata.reflect(schema='public')  # will reflect even if you do not have permissions on the tables in schema `public`, as long as you have access to pg_* system tables
metadata.reflect(schema='students')
metadata.reflect(schema='schools')

Note: When you reflect with an explicit schema

Reflected tables in metadata.tables will have the keys with the tables fully qualified schema name as in schema1.mytable, schema2.mytable

Any conflicting table names will be replaced with the later one. If you have any tables with the same name, you should implement your the function classname_for_table to prefix the names with the schema name.

An example of prefixing table names with the schema

def classname_for_table(base, tablename, table):
    schema_name = table.schema
    fqname = '{}.{}'.format(schema_name, tablename)
    return fqname


Base.prepare(classname_for_table=classname_for_table)

**As a bonus, here is a small snippet which will expose all tables within a dynamic submodule per schema so you can access it **

create a file ie. db.py and place the following

from types import ModuleType


def register_classes(base, module_dict):
    for name, table in base.classes.items():
        schema_name, table_name = name.split('.')
        class_name = table_name.title().replace('_', '')
        if schema_name not in module_dict:
            module = module_dict[schema_name] = ModuleType(schema_name)
        else:
            module = module_dict[schema_name]
        setattr(module, class_name, table)

Call this function with the automap base and the __dict__ of the module which you would like to register the schemas with.

 register_classes(base, globals())

or

import db
db.register_classes(base, db.__dict__)

and then you will get

import db
db.students.MyTable
db.schools.MyTable