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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f50d2591-2f63-4192-bffc-516e54fc7232n%40googlegroups.com.
