SQLAlchemy not finding Postgres table connected with postgres_fdw
<p>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</p>

<p>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:</p>

<pre><code># 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)
</code></pre>

<p>and get the error:</p>

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

<p>Then I tried:</p>

<pre><code>insp = reflection.Inspector.from_engine(engine)
print(insp.get_table_names())
</code></pre>

<p>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?</p>

<p>In order to map a table <a href="http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key" rel="nofollow">SQLAlchemy needs there to be at least one column denoted as a primary key column</a>. 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 <a href="http://docs.sqlalchemy.org/en/latest/core/reflection.html#overriding-reflected-columns" rel="nofollow">overriding the reflected primary key column</a> in the <strong><code>Table</code> instance</strong> (not in the mapped classes body), or better yet tell the mapper what columns comprise the candidate key:</p>

<pre><code>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
    }
</code></pre>

<p>To inspect foreign table names use the <a href="http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.base.PGInspector.get_foreign_table_names" rel="nofollow"><code>PGInspector.get_foreign_table_names()</code></a> method:</p>

<pre><code>print(insp.get_foreign_table_names())
</code></pre>

python postgresql sqlalchemy postgres-fdw