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 > > On Thu, Aug 6, 2020 at 4:59 PM Mike Bayer <mike...@zzzcomputing.com> wrote: >> __ >> >> >> 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. >> >> >>> >>> 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 >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/1c18624d-4403-42ea-9027-d69469a8dfbdo%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 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+unsubscr...@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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/c53ff2ce-1186-48f1-bffd-6ac71b02146a%40www.fastmail.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/CAFA3B43vd6KMsVA8fuUvGuVjfxabEwzTJ%3DMogS_qxdDt278QrA%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAFA3B43vd6KMsVA8fuUvGuVjfxabEwzTJ%3DMogS_qxdDt278QrA%40mail.gmail.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/f01211c2-292b-4951-9561-78dc54d66095%40www.fastmail.com.
