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/63c55e5a-157a-48c1-9aae-5c1c67aa63e7%40www.fastmail.com.
