On Thu, Oct 31, 2019, at 2:34 PM, Cory Virok wrote:
> 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.
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.
>
> - 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/04c1b097-afc0-481a-a040-dde4ae2d54b1%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/a30ac818-32ca-429d-a4f1-245c74147089%40www.fastmail.com.
