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)
>
On Saturday, August 1, 2020 at 9:03:46 PM UTC-4, Brendan Blanchard wrote:
>
> 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 sqlal...@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1c18624d-4403-42ea-9027-d69469a8dfbdo%40googlegroups.com.
