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.
