experiment.py

"""
SQLAlchemy with Postgres schemas reflection

This experiment creates some tables across schemas and asks sqlalchemy to reflect those tables.
The tables are not connected with constraints as that would allow reflection to *just* work.

"""
import os
from pprint import pprint

import sqlalchemy as sa


# Create the connection engine
# export DB_URL="postgresql://test:test@db/experiment"
engine = sa.create_engine(os.environ['DB_URL'])
# Create an inspector instance for getting the schema names
inspector = sa.inspect(engine)

# Create some tables by handle
with engine.connect() as conn:
    conn.execute("""\
create schema foo;
create schema bar;
create table tableau (id serial primary key, num integer);
create table foo.tableau (id serial primary key, word text);
create table bar.other (id serial primary key, word text);
insert into tableau (num) values (1), (2), (3);
insert into foo.tableau (word) values ('four'), ('five'), ('six');
insert into bar.other (word) values ('seven'), ('eight'), ('nine');
""")

# Reflect the tables
metadata = sa.MetaData()
metadata.reflect(bind=engine)
# Reflect the tables in the individual schemas
for schema in inspector.get_schema_names():
    # Ignore the internal informational schema and default public schema
    if schema in ['information_schema', 'public']:
        continue
    metadata.reflect(bind=engine, schema=schema)

# Print out the table names
print("Tables:  " + ', '.join(metadata.tables.keys()))
print('-'*80)
pprint(metadata.tables)

requirements.txt

sqlalchemy
psycopg2-binary