`Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models

[Vitaly Kruglikov]

Dear all,

I am using:

sqlalchemy==1.3.18

psycopg2==2.8.4

connection url schema: "postgresql+psycopg2://..."

postgres 10.x

when I define an explicit AutomapBase-derived model for 'sqa_global_context'  table with only the primary key, I expected that running `Base.metadata.reflect(bind=database.ENGINE, only=['sqa_global_context']); Base.prepare()` would backfill missing columns and relationships in that table. However, after running `Base.prepare(engine, reflect=True)`, the missing columns and relationships are not populated in my table.

My code looks like this:

```

_AutomapBase = automap.automap_base()

class SqaGlobalContext(_AutomapBase):

    """Type template for the SqaGlobalContext table."""

    __tablename__ = 'sqa_global_context'

    key = sa.Column(

        sa.Integer, primary_key=True, server_default=sa.text('0'))

    _AutomapBase.metadata.reflect(bind=ENGINE, only=['ewf_sqa_global_context', <other related table names of interest>])

    _AutomapBase.prepare()

```

The reason I use `_AutomapBase.metadata.reflect(ENGINE, only=[...])` followed by `_AutomapBase.prepare()` instead of just `_AutomapBase.prepare(ENGINE, reflect=True)` is because `_AutomapBase.prepare(ENGINE, reflect=True)` ends up reflecting **all** the tables in the database and I wanted to avoid the unnecessary overhead of reflecting the many additional tables that are managed by another subsystem not of interest to my application.

So, after running the above code, I expected `SqaGlobalContext` model to be populated with additional columns (`created_at`, `updated_at`, etc.) and relationships based on foreign keys (see below for actual table DDL). But the missing columns and relationships didn't get added:

```

dir(SqaGlobalContext)

['__abstract__', '__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__mapper__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__table__', '__tablename__', '__weakref__', '_decl_class_registry', '_sa_class_manager', '_sa_decl_prepare', '_sa_raise_deferred_config', 'classes', 'key', 'metadata', 'prepare']

```

As you can see from `dir(SqaGlobalContext)` - the additional columns didn't get populated in `SqaGlobalContext`. So, how can I make this work without reflecting the all the tables in the database?

Alembic outputs this when generating this table:

```

INFO  [sqlalchemy.engine.base.Engine] 

CREATE TABLE ewf_sqa_global_context (

key INTEGER DEFAULT 0 NOT NULL, 

created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 

updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, 

testing_suspended BOOLEAN DEFAULT false NOT NULL, 

metadata_tag VARCHAR(256) DEFAULT NULL, 

attempt_number INTEGER DEFAULT NULL, 

attempt_context_id INTEGER DEFAULT NULL, 

CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key), 

CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata FOREIGN KEY(metadata_tag) REFERENCES ewf_empower_metadata (tag) ON DELETE RESTRICT ON UPDATE CASCADE, 

CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183 FOREIGN KEY(attempt_context_id) REFERENCES ewf_sqa_attempt_context (key) ON DELETE RESTRICT ON UPDATE CASCADE

```

Actual Schema in database.

```

CREATE TABLE public.ewf_sqa_global_context

(

    key integer NOT NULL DEFAULT 0,

    created_at timestamp with time zone NOT NULL DEFAULT now(),

    updated_at timestamp with time zone NOT NULL DEFAULT now(),

    testing_suspended boolean NOT NULL DEFAULT false,

    metadata_tag character varying(256) COLLATE pg_catalog."default" DEFAULT NULL::character varying,

    attempt_number integer,

    attempt_context_id integer,

    CONSTRAINT pk_ewf_sqa_global_context PRIMARY KEY (key),

    CONSTRAINT fk_ewf_sqa_global_context_attempt_context_id_ewf_sqa_at_0183 FOREIGN KEY (attempt_context_id)

        REFERENCES public.ewf_sqa_attempt_context (key) MATCH SIMPLE

        ON UPDATE CASCADE

        ON DELETE RESTRICT,

    CONSTRAINT fk_ewf_sqa_global_context_metadata_tag_ewf_empower_metadata FOREIGN KEY (metadata_tag)

        REFERENCES public.ewf_empower_metadata (tag) MATCH SIMPLE

        ON UPDATE CASCADE

        ON DELETE RESTRICT

```

[Vitaly Kruglikov]

I just noticed a TYPO - but google groups doesn't let me edit my post. In a couple of places in my post, I accidentally entered `sqa_global_context` as the table name instead of `ewf_sqa_global_context`. But in actual code that I am executing, it's `ewf_sqa_global_context` everywhere.

[Mike Bayer]

your reflect() call requires extend_existing=True in this case otherwise existing Table objects as the one you are creating with SqaGlobalContext will not be affected.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/280b0e8e-99e7-4c53-af65-297ad1ed5ba4o%40googlegroups.com.

[Vitaly Kruglikov]

Thanks Mike! `extend_existing=True` is having the partially-specified explicit models pick up all the remaining columns.