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].
> 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/8fcc46f6-89d8-4dff-9b23-6013384e26fc%40www.fastmail.com.
