What options do I have if I want to use different column types depending on the inherited class, and it's not about foreign keys? I have a ENUM column in the model, while it is a string column in the DB.
class HasInternalStatus: @declared_attr.cascading def internal_status(cls): return Column(ENUM(cls.Status)) class SubclassOne(HasInternalStatus, Base): class Status(Enum): PENDING = "PENDING" ACTIVE = "ACTIVE" class SubclassTwo(HasInternalStatus, Base): class Status(Enum): ACTIVE = "ACTIVE" CANCELED = "CANCELED" среда, 1 апреля 2015 г., 20:04:51 UTC+3 пользователь Michael Bayer написал: > > > > On 4/1/15 10:28 AM, Pierre B wrote: > > Here's a simple visual of the schema > > > OK, so that's called a polymorphic foreign key. SQLAlchemy doesn't have > first class support for this concept because it's relationally incorrect, > but there is an example at > http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/generic_associations/generic_fk.html > > which shows one way to produce this effect. The key aspects to this in > reference to your model attempts are that there are no ForeignKey objects; > objects like Column and ForeignKey are schema-level objects, and if you > construct one, that implies it exists in the schema. That's why you can't > make two Column objects with the same name pointing to the same table, and > this is what I'm referring to when I say that the schema has to be > considered when building out these declarations. > > The techniques to make the relationship here involve using the > "primaryjoin" argument to establish how the tables join directly, as well > as the "foreign()" annotation and/or "foreign_keys" argument which you can > see used in the example; that is, how the tables join is constructed using > all ORM constructs and not schema-level constructs. > > > > > <https://i.imgur.com/TaC2V6b.png> > > There are no foreign key constraints in the database schema, id1 and id2 > are just stored there, a type column is used to retrieve records e.g type > equals B for an association between RightB and ReftB and equals A between > RightA and LeftA. That is why I'm trying to set a default value for the > type column so I don't have to deal with that junction table when inserting > records. > > > On Wednesday, April 1, 2015 at 4:11:03 PM UTC+2, Michael Bayer wrote: >> >> >> >> On 4/1/15 4:55 AM, Pierre B wrote: >> >> Unfortunately I'm inheriting the relational model from an old >> application. I have dozens of tables using a single junction table for >> associations. >> I can not completely redesign my relational model because it needs to be >> compatible with the old application. >> >> I was asking no such thing. I only ask that you consider the relational >> model when building *new* elements of the application. If these models >> are in fact mapping to an existing schema, I find it surprising that your >> existing database schema includes *two* foreign key constraints present on >> each of people4l2.id1 and people4l2.id2, constraining each column to both >> left1.id/left2.id and right1.id/right2.id. >> >> >> >> >> At this point, I think my best option is setting up table inheritance at >> the database level (database is Postgresql) and migrating records into >> children tables. Minimal code refactoring would be involved in the old >> application and it would be possible to use the association object pattern. >> >> On Tuesday, March 31, 2015 at 8:05:19 PM UTC+2, Michael Bayer wrote: >>> >>> >>> >>> Pierre B <[email protected]> wrote: >>> >>> > I tried using the association object pattern before but can't get it >>> to work because I use the same id1 and id2 columns for all foreign keys and >>> I'm not able to override them in the sub-classes ("conflicts with existing >>> column" error). >>> > class MyClass(HasSomeAttribute, db.Model): >>> > __tablename__ = 'people4l2' >>> > id = db.Column(db.Integer, primary_key=True) >>> > >>> > class MySubClass1(MyClass): >>> > right1_id = db.Column('id2', db.Integer, ForeignKey('right1.id')) >>> > left1_id = db.Column('id1', db.Integer, ForeignKey('left1.id')) >>> > >>> > class MySubClass2(MyClass): >>> > right2_id = db.Column('id2', db.Integer, ForeignKey('right2.id')) >>> > left2_id = db.Column('id1', db.Integer, ForeignKey('left2.id’)) >>> >>> That’s because you do not have a __tablename__ for these subclasses, so >>> when >>> you put a column on the subclass, that is physically a column on the >>> ‘people4l2’ table; the names cannot be conflicting. Also, it is not >>> possible >>> to have a column named “people4l2.id2” which is in some cases a foreign >>> key >>> to “right1.id” and in other cases to “right2.id”. >>> >>> This probably all seems very complicated if you only think of it in >>> terms of >>> a Python object model. That’s why it is essential that you design your >>> database schema in terms of database tables, and how those tables will >>> work >>> within a purely relational model, without Python being involved, first. >>> >>> For simple cases, the design of the relational model and the object >>> model >>> are so similar that this explicit step isn’t necessary, but once the >>> goals >>> become a little bit divergent between relational and object model, >>> that’s >>> when the relational model has to be developed separately, up front. This >>> is >>> the essence of how SQLAlchemy works, which becomes apparent the moment >>> you >>> get into models like these which are typically impossible on most other >>> ORMs, since most ORMs do not consider design of the relational model as >>> separate from the object model. >>> >>> The tradeoff here is basically between “more work with SQLAlchemy” vs. >>> “not possible at all with other ORMs” :) >>> >>> The relational model is the more rigid part of the system here, so you >>> have to >>> work that part out first; then determine how you want to map the Python >>> object model on top of the relational model. >>> >>> > On Tuesday, March 31, 2015 at 4:29:52 PM UTC+2, Michael Bayer wrote: >>> > >>> > >>> > Pierre B <[email protected]> wrote: >>> > >>> > > Here's my use case: >>> > > right1 = Right() >>> > > right.left = Left() >>> > > >>> > > right2 = Right2() >>> > > right2.left = Left2() >>> > > >>> > > db.session.add(right) // automatically create the junction using >>> MySubClass1 and set the type field to 1 >>> > > db.session.add(right2) // automatically create the junction using >>> MySubClass1 and set the type field to 2 >>> > > db.session.commit() >>> > > >>> > > Basically I have a junction table associating a bunch of different >>> tables in my model. >>> > > I want to abstract that mechanism using relationships and >>> polymorphism so that I don't have to deal with that junction table while >>> coding. >>> > > The relationships I created allow me to not have to deal with it >>> while selecting records but I can't get it to set the type field while >>> inserting records. >>> > >>> > OK, you are using the association object pattern. You cannot use >>> “secondary” >>> > in the way that you are doing here. You need to map a relationship to >>> > MySubClass1 explicitly. To reduce verbosity, you’d then apply the >>> > association proxy pattern. Without association proxy, your association >>> of >>> > right and left will be something like: >>> > >>> > right2 = Right2() >>> > right2.left_association = MySubClass1() >>> > right2.left_association.left = Left2() >>> > >>> > the association proxy then allows for MySubClass1() to be called >>> > automatically and you can refer to “right2.left” directly. >>> > >>> > Start with: >>> > >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#association-object >>> >>> > >>> > make that work completely, with the more verbose use pattern. >>> > >>> > then when that is totally working and understood, then move onto >>> association >>> > proxy: >>> > >>> > >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html >>> > >>> > >>> > >>> > > On Tuesday, March 31, 2015 at 4:11:51 PM UTC+2, Michael Bayer wrote: >>> > > >>> > > Pierre B <[email protected]> wrote: >>> > > >>> > > > I made a type in the Right model, here are the models again: >>> > > >>> > > if you’re referring to the behavior of Right.left when you use it in >>> a >>> > > query, such as query(Right).join(Right.left), then the “default” >>> value of a >>> > > Column object has no interaction there. >>> > > >>> > > it seems like you probably want to do something very simple here but >>> I’m not >>> > > getting enough information on what that is. If you could illustrate >>> the >>> > > usage of the objects that you are looking for, that would help. >>> > > >>> > > > >>> > > >>> > > >>> > > >>> > > > class HasSomeAttribute(object): >>> > > > @declared_attr.cascading >>> > > > def type(cls): >>> > > > if has_inherited_table(cls): >>> > > > if cls.__name__ == 'MySubClass1': >>> > > > return db.Column(db.Integer, default=1) >>> > > > else: >>> > > > return db.Column(db.Integer, default=2) >>> > > > else: >>> > > > return db.Column(db.Integer, default=0) >>> > > > >>> > > > class MyClass(HasSomeAttribute, db.Model): >>> > > > __tablename__ = 'people4l2' >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > id1 = db.Column(db.Integer) >>> > > > id2 = db.Column(db.Integer) >>> > > > >>> > > > class MySubClass1(MyClass): >>> > > > pass >>> > > > >>> > > > class MySubClass2(MyClass): >>> > > > pass >>> > > > >>> > > > class Right(db.Model): >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > left = relationship( >>> > > > 'Left', >>> > > > secondary= MySubClass1.__table__, >>> > > > primaryjoin='and_(MySubClass1.type == 802, MySubClass1.id2 >>> == Right.id)', >>> > > > secondaryjoin='and_(MySubClass1.type == 802, >>> MySubClass1.id1 == Left.id)' >>> > > > ) >>> > > > >>> > > > class Left(db.Model): >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > >>> > > > >>> > > > On Tuesday, March 31, 2015 at 12:12:35 PM UTC+2, Pierre B wrote: >>> > > > Hi Michael, >>> > > > >>> > > > Thank you for your response. >>> > > > Unfortunately I have already tried to use the __init__ >>> function/catch the init event but I am only referencing the sub classes in >>> a relationship which does not seem to actually instantiate classes because >>> the __init__ is never called/init event is never fired. >>> > > > Here is a simple version of my models. >>> > > > >>> > > > class HasSomeAttribute(object): >>> > > > @declared_attr.cascading >>> > > > def type(cls): >>> > > > if has_inherited_table(cls): >>> > > > if cls.__name__ == 'MySubClass1': >>> > > > return db.Column(db.Integer, default=1) >>> > > > else: >>> > > > return db.Column(db.Integer, default=2) >>> > > > else: >>> > > > return db.Column(db.Integer, default=0) >>> > > > >>> > > > class MyClass(HasSomeAttribute, db.Model): >>> > > > __tablename__ = 'people4l2' >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > id1 = db.Column(db.Integer) >>> > > > id2 = db.Column(db.Integer) >>> > > > >>> > > > class MySubClass1(MyClass): >>> > > > pass >>> > > > >>> > > > class MySubClass2(MyClass): >>> > > > pass >>> > > > >>> > > > class Right(db.Model): >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > subclass_attr = relationship( >>> > > > 'Contact', >>> > > > secondary= MySubClass1.__table__, >>> > > > primaryjoin='and_(MySubClass1.type == 802, MySubClass1.id2 >>> == Right.id)', >>> > > > secondaryjoin='and_(MySubClass1.type == 802, >>> MySubClass1.id1 == Left.id)' >>> > > > ) >>> > > > >>> > > > class Left(db.Model): >>> > > > id = db.Column(db.Integer, primary_key=True) >>> > > > >>> > > > MyClass is used as a junction table for a bunch of different >>> relationships, the type field is used to differentiate the relationships. >>> > > > >>> > > > >>> > > > On Monday, March 30, 2015 at 5:26:30 PM UTC+2, Michael Bayer >>> wrote: >>> > > > >>> > > > >>> > > > Pierre B <[email protected]> wrote: >>> > > > >>> > > > > Hi all, >>> > > > > >>> > > > > I'm ultimately trying to have different default values for the >>> same column. Following the documentation, the @declared_attr.cacading >>> decorator seems to be the best approach. >>> > > > > Here's my code: >>> > > > > class HasSomeAttribute(object): >>> > > > > @declared_attr.cascading >>> > > > > def type(cls): >>> > > > > if has_inherited_table(cls): >>> > > > > if cls.__name__ == 'MySubClass1': >>> > > > > return db.Column(db.Integer, default=1) >>> > > > > else: >>> > > > > return db.Column(db.Integer, default=2) >>> > > > > else: >>> > > > > return db.Column(db.Integer, default=0) >>> > > > > >>> > > > > class MyClass(HasSomeAttribute, db.Model): >>> > > > > __tablename__ = 'people4l2' >>> > > > > id = db.Column(db.Integer, primary_key=True) >>> > > > > >>> > > > > class MySubClass1(MyClass): >>> > > > > pass >>> > > > > >>> > > > > class MySubClass2(MyClass): >>> > > > > pass >>> > > > > >>> > > > > I iterated quite a few times over this but I'm systematically >>> getting this error: >>> > > > > ArgumentError: Column 'type' on class <class >>> '__main__.MySubClass1'> conflicts with existing column 'people4l2.type’ >>> > > > >>> > > > this mapping illustrates MySubClass1 and MySubClass2 as both >>> sharing the >>> > > > same table “people4l2”, as they have no __tablename__ attribute, >>> so there >>> > > > can only be one “type” column. So in this case it is not >>> appropriate to use >>> > > > cascading in exactly this way, as MyClass already has a “type” >>> column, and >>> > > > that gets attached to the “people4l2” table and that’s it; there >>> can be no >>> > > > different “type” column on MySubClass1/MySubClass2. >>> > > > >>> > > > If you’d like “type” to do something different based on which >>> class is being >>> > > > instantiated, this is an ORM-level differentiation. Use either the >>> > > > constructor __init__() to set it or use the init() event >>> > > > ( >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html?highlight=event%20init#sqlalchemy.orm.events.InstanceEvents.init). >>> >>> >>> > > > >>> > > > OTOH if “type” is actually the “polymoprhic discriminator”, which >>> is what >>> > > > this looks like, then you’d be looking to just set up “type” as >>> the >>> > > > “polymorphic_on” column and set up the “1”, “2”, “0” as the >>> polymorphic >>> > > > identity (see >>> > > > >>> http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#single-table-inheritance >>> >>> > > > for a simple example). >>> > > > >>> > > > >>> > > > -- >>> > > > 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 post to this group, send email to [email protected]. >>> > > > Visit this group at http://groups.google.com/group/sqlalchemy. >>> > > > For more options, visit https://groups.google.com/d/optout. >>> > > >>> > > -- >>> > > 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 post to this group, send email to [email protected]. >>> > > Visit this group at http://groups.google.com/group/sqlalchemy. >>> > > For more options, visit https://groups.google.com/d/optout. >>> > >>> > -- >>> > 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 post to this group, send email to [email protected]. >>> > Visit this group at http://groups.google.com/group/sqlalchemy. >>> > For more options, visit https://groups.google.com/d/optout. >>> >> -- >> 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 post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> >> >> -- > 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] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- 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 post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
