introspect.py

import contextlib as c
import decorator as d

from pgconvert import schema as ps

@d.decorator
def with_cursor(f, conn, *args, **kwargs):
    with c.closing(conn.cursor()) as cursor:
        return f(cursor, *args, **kwargs)

@with_cursor
def extract(cur, database_names):
    databases = []
    for database_name in database_names:
        database = extract_database(cur, database_name)
        if database is not None:
            tables = extract_tables(cur, database)
            databases.append(database)
    return databases

def extract_database(cur, database_name):
    cur.execute('''
        SELECT
            SCHEMA_NAME
        FROM SCHEMATA
        WHERE
            SCHEMA_NAME = %s
    ''', (database_name,))
    for name, in cur:
        return ps.Database(name)

def extract_tables(cur, database):
    cur.execute('''
        SELECT
            TABLE_SCHEMA,
            TABLE_NAME,
            TABLE_COMMENT
        FROM TABLES
        WHERE
            TABLE_CATALOG = 'def'
            AND TABLE_SCHEMA = %s
    ''', (database.name,))
    for schema, name, comment in cur:
        database.add_table(schema, name, comment)
    return database.tables

schema.py

import collections as col

Table = col.namedtuple('Table', [
    'schema',
    'name',
    'comment'
])

_Database = col.namedtuple('Database', [
    'name',
    'tables'
])
class Database(_Database):
    def __new__(cls, name, tables=None):
        if tables is None:
            tables = []
        return super(cls, Database).__new__(cls, name, tables)
    
    def add_table(self, schema, name, comment):
        if schema != self.name:
            raise ValueError('Table schema `%s` does not match database schema `%s`' % (
                schema,
                self.name
            ))
        self.tables.append(Table(self, name, comment))