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 <rocambol...@gmail.com <javascript:>> 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 <http://right1.id>'))
> > left1_id = db.Column('id1', db.Integer, ForeignKey('left1.id
> <http://left1.id>'))
> >
> > class MySubClass2(MyClass):
> > right2_id = db.Column('id2', db.Integer,
> ForeignKey('right2.id <http://right2.id>'))
> > left2_id = db.Column('id1', db.Integer, ForeignKey('left2.id
> <http://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 <http://right1.id>” and in other cases to “right2.id
> <http://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 <rocambol...@gmail.com> 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
>
> <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
>
> <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 <rocambol...@gmail.com> 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 <rocambol...@gmail.com> 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
>
> <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
>
> <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 sqlalchemy+...@googlegroups.com.
> > > > To post to this group, send email to sqlal...@googlegroups.com.
> > > > Visit this group at
> http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> > > > For more options, visit https://groups.google.com/d/optout
> <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 sqlalchemy+...@googlegroups.com.
> > > To post to this group, send email to sqlal...@googlegroups.com.
> > > Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> > > For more options, visit https://groups.google.com/d/optout
> <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 sqlalchemy+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <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 sqlalchemy+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
