SQLAlchemy not finding Postgres table connected with postgres_fdw
Question: Please excuse any terminology typos, don't have a lot of experience with
databases other than SQLite. I'm trying to replicate what I would do in SQLite
where I could ATTACH a database to a second database and query across all the
tables. I wasn't using SQLAlchemy with SQLite

I'm working with SQLAlchemy 1.0.13, Postgres 9.5 and Python 3.5.2 (using
Anaconda) on Win7/54. I have connected two databases (on localhost) using
postgres_fdw and imported a few of the tables from the secondary database. I
can successfully manually query the connected table with SQL in PgAdminIII and
from Python using psycopg2. With SQLAlchemy I've tried:

    
    
    # Same connection string info that psycopg2 used
    engine = create_engine(conn_str, echo=True)
    
    class TestTable(Base):
        __table__ = Table('test_table', Base.metadata,
                          autoload=True, autoload_with=engine)
    
        # Added this when I got the error the first time
        # test_id is a primary key in the secondary table
        Column('test_id', Integer, primary_key=True)
    

and get the error:

    
    
    sqlalchemy.exc.ArgumentError: Mapper Mapper|TestTable|test_table could not
    assemble any primary key columns for mapped table 'test_table'
    

Then I tried:

    
    
    insp = reflection.Inspector.from_engine(engine)
    print(insp.get_table_names())
    

and the attached tables aren't listed (the tables from the primary database do
show up). Is there a way to do what I am trying to accomplish?


Answer: In order to map a table [SQLAlchemy needs there to be at least one column
denoted as a primary key
column](http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-
i-map-a-table-that-has-no-primary-key). This does not mean that the column
need actually be a primary key column in the eyes of the database, though it
is a good idea. Depending on how you've imported the table from your foreign
schema it may not have a representation of a primary key constraint, or any
other constraints for that matter. You can work around this by either
[overriding the reflected primary key
column](http://docs.sqlalchemy.org/en/latest/core/reflection.html#overriding-
reflected-columns) in the **`Table` instance** (not in the mapped classes
body), or better yet tell the mapper what columns comprise the candidate key:

    
    
    engine = create_engine(conn_str, echo=True)
    
    test_table = Table('test_table', Base.metadata,
                       autoload=True, autoload_with=engine)
    
    class TestTable(Base):
        __table__ = test_table
        __mapper_args__ = {
            'primary_key': (test_table.c.test_id, )  # candidate key columns
        }
    

To inspect foreign table names use the
[`PGInspector.get_foreign_table_names()`](http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.base.PGInspector.get_foreign_table_names)
method:

    
    
    print(insp.get_foreign_table_names())
    

