Re: [sqlalchemy] polymorphic inheritance and unique constraints
Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique
along either types. is there a way to move this column to the parent and use
a constraint in the child? my implementation is postgres 9.4+ with psycopg2
only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things), this
works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is
in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is
in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False) # since both child uses bar_id, why not having it on the
parent?
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child1_specific_name,), # will it
work?
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child2_specific_code,), # will it
work?
)
well, will it work without being a concrete inheritance? :)
best regards,
richard.
--
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.
--
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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Andrew Millspaugh millspaugh.and...@gmail.com wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1 [ E
] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
what does your SQL output say? Is this query(A).join(A.f) or the “f”
attribute on an existing “A”? I’m assuming you got this style from
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins,
note that its experimental and not well supported. In most cases you should
deal with individual relationships between each class.
--
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.
--
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.
Re: [sqlalchemy] Adding a listener on a backref
Cyril Scetbon cscet...@gmail.com wrote:
I've already tried. But when I try to define a listener it says the user
attribute does not exist on Address which is true as it's not defined. I
suppose there is a time when it's defined (hen the relationship is actually
auto-generated) and maybe it's just the location of the event which is not
the right one.
two choices:
1. call configure_mappers() so that the backref resolves itself to the other
side.
2. use two distinct relationship() objects with back_populates instead. See
docs.sqlalchemy.org/en/rel_0_9/orm/backref.html.
Le mardi 24 mars 2015 14:23:00 UTC+1, Michael Bayer a écrit :
the “user” backref here is a relationship() like any other, just specify
Address.user as the target of the event.
Cyril Scetbon csce...@gmail.com wrote:
Hi,
Is there a way to add an event listener on a backref ?
I have something like :
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
I'd like to do add an action when a dev set the attribute Address.user.
--
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.
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.
--
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.
Re: [sqlalchemy] Adding a listener on a backref
the “user” backref here is a relationship() like any other, just specify
Address.user as the target of the event.
Cyril Scetbon cscet...@gmail.com wrote:
Hi,
Is there a way to add an event listener on a backref ?
I have something like :
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
I'd like to do add an action when a dev set the attribute Address.user.
--
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.
--
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.
[sqlalchemy] polymorphic inheritance and unique constraints
hi all!
i'm dealing with a little problem here. i have a parent table and its
two inheritances. there is a value that both children have and must be
unique along either types. is there a way to move this column to the
parent and use a constraint in the child? my implementation is postgres
9.4+ with psycopg2 only.
as a simple example (i'm just creating this example to simplify things),
this works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id),
primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works,
bar_id is in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id),
primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works,
bar_id is in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False) # since both child uses bar_id, why not having it
on the parent?
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id),
primary_key=True)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child1_specific_name,),
# will it work?
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id),
primary_key=True)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child2_specific_code,),
# will it work?
)
well, will it work without being a concrete inheritance? :)
best regards,
richard.
--
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.
Re: [sqlalchemy] Adding a listener on a backref
I've already tried. But when I try to define a listener it says the user
attribute does not exist on Address which is true as it's not defined. I
suppose there is a time when it's defined (hen the relationship is actually
auto-generated) and maybe it's just the location of the event which is not
the right one.
Le mardi 24 mars 2015 14:23:00 UTC+1, Michael Bayer a écrit :
the “user” backref here is a relationship() like any other, just specify
Address.user as the target of the event.
Cyril Scetbon csce...@gmail.com javascript: wrote:
Hi,
Is there a way to add an event listener on a backref ?
I have something like :
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
I'd like to do add an action when a dev set the attribute Address.user.
--
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.
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.
[sqlalchemy] Re: sqlalchemy (0.9.7) double quoting python list items when used in where in statement
Update. MySQL-python = 1.2.5 - works MySQL-python 1.2.3 - not work MySQL-python 1.2.4 - maybe, not tested. 2015 m. kovas 23 d., pirmadienis 16:45:48 UTC+2, Edgaras Lukoševičius rašė: Hello, as I'm not receiving any responses in stackoverflow I wil try here. Can someone help me with this issue? http://stackoverflow.com/questions/29195825/sqlalchemy-double-quoting-list-items -- 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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Yes, I got the style from there.
I have a great great great great grandchild that I need to be able to
access by the great great great great grandparent id. How would you
recommend doing that, then? I don't want to have to write f_instances =
F.query.join(F.e).join(E.d).join(D.c).join(C.b).join(B.a).filter(A.id ==
1).all() just to find the F's that have A.id 1.
On Tuesday, March 24, 2015 at 6:28:31 AM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com javascript: wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ]
10..1 [ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones
which have a relationship with A. I'm sure I'm missing something simple,
but I can't seem to find it... Any help out there?
what does your SQL output say? Is this query(A).join(A.f) or the “f”
attribute on an existing “A”? I’m assuming you got this style from
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins,
note that its experimental and not well supported. In most cases you
should
deal with individual relationships between each class.
--
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.
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.
Re: [sqlalchemy] polymorphic inheritance and unique constraints
are these two separate constraints? I just looked and it seems like they are
distinct.
I just added a fix to 1.0 because someone was hacking around something similar
to this.
The easiest way to get these for the moment is just to create the
UniqueConstraint outside of the class definition.
class Foo(Base):
# …
class Bar(Foo):
# …
UniqueConstraint(Bar.x, Foo.y)
that way all the columns are set up, should just work.
Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote:
well, understanding better the docs for column conflicts, can i use a
declared_attr in a unique constraint? if yes, my problem is solved :)
On 03/24/2015 10:33 AM, Michael Bayer wrote:
Richard Gerd Kuesters | Pollux
rich...@pollux.com.br
wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique
along either types. is there a way to move this column to the parent and
use a constraint in the child? my implementation is postgres 9.4+ with
psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts
.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things),
this works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id
is in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id
is in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id),
nullable=False) # since both child uses bar_id, why not having it on the
parent?
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child1_specific_name,), # will
it work?
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child2_specific_code,), # will
it work?
)
well, will it work without being a concrete inheritance? :)
best regards,
richard.
--
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
Re: [sqlalchemy] polymorphic inheritance and unique constraints
thanks again, Mike!
just a question: to make the constraint in the parent, shouldn't i move
other columns that composes the constraint to the parent too?
cheers,
richard.
On 03/24/2015 10:33 AM, Michael Bayer wrote:
Richard Gerd Kuesters | Pollux rich...@pollux.com.br wrote:
hi all!
i'm dealing with a little problem here. i have a parent table and its two
inheritances. there is a value that both children have and must be unique along
either types. is there a way to move this column to the parent and use a
constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.
if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).
the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts.
You’d need to make this work for both the column and the constraint.
as a simple example (i'm just creating this example to simplify things), this
works:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(bar_id, child1_specific_name,), # works, bar_id is
in MyChild1
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(bar_id, child2_specific_code,), # works, bar_id is
in MyChild2
)
but i would like to do this, if possible:
class MyParent(Base):
foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
foo_name = Column(Unicode(64), nullable=False)
foo_type = Column(Integer, nullable=False)
bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
# since both child uses bar_id, why not having it on the parent?
__mapper_args__ = {
polymorphic_on: foo_type,
polymorphic_identity: 0
}
class MyChild1(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child1_specific_name = Column(Unicode(5), nullable=False)
child1_baz_stuff = Column(Boolean, default=False)
__mapper_args__ = {
polymorphic_identity: 1
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child1_specific_name,), # will it
work?
)
class MyChild2(MyParent):
foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
child2_specific_code = Column(UUID, nullable=False)
child2_baz_stuff = Column(Float, nullable=False)
__mapper_args__ = {
polymorphic_identity: 2
}
__table_args__ = (
UniqueConstraint(MyParent.bar_id, child2_specific_code,), # will it
work?
)
well, will it work without being a concrete inheritance? :)
best regards,
richard.
--
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.
--
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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Because I also want to be able to go the other way. I want to be able to
get the a attribute from any given F.
On Tuesday, March 24, 2015 at 4:47:42 PM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com javascript: wrote:
Yes, I got the style from there.
I have a great great great great grandchild that I need to be able to
access by the great great great great grandparent id. How would you
recommend doing that, then? I don't want to have to write f_instances =
F.query.join(F.e).join(E.d).join(D.c).join(C.b).join(B.a).filter(A.id ==
1).all() just to find the F's that have A.id 1.
If that’s all you need, why not put an accessor on F?
class F(Base):
# …
@classmethod
def join_to_a(cls):
return (F.e, E.d, D.c, C.b, B.a)
then:
F.query.join(*F.join_to_a())
On Tuesday, March 24, 2015 at 6:28:31 AM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ]
10..1 [ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones
which have a relationship with A. I'm sure I'm missing something simple,
but I can't seem to find it... Any help out there?
what does your SQL output say? Is this query(A).join(A.f) or the “f”
attribute on an existing “A”? I’m assuming you got this style from
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins,
note that its experimental and not well supported. In most cases you
should
deal with individual relationships between each class.
--
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.
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+...@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.
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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Andrew Millspaugh millspaugh.and...@gmail.com wrote:
Yes, I got the style from there.
I have a great great great great grandchild that I need to be able to access
by the great great great great grandparent id. How would you recommend doing
that, then? I don't want to have to write f_instances =
F.query.join(F.e).join(E.d).join(D.c).join(C.b).join(B.a).filter(A.id ==
1).all() just to find the F's that have A.id 1.
If that’s all you need, why not put an accessor on F?
class F(Base):
# …
@classmethod
def join_to_a(cls):
return (F.e, E.d, D.c, C.b, B.a)
then:
F.query.join(*F.join_to_a())
On Tuesday, March 24, 2015 at 6:28:31 AM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1 [
E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
what does your SQL output say? Is this query(A).join(A.f) or the “f”
attribute on an existing “A”? I’m assuming you got this style from
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins,
note that its experimental and not well supported. In most cases you should
deal with individual relationships between each class.
--
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.
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.
--
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.
[sqlalchemy] translating a complex raw sql statement to SQLalchemy ORM query -- or, binding list/array params to from_statement()
greetings, i have a project of transferring everything from an old API to a new one. the new API uses sqlalchemy ORM exclusively and my old used raw sql with the py-postgresql driver. i need help converting some of the more complex statements into ORM, or at least into a textual statement with some bound parameters. this statement is an upsert type that will update if exists, or insert if not. additionally, i'm dealing with an array input (python list) and i can't figure out how to bind subgroups using .params(). it is easily done in ORM with .in_() but i lack the experience in how to convert this set of queries into ORM. given the following data: vid: 'CVE-2002-2443' dept: 'SecEng' subgroups: ['Archive', 'Desktop', 'DB', 'API'] status:'n/a' and the following existing SQL statement (postgresql): WITH new_values (vid,dept,subgroups,status) AS ( VALUES (:vid, :dept, :subgroups, :status)), persub AS ( SELECT :vid as vid,dept,subgroup FROMsme_subgroups sg WHERE sg.dept = :dept AND sg.subgroup = ANY (:subgroups)), upsert AS ( UPDATE sme_vuln_status sv SET status = nv.status, published = now() at time zone 'UTC' FROM new_values nv WHERE sv.vid = nv.vid AND sv.dept = nv.dept AND sv.subgroup = ANY (nv.subgroups) RETURNING sv.*)INSERT INTO sme_vuln_status (vid,dept,subgroup,status,published) SELECT vid,dept,subgroup,:status,now() at time zone 'UTC' FROM persub WHERE NOT EXISTS (SELECT * FROM upsert up WHERE up.vid = persub.vid AND up.dept = persub.dept AND up.subgroup = persub.subgroup ) how can i properly do either of: - bind a list as a variable for a raw .from_statement().params(..., subgroups=?) or similar set of methods - build an ORM query matching this raw statement? thank you :) -d -- 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.
Re: [sqlalchemy] Guaranteeing same connection for scoped session
Thanks very much Mike. On Monday, March 23, 2015 at 12:40:46 PM UTC-4, Michael Bayer wrote: Kent jkent...@gmail.com javascript: wrote: In cases where we interact with the database session (a particular Connection) to, for example, obtain an application lock which is checked out from database for the lifetime of the database session (not just the duration of a transaction), it is important that I guarantee future scoped session instances get the same connection (and, for example, the pool_recycle or something else has thrown out that connection and grabbed a new one). Please advise me where I can best implement this guarantee. A Session subclass's connection() method seems it might be the appropriate place, but let me know if there is a better recipe. you’d want to create that Session associated with the Connection directly: my_session = scoped_session(bind=some_connection) then of course make sure you .close() it and .close() the connection at the end of the use of that session. The Session.connection() method's docs say: If this Session is configured with autocommit=False, either the Connection corresponding to the current transaction is returned, or if no transaction is in progress, a new one is begun and the Connection returned (note that no transactional state is established with the DBAPI until the first SQL statement is emitted). If the session is one registered in my scoped registry, I'd like to always return the same connection to guarantee I am using the one with the database-side checked-out application lock. What's my best option? Thanks much! -- 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. 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.
[sqlalchemy] Re: Can't figure out multiple level secondary join
My examples I just gave were actually wrong. Let me rewrite them:
*Case 1 (using relationships with composite secondary joins):*
Sub case 1: Given an instance of F, called 'f', perform some action based
on the 'a' property:
if current_user.authorized(f.a):
# do something
Sub case 2: Given an instance of A, called 'a', perform some action on each
one of its F great great great grandchildren:
for f_instance in a.fs:
# do something
*Case 2 (using joined queries):*
Sub case 1: Given an instance of F, called 'f', perform some action based
on the 'a' property:
if current_user.authorized(f.e[0].d.c.b.a):
# do something
Sub case 2: Given an instance of A, called 'a', perform some action on each
one of its F great great great grandchildren:
# the filter I've written here obviously doesn't work. It would have to be
some compound using
# any or in_ or something. I should be able to use any one of the e
instances to get up to my a instance
for f_instance in F.query.join(*F.join_to_a).filter(f.e[0].d.c.b.a).all():
# do something
On Tuesday, March 24, 2015 at 1:12:24 AM UTC-7, Andrew Millspaugh wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1
[ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
[sqlalchemy] Dynamically constructing joins
I have a situation where I can have an arbitrary number of subqueries that
need to be joined on the last step, except if the number of queries, n, is
1.
For example, for n = 1, suppose I have a complex query set to the variable
A[1]
The final submitted query would then look like:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid)))
Easy enough!
Now, suppose, I have two complex queries, A[1] and A[2] that are then
joined as such:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid))). \
join(A[2],A[2].c.patient_sid==a[1].c.patient_sid)
Not too bad...
Now, I have an arbitrary number of complex queries, A[1]...A[n] that need
to be joined:
query = db.session.query(label('sid',
distinct(A[1].c.patient_sid))). \
join(A[2],A[2].c.patient_sid==a[1].c.patient_sid). \
join(A[n],A[n].c.patient_sid==a[1].c.patient_sid)
The above works fine, when I have conditionals based on the number n of
queries, e.g.,
if (n == 1):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
if (n == 2):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid))). \
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)
if (n == 3):
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid))). \
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid). \
join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)
etc., but since I can have an arbitrary number of these queries that need
to be joined, not only is use of conditionals to set up the correct form of
my join inefficient, it is highly redundant and would be a huge mess.
Thus, I am wondering if it is possible to construct my join somehow, like:
if (n == 1):
query = 'db.session.query(label('sid',
distinct(a[1].c.patient_sid)))'
elif (n 1):
query = 'db.session.query(label('sid',
distinct(a[1].c.patient_sid)))'
for i in range (0,n)
query += '.join(A[i],A[i].c.patient_sid==a[1].c.patient_sid)'
eval(query)
I've tried all sorts of crazy things, but cannot get the general case to
work.
Thanks in advance!
Greg--
--
Greg M. Silverman
Senior Developer Analyst
Cardiovascular Informatics http://www.med.umn.edu/cardiology/
University of Minnesota
--
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.
[sqlalchemy] Re: Can't figure out multiple level secondary join
It's more of a convenience thing. I want a nice way to be able to do
something like:
A.fs
or
F.a
For example, if a user is only allowed to modify an F object if they have a
key for the corresponding a object, I'd like to write something like:
if current_user.authorized(F.a):
# do something
On the other side, I'd like to be able to get the list of all fs on an A
without having to do a query. I want to be able to write something like:
for f_instance in A.fs:
# do something
If you think the best way to do this is to simply switch to using queries,
I'm fine with that. I just felt that it was awkward. It will change my
procedures to something like:
if current_user.authorized(F.query.join(*F.join_to_a()).one()):
# do something
for f_instance in F.query.join(F.a).all():
# do something
I'm pretty inexperienced with SQLAlchemy. I mostly want to know the best
way to deal with a relationship like this. I am trying to avoid adding a
fake relationship directly between F and A, as it could get out of sync
with the actual nested relationship. In general, if I have a sqlalchemy
class for which I know the instances will always be uniquely identified by
a (great)^n grandparent, how should I be accessing the grandparents from
the grandchild and vice versa?
On Tuesday, March 24, 2015 at 1:12:24 AM UTC-7, Andrew Millspaugh wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1
[ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Andrew Millspaugh millspaugh.and...@gmail.com wrote:
I'm pretty inexperienced with SQLAlchemy. I mostly want to know the best way
to deal with a relationship like this. I am trying to avoid adding a fake
relationship directly between F and A, as it could get out of sync with the
actual nested relationship. In general, if I have a sqlalchemy class for
which I know the instances will always be uniquely identified by a (great)^n
grandparent, how should I be accessing the grandparents from the grandchild
and vice versa?
The usual way is SQLA just handles simple relationships and you use Python
to hide the gaps between the two; that is, methods and properties. A method
to iterate through all of the d.c.b.a for each e, for example, you’d put a
@property on E that is “d.c.b.a”. You probably want to consider what SQL you
want to see or not, since a relationship that joins straight from F to A
would get there without loading any rows for E, D, C or B in between, so may
be more what you want from a performance perspective.
There’s an extension called association proxy that is also used to build up
systems like these on the Python side in a potentially nicer way than using
straight methods and properties. Jumping across 5 gaps on a regular
basis is still a complex case no matter what.
On Tuesday, March 24, 2015 at 1:12:24 AM UTC-7, Andrew Millspaugh wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1 [ E
] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
--
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.
Re: [sqlalchemy] Can't figure out multiple level secondary join
Andrew Millspaugh millspaugh.and...@gmail.com wrote:
Because I also want to be able to go the other way. I want to be able to get
the a attribute from any given F.
well, then you’d put one on F also.
if you want to send working code and SQL we can see what’s wrong with the
relationship b.c. the one in the docs works (I think).
On Tuesday, March 24, 2015 at 4:47:42 PM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com wrote:
Yes, I got the style from there.
I have a great great great great grandchild that I need to be able to
access by the great great great great grandparent id. How would you
recommend doing that, then? I don't want to have to write f_instances =
F.query.join(F.e).join(E.d).join(D.c).join(C.b).join(B.a).filter(A.id ==
1).all() just to find the F's that have A.id 1.
If that’s all you need, why not put an accessor on F?
class F(Base):
# …
@classmethod
def join_to_a(cls):
return (F.e, E.d, D.c, C.b, B.a)
then:
F.query.join(*F.join_to_a())
On Tuesday, March 24, 2015 at 6:28:31 AM UTC-7, Michael Bayer wrote:
Andrew Millspaugh millspau...@gmail.com wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1
[ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
what does your SQL output say? Is this query(A).join(A.f) or the “f”
attribute on an existing “A”? I’m assuming you got this style from
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#composite-secondary-joins,
note that its experimental and not well supported. In most cases you should
deal with individual relationships between each class.
--
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.
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+...@googlegroups.com.
To post to this group, send email to sqlal...@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.
--
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.
[sqlalchemy] Re: Dynamically constructing joins
any reason why you're not building a query like this?
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
if n = 2
query = query.\
join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)
if n = 3
query = query.\
join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)
or
query = db.session.query(label('sid',
distinct(a[1].c.patient_sid)))
for i in range(2, n):
query = query.\
join(a[i],a[i].c.patient_sid==a[1].c.patient_sid)
--
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.
[sqlalchemy] Re: Can't figure out multiple level secondary join
Awesome. This was the answer I was really looking for. Thank you.
I'll probably hide the relationship with python for now (I'll look into
association proxies as well) and wait until it's slow enough to bother
anyone before trying to optimize the sql. Trying to use these secondary
composite joins is causing me to lose more hair than it's probably worth.
On Tuesday, March 24, 2015 at 1:12:24 AM UTC-7, Andrew Millspaugh wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1
[ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
Re: [sqlalchemy] Is it possible to add another criterion to this backref?
The is_deleted column is in the User table. If possible I'd rather avoid having to replicate it in the favorite tables (hard-deleting favorites is fine, I only need soft deletion for users). -- 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.
[sqlalchemy] Is it possible to add another criterion to this backref?
Hi, I have the following models for a favorite system: https://gist.github.com/ThiefMaster/e4f622d54c74ee322282 Now I'd like to restrict the relationship that's created by the backref in L24, so it doesn't include any favorited users which have the is_deleted column set to true. I tried playing around with primaryjoin in the backref but couldn't get it working. Is what I'm trying to do actually possible? Cheers Adrian -- 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.
Re: [sqlalchemy] Is it possible to add another criterion to this backref?
ThiefMaster adr...@planetcoding.net wrote: The is_deleted column is in the User table. If possible I'd rather avoid having to replicate it in the favorite tables (hard-deleting favorites is fine, I only need soft deletion for users). the column can be on either side. I think maybe you want to illustrate the actual attempts that aren’t working if there’s some kind of help being sought here. -- 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. -- 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.
Re: [sqlalchemy] Is it possible to add another criterion to this backref?
@declared_attr def user(cls): The user owning this favorite return db.relationship( 'User', lazy=False, foreign_keys=lambda: [cls.user_id], backref=db.backref( '_favorite_users', lazy=True, cascade='all, delete-orphan', primaryjoin=lambda: '(User.id == user_id) ~target.is_deleted' ) ) I've added it on the backref since that's the relationship where I want the filter to apply. In the end I'd like to be able to do this: User.query.get(123)._favorite_users which would get me a list of all the favorite users (I'll be using association_proxy, but for now I need to get the relationships themselves working) besides those users who have is_deleted=True (on the User, not the FavoriteUser). But no matter what I put there (tried both lambdas and strings), I always get this error (so I can't even try to figure out the correct criteria to use, since it fails early, during mapper configuration time): sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument 'primaryjoin'; got: '(User.id == user_id) ~target.is_deleted', type type 'unicode' Actually looking at this code again... it's almost a standard many-to-many relationship, so I should probably be using secondary and secondaryjoin somewhere. Can I define this backref-like, i.e. from within the FavoriteUser model? That way I don't have to spread things around so much (which would be the case if I defined the relationship in the User model). -- Adrian -- 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.
[sqlalchemy] Re: Can't figure out multiple level secondary join
Ignore the annotations below the model...those correspond to the actual
names but I forgot to remove them.
On Tuesday, March 24, 2015 at 1:12:24 AM UTC-7, Andrew Millspaugh wrote:
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1
[ E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
Re: [sqlalchemy] Is it possible to add another criterion to this backref?
Adrian adr...@planetcoding.net wrote: @declared_attr def user(cls): The user owning this favorite return db.relationship( 'User', lazy=False, foreign_keys=lambda: [cls.user_id], backref=db.backref( '_favorite_users', lazy=True, cascade='all, delete-orphan', primaryjoin=lambda: '(User.id == user_id) ~target.is_deleted' ) ) the primaryjoin should be either the expression as a Python object (not a string), or a lambda that returns the expression object (not a string), or if you’re using declarative it can be a string that’s eval’ed. But not a lambda *and* a string at the same time. I've added it on the backref since that's the relationship where I want the filter to apply. In the end I'd like to be able to do this: User.query.get(123)._favorite_users which would get me a list of all the favorite users (I'll be using association_proxy, but for now I need to get the relationships themselves working) besides those users who have is_deleted=True (on the User, not the FavoriteUser). But no matter what I put there (tried both lambdas and strings), I always get this error (so I can't even try to figure out the correct criteria to use, since it fails early, during mapper configuration time): sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument 'primaryjoin'; got: '(User.id == user_id) ~target.is_deleted', type type 'unicode’ yeah that’s the lambda + string together which is not the correct use. Actually looking at this code again... it's almost a standard many-to-many relationship, so I should probably be using secondary and secondaryjoin somewhere. Can I define this backref-like, i.e. from within the FavoriteUser model? That way I don't have to spread things around so much (which would be the case if I defined the relationship in the User model). the model here doesn’t illustrate how this would be a many-to-many. -- 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.
Re: [sqlalchemy] Is it possible to add another criterion to this backref?
ThiefMaster adr...@planetcoding.net wrote: Hi, I have the following models for a favorite system: https://gist.github.com/ThiefMaster/e4f622d54c74ee322282 Now I'd like to restrict the relationship that's created by the backref in L24, so it doesn't include any favorited users which have the is_deleted column set to true. I tried playing around with primaryjoin in the backref but couldn't get it working. Is what I'm trying to do actually possible? yes, if this had an is_deleted column (which it does not) you’d add that to the primaryjoin (which I don’t see here), it would only select for that column. Cheers Adrian -- 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. -- 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.
[sqlalchemy] Adding a listener on a backref
Hi,
Is there a way to add an event listener on a backref ?
I have something like :
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
I'd like to do add an action when a dev set the attribute Address.user.
--
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.
[sqlalchemy] Can't figure out multiple level secondary join
I've got a class hierarchy that looks something like this:
[ A ] 1* [ B ] 1-* [ C ] 1--* [ D ] 10..1 [
E ] 1..*--0..1 [ F ]
orgprojticketsnap
bidlimit ticketset
And I'm trying to add a relationship from A to F with a backref. The
relationship definition (on the A model) looks like:
f = DB.relationship('F',
secondary=(
'join(F, E, F.id == E.f_id)'
'.join(D, E.d_id == D.id)'
'.join(C, D.c_id == C.id)'
'.join(B, C.b_id == B.id)'
),
primaryjoin='A.id == B.a_id',
secondaryjoin='E.f_id == F.id',
backref=DB.backref('a', uselist=False), viewonly=True
)
Now, if I query A.f, I get all of the F's, instead of just the ones which
have a relationship with A. I'm sure I'm missing something simple, but I
can't seem to find it... Any help out there?
--
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.
