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] <javascript:>.
> 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/ed9e6286-5b9f-4346-a7e8-f9b21508720b%40googlegroups.com.
