Thanks Mike! `extend_existing=True` is having the partially-specified explicit models pick up all the remaining columns.
On Tuesday, August 18, 2020 at 11:29:39 AM UTC-7 Mike Bayer wrote: > 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. > > > > On Tue, Aug 18, 2020, at 1:59 PM, Vitaly Kruglikov wrote: > > 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 > ``` > > > -- > 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 > > <https://groups.google.com/d/msgid/sqlalchemy/280b0e8e-99e7-4c53-af65-297ad1ed5ba4o%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f50d2591-2f63-4192-bffc-516e54fc7232n%40googlegroups.com.