Dynamically creating relationships from polymorphic subclasses to one class

[Brendan Blanchard]

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()

[Mike Bayer]

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}]",

        ),

    )

--

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a2dc5325-aa51-45f7-8d8d-388241bb84ceo%40googlegroups.com.

[Brendan Blanchard]

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

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Brendan Blanchard]

I'm now having a problem with the actual implementation of this beyond the test case I provided. My database already exists (was created previously with Member-like SQLA objects), so the tables are defined already, but the models that created them have extend_existing=True in their __table_args__. However, when I create my Collection classes dynamically (which add the FK column and relationship to the Member-like objects), the attributes are assigned dynamically as expected (the relationships and FKs exist on the Member-like classes), but I am getting sqlite3.OperationalError: no such column: met.blvmetstats1hour_id (see full trace below).

It's my cursory understanding that my example (with Mike's fix) works because my classes are not instantiated, nor are the tables created prior to creating the metadata for the first time. If this is correct, what's required in order for the new instrumentedattributes that were dynamically added to the class to get added to the existing Member-like tables?

Thanks,

Brendan

2020-08-06 15:37:42,007 -INFO- Running PeriodProcessor for BLVMetStats1Hour on BLVMET

Traceback (most recent call last):

  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such column: met.blvmetstats1hour_id

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/runtime/BLV/periods.py", line 36, in <module>
    proc(datetime(2020, 6, 1), datetime(2020, 8, 1), session=session, logger=logger)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/processing/periods/processors.py", line 25, in period_processor
    members = (session.query(member_cls)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3319, in all
    return list(self)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3481, in __iter__
    return self._execute_and_instances(context)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3506, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1133, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1323, in _execute_context
    self._handle_dbapi_exception(
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1517, in _handle_dbapi_exception
    util.raise_(
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/home/brendan/PycharmProjects/BoulderAIRAnalysis/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: met.blvmetstats1hour_id
[SQL: SELECT met.date AS met_date, met.wind_speed AS met_wind_speed, met.wind_direction AS met_wind_direction, met."temp" AS met_temp, met.battery_voltage AS met_battery_voltage, met.air_temp AS met_air_temp, met.pmt_temp AS met_pmt_temp, met.solar_watt_average AS met_solar_watt_average, met.solar_kj_total AS met_solar_kj_total, met.relative_humidity AS met_relative_humidity, met.id AS met_id, met.site AS met_site, met.type AS met_type, met.blvmetstats1hour_id AS met_blvmetstats1hour_id, met.blvmetstats1month_id AS met_blvmetstats1month_id
FROM met
WHERE met.date >= ? AND met.date < ? AND met.type IN (?)]
[parameters: ('2020-06-01 00:00:00.000000', '2020-06-01 01:00:00.000000', 'BLVMET')]
(Background on this error at: http://sqlalche.me/e/e3q8)

[Mike Bayer]

On Thu, Aug 6, 2020, at 3:45 PM, Brendan Blanchard wrote:

I'm now having a problem with the actual implementation of this beyond the test case I provided. My database already exists (was created previously with Member-like SQLA objects), so the tables are defined already, but the models that created them have extend_existing=True in their __table_args__.

why extend_existing?  are you also using table reflection?  that might be a little ambitious here.   there's no other reason to use that flag and your test case as written it doesn't seem to be needed.

However, when I create my Collection classes dynamically (which add the FK column and relationship to the Member-like objects), the attributes are assigned dynamically as expected (the relationships and FKs exist on the Member-like classes), but I am getting sqlite3.OperationalError: no such column: met.blvmetstats1hour_id (see full trace below).

that is correct, all columns need to be represented in the database naturally, I had assumed you were creating your schema based on the completed model after all attributes were added.

It's my cursory understanding that my example (with Mike's fix) works because my classes are not instantiated, nor are the tables created prior to creating the metadata for the first time. If this is correct, what's required in order for the new instrumentedattributes that were dynamically added to the class to get added to the existing Member-like tables?

SQLite supports the addition of columns using ALTER TABLE: https://www.sqlite.org/lang_altertable.html

you can emit this command directly or perhaps use a tool like Alembic, however it's not clear what the flow of operations is here.     note that it is very unconventional to emit ALTER TABLE while an application is actually running and as a product of normal user interaction, not really very different from repairing a car while it's moving.

Because this is SQLite and I assume this is a simple console or GUI application with a single user / thread, maybe you can get away with it here, but note that these columns cannot be removed from the table once added, unless you copy the data into a new table and drop the old one.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1c18624d-4403-42ea-9027-d69469a8dfbdo%40googlegroups.com.

[Brendan Blanchard]

I think I've misunderstood the usage of extend_existing=True on a table, but from my reading today it sounds like it will only have an effect on building up a table definition prior to calling create_all() on the metadata?

Forgetting that, currently, the application wouldn't necessarily create the Collection classes ahead of creating any Member-like objects, thus the original model and table creation won't have the extra FK columns assigned during the creation of the Collection classes. This was developed with the idea that raw data would be loaded into the database (as Member-like objects), and Collections of them would later be added (such as 1-Hour Collections that contain all 5-second data during that hour as members) that can then run statistics on their member data. It sounds like my simplest solution will be to make sure the Collection classes are instantiated ahead of any objects being created (or, if more are added later, modify the DB accordingly)?

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/vTiuvBl1o00/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c53ff2ce-1186-48f1-bffd-6ac71b02146a%40www.fastmail.com.

[Mike Bayer]

On Thu, Aug 6, 2020, at 5:11 PM, Brendan Blanchard wrote:

I think I've misunderstood the usage of extend_existing=True on a table, but from my reading today it sounds like it will only have an effect on building up a table definition prior to calling create_all() on the metadata?

it's really only used when you make use of the autoload_with / autoload flag on Table.

Forgetting that, currently, the application wouldn't necessarily create the Collection classes ahead of creating any Member-like objects, thus the original model and table creation won't have the extra FK columns assigned during the creation of the Collection classes. This was developed with the idea that raw data would be loaded into the database (as Member-like objects), and Collections of them would later be added (such as 1-Hour Collections that contain all 5-second data during that hour as members) that can then run statistics on their member data. It sounds like my simplest solution will be to make sure the Collection classes are instantiated ahead of any objects being created (or, if more are added later, modify the DB accordingly)?

yeah that would be fine, the other way to approach it is to use more of a dynamic table format but that is not as easy to query.   it sounds like you are building a star schema?  https://en.wikipedia.org/wiki/Star_schema

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFA3B43vd6KMsVA8fuUvGuVjfxabEwzTJ%3DMogS_qxdDt278QrA%40mail.gmail.com.

[Brendan Blanchard]

yeah that would be fine, the other way to approach it is to use more of a dynamic table format but that is not as easy to query.   it sounds like you are building a star schema?  https://en.wikipedia.org/wiki/Star_schema

Ah...unintentionally, yes. A simple one in any case. If I did more heavy normalization to not repeat dates across tables and the likes, that's exactly what it would start looking like. Easier queries are definitely preferable as I'll be handing the final product off and it'll need to be easy to maintain without in-depth SQL knowledge (not that I have that, either, really). The concrete use case is I have a lot of declarative classes that represent atmospheric measurements (and meta measurements on the instruments), and despite their differences (some classes have 2-3 attributes that are gas concentrations, while others have 10+ measurements of temperatures, pressures, etc), whatever those attributes are, I need similar statistics (mean, median, stdev, maybe others), so the idea was to create a single class factory that when given a member class and a list of attributes, would create a Period/Collection class to reference the sub-data and run statistics on them.

The main reason I wanted them as related members of the class is that the data are subject to filtering and corrections, and I wanted the relationship available so if I change underlying data, I can be sure to invalidate the statistics on any related Period/Collections, so when those are re-accessed, they'll be recalculated based on the updated member data. This is working now that I made sure the Period/Collection classes are already created prior to create_all being called.

Many thanks,

Brendan

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f01211c2-292b-4951-9561-78dc54d66095%40www.fastmail.com.