Problems with composite secondary joins with DeferredReflection

146 views
Skip to first unread message

Cory Virok

unread,
Oct 30, 2019, 6:35:05 PM10/30/19
to sqlalchemy
I recently upgraded from SQLAlchemy 1.2 to 1.3 and one of the relationships I have no longer works. I'm trying to reflect an existing schema and provide a base class that provides some helper methods to all of my ORM classes. I'm also trying to defer reflection to after the ORM classes are prepared.

The error I'm seeing is:

  File "scratch.py", line 98, in <module>
    a = session.query(A)
  File "lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1544, in query
    return self._query_cls(entities, self, **kwargs)
  File "lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 168, in __init__
    self._set_entities(entities)
  File "lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 200, in _set_entities
    self._set_entity_selectables(self._entities)
  File "lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 231, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 4121, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 855, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2135, in _with_polymorphic_mappers
    configure_mappers()
  File "lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 3248, in configure_mappers
    mapper._post_configure_properties()
  File "lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1947, in _post_configure_properties
    prop.init()
  File "lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 196, in init
    self.do_init()
  File "lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1860, in do_init
    self._process_dependent_arguments()
  File "lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1889, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "lib/python2.7/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 294, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
  File "lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 734, in __missing__
    self[key] = val = self.creator(key)
  File "lib/python2.7/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 286, in _access_cls
    value = resolv(key)
  File "lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py", line 778, in _resolve
    cls._reflect_table(t1, engine)
  File "lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py", line 811, in _reflect_table
    schema=table.schema,
  File "<string>", line 2, in __new__
  File "lib/python2.7/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 492, in __new__
    table._init_existing(*args, **kw)
  File "lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 708, in _init_existing
    _extend_on=_extend_on,
  File "lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 619, in _autoload
    _extend_on=_extend_on,
  File "lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2160, in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1612, in run_callable
    return callable_(self, *args, **kwargs)
  File "lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 459, in reflecttable
    table, include_columns, exclude_columns, resolve_fks, **opts
  File "lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 629, in reflecttable
    table_name, schema, **table.dialect_kwargs
  File "lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 314, in get_table_options
    self.bind, table_name, schema, info_cache=self.info_cache, **kw
  File "<string>", line 2, in get_table_options
  File "lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2499, in get_table_options
    connection, table_name, schema, **kw
  File "lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2745, in _parsed_state_or_create
    info_cache=kw.get("info_cache", None),
  File "<string>", line 2, in _setup_parser
  File "lib/python2.7/site-packages/sqlalchemy/engine/reflection.py", line 56, in cache
    ret = fn(self, con, *args, **kw)
  File "lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2773, in _setup_parser
    connection, None, charset, full_name=full_name
  File "lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2876, in _show_create_table
    raise exc.NoSuchTableError(full_name)
sqlalchemy.exc.NoSuchTableError: `join`



from sqlalchemy import Column, Integer, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import relationship, Session

_Base = declarative_base()


class Base(_Base, DeferredReflection):
 __abstract__ = True


class A(Base):
 __tablename__ = 'a'

 id = Column(Integer, primary_key=True)
 b_id = Column(ForeignKey('b.id'))

 d = relationship("D",
 secondary="join(B, D, B.d_id == D.id)."
 "join(C, C.d_id == D.id)",
 primaryjoin="and_(A.b_id == B.id, A.id == C.a_id)",
 secondaryjoin="D.id == B.d_id",
 uselist=True
 )


class B(Base):
 __tablename__ = 'b'

 id = Column(Integer, primary_key=True)
 d_id = Column(ForeignKey('d.id'))


class C(Base):
 __tablename__ = 'c'

 id = Column(Integer, primary_key=True)
 a_id = Column(ForeignKey('a.id'))
 d_id = Column(ForeignKey('d.id'))


class D(Base):
 __tablename__ = 'd'

 id = Column(Integer, primary_key=True)


engine = create_engine("mysql+mysqldb://ro...@127.0.0.1:3306/testing?charset=utf8mb4&binary_prefix=true", echo=True)
engine.execute("""
DROP DATABASE testing;
CREATE DATABASE testing;
USE testing;

CREATE TABLE d (
 id int unsigned auto_increment,
 primary key (id)
) engine=innodb;

CREATE TABLE b (
 id int unsigned auto_increment,
 d_id int unsigned default null,
 primary key (id),
 foreign key (d_id) references `d` (id)
) engine=innodb;

CREATE TABLE a (
 id int unsigned auto_increment,
 b_id int unsigned default null,
 primary key (id),
 foreign key (b_id) references `b` (id)
) engine=innodb;

CREATE TABLE c (
 id int unsigned auto_increment,
 a_id int unsigned default null,
 d_id int unsigned default null,
 primary key (id),
 foreign key (a_id) references `a` (id),
 foreign key (d_id) references `d` (id)
) engine=innodb;

INSERT INTO d (id) values (1), (2), (3);
INSERT INTO b (d_id) values (1), (1), (2);
INSERT INTO a (b_id) values (2), (3), (1);
INSERT INTO c (a_id, d_id) values (1, 1), (1, 2), (2, 3);
""")

