Hi Everyone,
SQLAlchemy nicely reflects tables and builds model using automap. When
providing the parameter shema to reflect, tables from that schema are
discovered. I've noticed that tables from other schemas will also be
included if there is a foreign key to the other schema.
It's all good but what should I do to reflect and automap *only* tables
from the schema i.e. ignore tables and relationships to other schemas?
I've done it by removing unwanted:
- foreign keys that refer to other schemas from table objects
- foreign keys constraints that refer to other schemas from table objects
- foreign keys that refer to other schemas from column objects
I've attached the script that does what I've explained above.
It seems to work and no errors when reflect, automap and making queries
even with joins but is it OK to do it this way?
Much appreciate any comments.
Cheers,
Michal
--
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/5984bc30-5325-4053-b42e-694f96af9516n%40googlegroups.com.
import functools
from sqlalchemy import (
create_engine,
MetaData,
PrimaryKeyConstraint,
ForeignKeyConstraint
)
from sqlalchemy.ext.automap import automap_base
def is_target_in_schema(target_full_name, schema):
"""Checks if target_full_name is in the schema.
The target_full_name is dot separated [schema].[table].[column]
It may be [table].[column] when
"""
if schema is None:
schema = ''
try:
referred_schema = target_full_name.split('.')[-3]
except IndexError:
referred_schema = ''
return schema == referred_schema
def pk_and_local_fk_constraints(c, db_schema=None):
"""Function for filter.
Filter out foreign key constraints that point to different schema.
"""
if isinstance(c, PrimaryKeyConstraint):
return True
if isinstance(c, ForeignKeyConstraint):
target_fullname = c.elements[0].target_fullname
return is_target_in_schema(target_fullname, db_schema)
return False
connstr = 'mssql://test:[email protected]:1433/test?driver=FreeTDS&TDS_Version=7.0'
engine = create_engine(connstr)
db_schema = 'Customer'
metadata = MetaData()
metadata.reflect(engine, schema=db_schema, resolve_fks=False)
for table in metadata.tables.values():
# removes fk from columns that refer outside the schema
for col in table.columns:
col.foreign_keys = {
fk for fk in col.foreign_keys
if is_target_in_schema(fk.target_fullname, db_schema)
}
# removes fk that refer outside the schema
table.foreign_keys = {
fk for fk in table.foreign_keys
if is_target_in_schema(fk.target_fullname, db_schema)
}
# keep only pk constraints and fk constraints within the schema
table.constraints = set(
filter(
functools.partial(
pk_and_local_fk_constraints, db_schema=db_schema
),
table.constraints
)
)
Base = automap_base(metadata=metadata)
Base.prepare(engine)
classes = [(c.__name__) for c in Base.classes]
classes.sort()
print(classes)
print('Dealers' in classes)
# output, Dealers not present in reflected model
# >>>> ['People', 'Service', 'Vehicles']
# >>>> False
