Re: [sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
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', ])
> _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
Re: [sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
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', ])
> _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
[sqlalchemy] `Base.prepare()` doesn't backfill missing columns and relationships in AutomapBase-derived models
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', ])
_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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/280b0e8e-99e7-4c53-af65-297ad1ed5ba4o%40googlegroups.com.