Base.metadata.reflect(bind=engine)

A.prepare(engine)
B.prepare(engine)
C.prepare(engine)
D.prepare(engine)

engine = create_engine("mysql+mysqldb://ro...@127.0.0.1:3306/testing?charset=utf8mb4&binary_prefix=true", echo=True)
session = Session(engine)

a = session.query(A)
for _a in a:
 print(a)
 if _a.d:
 print(list(_a.d))

I've verified that the error only happens when I defer reflection. I.e. I remove the DefferedReflection base class from _Base and I comment out the A.prepare(engine)... lines.

Has anyone else run into this?

Thanks!

Mike Bayer

unread,
Oct 30, 2019, 9:49:50 PM10/30/19
to noreply-spamdigest via sqlalchemy
it's clear that the deferredreflection sees the word "join" as the name of a table to reflect from the "secondary" argument.    Also, your test reproduces the error in all versions of SQLAlchemy, I can't find any version that has DeferredReflection which does not behave identically; 1.2, 1.1, 1.0, 0.9, they all do the same thing.   So if you saw something break between 1.2 and 1.3, it's something else, this happens every time.

The test case can be made to work by using a lambda instead of a string, which means SQLAlchemy can do less guessing as to what the name of the "secondary" table might be (in this case nothing):

from sqlalchemy import and_, join

# ...

class ...

    d = relationship(
        "D",
        secondary=lambda: join(B, D, B.d_id == D.id).join(C, C.d_id == D.id),
        primaryjoin=lambda: and_(A.b_id == B.id, A.id == C.a_id),
        secondaryjoin=lambda: D.id == B.d_id,
        uselist=True,
    )
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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+...@googlegroups.com.

Cory Virok

unread,
Oct 31, 2019, 1:10:36 PM10/31/19
to sqlalchemy
Hello Mike and thank you for the fast reply!

The primary reason I'm using the string form of the join conditions is because I'm unable to easily import the module that the relationship refers to, due to circular dependencies in a large codebase. Do you know of any way to specify the same secondary condition that doesn't require importing the module that the condition refers to?

As for the previous versions, thanks for testing that. The code that I'm upgrading was using the DeclarativeReflectedBase class from the examples. So something else must have caused the string form of the relationship to break. 

Thanks,
- Cory
To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

Cory Virok

unread,
Oct 31, 2019, 2:34:22 PM10/31/19
to sqlalchemy
Actually, I think I can use your lambda example and just scope the import to the function, like so:


def _d_secondary():
 return join(B, D, B.d_id == D.id).join(C, C.d_id == D.id)


class A(Base):
 __tablename__ = 'a'

 id = Column(Integer, primary_key=True)
 b_id = Column(ForeignKey('b.id'))

 d = relationship("D",

 secondary=_d_secondary,
 #secondary="join(B, D, B.d_id == D.id)."
 # "join(C, C.d_id == D.id)",

 primaryjoin="and_(A.b_id == B.id, A.id == C.a_id)",
 secondaryjoin="D.id == B.d_id",
 uselist=True
 )

That seems to be a decent workaround for the problem. 

If you did want to provide the "join(...)" string secondary relationship functionality to the deferred mappings, I did some investigation and found that the reason it doesn't work with the deferred configuration is because the `self.fallback[key]` method for determining how to resolve the secondary is never used. This might be fixable if you add the fallback resolver to rel.secondary._resolvers alongside cls._sa_deferred_table_resolver(...)n sqlalchemy.schema.ext.declarative/api.py#prepare()

@classmethod
def prepare(cls, engine):
    """Reflect all :class:`.Table` objects for all current
    :class:`.DeferredReflection` subclasses"""

    to_map = _DeferredMapperConfig.classes_for_base(cls)
    for thingy in to_map:
        cls._sa_decl_prepare(thingy.local_table, engine)
        thingy.map()
        mapper = thingy.cls.__mapper__
        metadata = mapper.class_.metadata
        for rel in mapper._props.values():
            if (
                isinstance(rel, properties.RelationshipProperty)
                and rel.secondary is not None
            ):
                if isinstance(rel.secondary, Table):
                    cls._reflect_table(rel.secondary, engine)
                elif isinstance(rel.secondary, _class_resolver):
                    rel.secondary._resolvers += (
                        cls._sa_deferred_table_resolver(engine, metadata),
                    )

                    # ************* Add fallback resolver here ***************

But I'm not familiar enough with the code to know if that's the right place for it.

- Cory

Mike Bayer

unread,
Oct 31, 2019, 3:21:47 PM10/31/19
to noreply-spamdigest via sqlalchemy
I didn't really look to see why it happens, can you post an issue with your proposed patch ?     this is not code I deal with very often.



To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Reply all
Reply to author
Forward
0 new messages