Sqlalchemy: SQLAlchemy's automap does not work for tables that inherit primary key in PostgresQL

Created on 17 Feb 2020 · 3Comments · Source: sqlalchemy/sqlalchemy

Basically, I would like all of my tables to have the same increasing id, along with a date_created field, so I wrote a script like this:

create table base (
  id serial primary key,
  date_created timestamp default current_timestamp
);

create table users (
  username text,
  password text,
) inherits ("base");

create table books(
  name text,
  authors text
) inherits ("base");

In my Python code, if I use automap of SQLAlchemy, it would only give me base as the mapped class.

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

engine = create_engine("postgres_connection_string")

Base.prepare(engine, reflect=True)

for class_ in Base.classes:
    print(class_)

# should print 'base' and nothing else

If I add a primary key that is the same as base:

create table users (
  id serial primary key, -- an annoying fix
  username text,
  password text
) inherits ("base");

The Python code above would give me base and users.

The fix is annoying, so do I have a "cleaner" way?

Edit:

My Python version is 3.7, and SQLAlchemy's version is 1.3.13, but they should not be a concern here, should they not?

duplicate question

Source

thanhnguyen2187

All 3 comments

we don't reflect "inherits" partitions right now and that is #1803. apparently all of the tables and columns of the sub-tables are reflected, however, it does not make sense that the constraints would be copied from the base table to the sub-table unless there were some qualifying attribute that indicated these constraints are "inherited" and would not be copied out in a future DDL operation.

So to make this work, you would need to use an event to change the PrimaryKeyConstraint at reflection time.

But there's no way to do it without some kind of hardcoded convention, unless you wanted to query the PG database for the "inherits" value directly and figure out if a given column will in fact behave like a primary key.

anyway, if you can make a rule like, "columns named "id" are primary key columns", that looks like this:

from sqlalchemy import create_engine
from sqlalchemy import event
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy.schema import Table


@event.listens_for(Table, "after_parent_attach")
def before_parent_attach(target, parent):
    if not target.primary_key and "id" in target.c:
        target.primary_key.columns.add(target.c.id)


e = create_engine("postgresql://scott:tiger@localhost/test")

m = MetaData()


m.reflect(e)

you can adjust the event above to whatever heuristic you want to use.