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
On Thursday, October 31, 2019 at 10:10:36 AM UTC-7, Cory Virok wrote:
>
> 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
>
> On Wednesday, October 30, 2019 at 6:49:50 PM UTC-7, Mike Bayer wrote:
>>
>> 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,
>> )
>>
>>
>>
>> On Wed, Oct 30, 2019, at 6:35 PM, Cory Virok wrote:
>>
>> 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`
>>
>>
>> Here's a repro based on the example from the docs (
>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html?highlight=secondary%20composite#composite-secondary-joins
>> )
>>
>> 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://[email protected]: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://[email protected]: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!
>>
>>
>> --
>> 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/b6e3e875-3d17-49c0-b5f6-70d7215ea3f0%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/b6e3e875-3d17-49c0-b5f6-70d7215ea3f0%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>>
>>
--
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/04c1b097-afc0-481a-a040-dde4ae2d54b1%40googlegroups.com.
