Dealing with Automap Errors by excluding tables

Question

I'm using SQLAlchemy's automap to autogenerate the schema from a Postgres database managed by Odoo (direct access of the database is WAAAYYY faster than using the Odoo API). But following examples from the docs are giving me problems.

I'm trying to only parse specific tables per the advice here:

# we can reflect it ourselves from a database, using options
# such as 'only' to limit what tables we look at...
metadata.reflect(engine, only=['user', 'address'])

My code:

import sqlalchemy as sa
from sqlalchemy.ext.automap import automap_base
from app import odb
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy.orm.relationships import RelationshipProperty

metadata = sa.MetaData()
metadata.reflect(odb, only=['sale_order'])
Base = automap_base(metadata=metadata)
Base.prepare()
Order = Base.classes.sale_order

But I'm erroring out on a mapping for a table that I didn't specify.

[ins] In [1]: from app.omodels import Order
/home/mas/pe/app/omodels/omodels.py:8: SAWarning: Skipped unsupported reflection of expression-based index res_partner_vat_index
  metadata.reflect(odb, only=['sale_order'])
---------------------------------------------------------------------------
ArgumentError                             Traceback (most recent call last)
<ipython-input-1-39a04770d5b4> in <module>
----> 1 from app.omodels import Order

~/pe/app/omodels/__init__.py in <module>
----> 1 from .omodels import *

~/pe/app/omodels/omodels.py in <module>
      8 metadata.reflect(odb, only=['sale_order'])
      9 Base = automap_base(metadata=metadata)
---> 10 Base.prepare()
     11 Order = Base.classes.sale_order
     12

<string> in prepare(cls, autoload_with, engine, reflect, schema, classname_for_table, collection_class, name_for_scalar_relationship, name_for_collection_rela
tionship, generate_relationship, reflection_options)

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py in warned(fn, *args, **kwargs)
    296                         stacklevel=3,
    297                     )
--> 298             return fn(*args, **kwargs)
    299
    300         doc = fn.__doc__ is not None and fn.__doc__ or ""

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/ext/automap.py in prepare(cls, autoload_with, engine, reflect, schema, classname_for_table, collection_class
, name_for_scalar_relationship, name_for_collection_relationship, generate_relationship, reflection_options)
    926
    927             for map_config in _DeferredMapperConfig.classes_for_base(cls):
--> 928                 map_config.map()
    929
    930     _sa_decl_prepare = True

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/decl_base.py in map(self, mapper_kw)
   1070     def map(self, mapper_kw=util.EMPTY_DICT):
   1071         self._configs.pop(self._cls, None)
-> 1072         return super(_DeferredMapperConfig, self).map(mapper_kw)
   1073
   1074

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/decl_base.py in map(self, mapper_kw)
    990         return self.set_cls_attribute(
    991             "__mapper__",
--> 992             mapper_cls(self.cls, self.local_table, **self.mapper_args),
    993         )
    994

<string> in __init__(self, class_, local_table, properties, primary_key, non_primary, inherits, inherit_condition, inherit_foreign_keys, always_refresh, versi
on_id_col, version_id_generator, polymorphic_on, _polymorphic_map, polymorphic_identity, concrete, with_polymorphic, polymorphic_load, allow_partial_pks, batc
h, column_prefix, include_properties, exclude_properties, passive_updates, passive_deletes, confirm_deleted_rows, eager_defaults, legacy_is_orphan, _compiled_
cache_size)

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py in warned(fn, *args, **kwargs)
    296                         stacklevel=3,
    297                     )
--> 298             return fn(*args, **kwargs)
    299
    300         doc = fn.__doc__ is not None and fn.__doc__ or ""
~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py in __init__(self, class_, local_table, properties, primary_key, non_primary, inherits, inherit_condition, inherit_foreign_keys, always_refresh, version_id_col, version_id_generator, polymorphic_on, _polymorphic_map, polymorphic_identity, concrete, with_polymorphic, polymorphic_load, allow_partial_pks, batch, column_prefix, include_properties, exclude_properties, passive_updates, passive_deletes, confirm_deleted_rows, eager_defaults, legacy_is_orphan, _compiled_cache_size)
    681             self._configure_inheritance()
    682             self._configure_class_instrumentation()
