使用SQLAlchemy从postgres数据库反映每个模式

Question

我有一个现有的数据库，它有两个模式，名为schools和{}，包含在{}的实例中，并通过从该实例继承的两个不同的类

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

以及

^{pr2}$

我可以使用Base实例作为Base.metadata.create_all(bind=engine)在postgres中的测试数据库中重新创建它。如果我查询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)]

从psqlcli

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

但是，如果我想在declarative_base的其他实例中反映该数据库，则不会反映任何内容。在

有点像

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([])

我知道reflect接受一个schema作为参数，但我希望在反射期间一次获得所有这些参数。因为某些原因，我可以一次完成这个任务。在

有办法吗？在

Answer 1

当您调用metadata.reflect()时，它将只反映默认架构（您对其拥有权限的search_path中的第一个）。因此，如果您的搜索路径是public,students,school，它将只反映模式public中的表。如果您对schema public没有权限，public模式将被跳过，并将默认仅反映students。在

默认模式由SELECT current_schema();检索

以反映其他模式 您需要为每个模式调用metadata.reflect()。在

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.

用模式作为表名前缀的示例 在

^{pr2}$

**作为奖励，这里有一个小片段，它将在每个模式中公开动态子模块中的所有表，以便您可以访问它**

创建一个文件，即数据库并放置以下内容

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)

使用automap base和要注册模式的模块的__dict__调用此函数。在

 register_classes(base, globals())

或者

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

然后你会得到

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