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.