--> 683             self._configure_properties()
    684             self._configure_polymorphic_setter()
    685             self._configure_pks()

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py in _configure_properties(self)
   1439
   1440             self._configure_property(
-> 1441                 column_key, column, init=False, setparent=True
   1442             )
   1443

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py in _configure_property(self, key, prop, init, setparent)
   1674
   1675         if not isinstance(prop, MapperProperty):
-> 1676             prop = self._property_from_column(key, prop)
   1677
   1678         if isinstance(prop, properties.ColumnProperty):

~/pe/.venv/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py in _property_from_column(self, key, prop)
   1866                 "use the 'include_properties' or 'exclude_properties' "
   1867                 "mapper arguments to control specifically which table "
-> 1868                 "columns get mapped." % (key, self, column.key, prop)
   1869             )
   1870

ArgumentError: WARNING: when configuring property 'product_packaging' on mapped class stock_move->stock_move, column 'product_packaging' conflicts with property '<RelationshipProperty at 0x7fe61ba87f48; product_packaging>'. To resolve this, map the column to the class under a different name in the 'properties' dictionary.  Or, to remove all awareness of the column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped.

QUESTION: How can I exclude this table (or only include the tables that I want) so that SQLA doesn't go needlessly parsing tables I don't need (it also seems to take quite a while to parse this database, which is huge)?

Alternatively, how can I override this specific error so that I can access my Order object? The Error/Warning says

To resolve this, map the column to the class under a different name in the 'properties' dictionary. Or, to remove all awareness of the column entirely (including its availability as a foreign key), use the 'include_properties' or 'exclude_properties' mapper arguments to control specifically which table columns get mapped.

But I haven't described the schema for this table, and I don't know where to put 'exclude_properties'.

Answer 1

You should be able to use the automap_base without metadata.reflect and let Base handle the reflection. In this way you can add the reflection_options to prepare and only the required table is mapped. The issue that you got is because metadata.reflect is from SQLAlchemy Core and automap_base is using SQLAlchemy ORM.

from sqlalchemy import create_engine, select
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session

DB_URL = ''

Base = automap_base()

engine = create_engine(DB_URL, future=True)

# reflect the tables
Base.prepare(autoload_with=engine, reflection_options={'only': ['res_users']})

# mapped classes are now created with names by default
# matching that of the table name.
OUsers = Base.classes.res_users

for mapped_class in Base.classes:
    print(mapped_class)

You can also do it with SQLAlchemy Core in the following way (without automap_base):

meta = MetaData()
meta.reflect(bind=someengine)
OUsers = meta.tables['resusers']

Relevant documentation:

• Automap Basic Use
• Reflection Database Object

Answer 2

Turns out this is a simple case of naming collision that is addressed in the SQLA docs: Handling Simple Naming Conflicts.

I resolved it with the following method ripped straight from the docs:

def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
    name = referred_cls.__name__.lower()
    local_table = local_cls.__table__
    if name in local_table.columns:
        newname = name + "_"
        warnings.warn(
            "Already detected name %s present.  using %s" %
            (name, newname))
        return newname
    return name


Base.prepare(engine, reflect=True,
    name_for_scalar_relationship=name_for_scalar_relationship)

Though I don't know where they're pulling warnings from, I assume just a stand in for pick-your-own-logger.

It's worth noting I haven't figured out how to get the automap to ignore all tables save the ones I want. This is the state of my code and it still seems to map all the tables:

import sqlalchemy as sa
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from sqlalchemy.orm.relationships import RelationshipProperty

from app import odb


def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
    """Deals with a naming conflict problem."""
    name = referred_cls.__name__.lower()
    local_table = local_cls.__table__
    if name in local_table.columns:
        newname = name + "_"
        return newname
    return name


Session = sessionmaker(odb)

metadata = sa.MetaData()
metadata.reflect(odb, only=['res_users'])
Base = automap_base(metadata=metadata)
Base.prepare(name_for_scalar_relationship=name_for_scalar_relationship)

OUser = Base.classes.res_users
Order = Base.classes.sale_order
Partner = Base.classes.res_partner

But at least it works.