Of course! Thanks for the quick reply, I was afraid/hoping it would be
something simple I was missing -- I wasn't aware that it was required on
both sides of a bi-directional relationship, but that makes sense. I'm
using 1.3.17 and it does the same, I just only copied the first part of the
traceback, assuming it was the root cause. Not to mention I was blind to
what is was saying since I thought it was covered by having it on one side.
Two lessons learned today!
Thanks again,
Brendan
On Saturday, August 1, 2020 at 7:15:44 PM UTC-4, Mike Bayer wrote:
>
> hi and thanks for the straightforward test case.
>
> I'm not sure if that's an old version of SQLAlchemy you're using, when I
> run with current 1.3.18 release the error message is more descriptive:
>
> "Could not determine join condition between parent/child tables on
> relationship ACollection.members - there are multiple foreign key paths
> linking the tables. Specify the 'foreign_keys' argument, providing a list
> of those columns which should be counted as containing a foreign key
> reference to the parent table."
>
> the error message using a modern version should lead you right to the
> issue which is that you forgot to put "foreign_keys" on the "members"
> relationship:
>
> setattr(
> collection_class,
> "members",
> relationship(
> member_cls.__name__,
> back_populates=f"{cls_name}Rel",
> foreign_keys=f"[{member_cls.__name__}.{fk_col}]",
> ),
> )
>
>
>
>
>
> On Sat, Aug 1, 2020, at 1:54 PM, Brendan Blanchard wrote:
>
> I have SQLA classes in a project that have date and data components and
> I'm attempting to create a single base-class that I can subclass to create
> statistics for all points of data during some interval, but I want the
> interval subclasses to maintain references to their sub-data such that if I
> change the underlying data of a monthly period, the statistics on that
> month can be recalculated (all of this behavior is left out, except the
> relationships). In my real use-case, the Member class could be one of
> dozens of SQLA classes, each of which could have multiple collection
> classes related to it, which is why dynamic creation using type( ) is
> necessary.
>
> In a basic example, I have some data class that needs to be related to an
> arbitrary number of other collection classes (created dynamically). One
> created class might aggregate those data in 30-minute intervals, and
> another in monthly intervals. To do this, my thought was to create one base
> class that, when subclassed, is assigned a unique relationship to the
> member class. This is what I believe I'm doing, but I am running into
> sqlalchemy.exc.AmbiguousForeignKeysError. I've looked around extensively,
> and am reaching out here because my understanding is I'm doing what's
> required to have multiple relationships between two tables, but am still
> getting the error. I'm aware it could be an issue with how I'm creating
> subclasses as well, but haven't had any luck using inherit_conditions,
> either. The output and trimmed-down example is below:
>
> Setting ACollection_id on Member with ForeignKey(collections.id)
> Setting "members" attribute on ACollection which back populates
> "ACollectionRel"
> Setting attribute "ACollectionRel" on Member with:
> foreign_keys=[Member.ACollection_id]
>
> Setting BCollection_id on Member with ForeignKey(collections.id)
> Setting "members" attribute on BCollection which back populates
> "BCollectionRel"
> Setting attribute "BCollectionRel" on Member with:
> foreign_keys=[Member.BCollection_id]
>
>
> Traceback (most recent call last):
> File
> "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py",
>
> line 2620, in _determine_joins
> consider_as_foreign_keys=consider_as_foreign_keys,
> File "<string>", line 2, in join_condition
> File "<string>", line 2, in _join_condition
> File
> "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py",
>
> line 139, in warned
> return fn(*args, **kwargs)
> File
> "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>
> line 967, in _join_condition
> a, b, constraints, consider_as_foreign_keys
> File
> "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.7/site-packages/sqlalchemy/sql/selectable.py",
>
> line 1084, in _joincond_trim_constraints
> "join explicitly." % (a.description, b.description)
> sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
> 'collections' and 'basic_members'; tables have more than one foreign key
> constraint relationship between them. Please specify the 'onclause' of this
> join explicitly.
>
>
> from sqlalchemy import Column, String, Integer, DateTime, UniqueConstraint,
> ForeignKey
> from sqlalchemy.orm import relationship
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import create_engine
>
> Base = declarative_base()
>
>
> class Member(Base):
> """A testable basic SQLA class that contains some floating-point
> information."""
> __tablename__ = 'basic_members'
>
> id = Column(Integer, primary_key=True)
>
>
> class CollectionBase(Base):
> id = Column(Integer, primary_key=True)
> date = Column(DateTime)
> type = Column(String(15))
>
> __tablename__ = 'collections'
> __table_args__ = (
> UniqueConstraint('date', 'type'),
> {'extend_existing': True}
> )
> __mapper_args__ = {
> 'polymorphic_on': 'type',
> 'polymorphic_identity': 'base_class',
> }
>
>
> def collection_class_factory(name, member_cls):
>
> all_attrs = {
> '__mapper_args__': {
> 'polymorphic_identity': name
> },
>
> 'member_class': member_cls,
> *# keep a reference to the member class on the 'collection' class* }
>
> cls_name = f'{name}Collection'
> fk_col = f'{cls_name}_id'
>
> collection_class = type(cls_name, (CollectionBase,), all_attrs)
>
> print(f'Setting {fk_col} on {member_cls.__name__} with
> ForeignKey({collection_class.__tablename__}.id)')
>
> *# set the foreign key col on the members class to reference the periods they
> belong to* setattr(member_cls, fk_col, Column(Integer,
> ForeignKey(f'{collection_class.__tablename__}.id')))
>
> print(f'Setting "members" attribute on {collection_class.__name__} which
> back populates "{cls_name}Rel"')
>
> *# set the relationship from periods -> members* setattr(collection_class,
> 'members', relationship(member_cls.__name__, back_populates=f'{cls_name}Rel'))
>
> print(f'Setting attribute "{cls_name}Rel" on {member_cls.__name__} with:
> ')
> print(f'\tforeign_keys=[{member_cls.__name__}.{fk_col}]')
>
> *# set the relationship from members -> periods* setattr(member_cls,
> f'{cls_name}Rel', relationship(
> collection_class.__name__, uselist=False,
> foreign_keys=f'[{member_cls.__name__}.{fk_col}]',
> back_populates='members'
> ))
> print()
>
> return collection_class
>
>
> CollectionA = collection_class_factory('A', Member)
> CollectionB = collection_class_factory('B', Member)
>
> engine = create_engine('sqlite://')
> session = sessionmaker(bind=engine)()
>
> Base.metadata.create_all(bind=engine)
>
> a = CollectionA()
> b = CollectionB()
>
>
> --
> 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/a2dc5325-aa51-45f7-8d8d-388241bb84ceo%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/a2dc5325-aa51-45f7-8d8d-388241bb84ceo%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/ec69633b-92f5-4d97-b144-1409f0102422o%40googlegroups.com.
