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 1

In order to map a table SQLAlchemy needs there to be at least one column denoted as a primary key column. 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 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() method:

print(insp.get_foreign_table_names())

Answer 2

Building on sibling answer by @ilja.

When using the SQLAlchemy automap feature to automatically generate mapped classes and relationships from an existing database schema, I found that the __mapper_args__ solution didn't create the model.

This alternative method where you manually define the private key will correctly enable automap to create your model.

from sqlalchemy import Column, create_engine, Text
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.schema import Table

Base = automap_base()

engine = create_engine(conn_str, convert_unicode=True)

pk = Column('uid', Text, primary_key=True)
test_table = Table(
    'test_table', Base.metadata, pk, autoload=True, autoload_with=engine
)

# Inspect postgres schema
Base.prepare(engine, reflect=True)

print(dict(Base.classes))
print(test_table)