On Thu, Aug 23, 2018 at 3:07 PM, Alex Rothberg <[email protected]> wrote:
> I didn't mean to confuse this question by showing both formats of the
> many-to-many relationship (using secondary and not); I am aware that using
> both can lead to problems / inconsistencies.
>
> I just wanted to show that I had both options available at my disposal. Is
> there anyway to use the secondary relationship (rather than the association
> one) with some combination of cascading options to get what I want where I
> can create and use a Geography knowing only its pk without the ORM trying to
> then save it to the DB?
So to get the session.merge(x, load=False) pattern with a transient
object, there is a more special purpose method that is not going to
cascade or anything like that, but if you have just the identity you
want and are watching carefully what you are doing, it is the
make_transient_to_detached() function, example below.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
Base = declarative_base()
fund_geo = Table(
'fund_geo', Base.metadata,
Column('geo_id', ForeignKey('geography.id'), primary_key=True),
Column('fund_id', ForeignKey('fund.id'), primary_key=True)
)
class Geography(Base):
__tablename__ = 'geography'
id = Column(Integer, primary_key=True)
class Fund(Base):
__tablename__ = 'fund'
id = Column(Integer, primary_key=True)
geographies = relationship(
Geography,
backref="fund",
secondary=fund_geo
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
g1 = Geography()
s.add(g1)
s.commit()
s.close()
g1 = Geography(id=1)
make_transient_to_detached(g1)
s.add(g1)
f1 = Fund(geographies=[g1])
s.add(f1)
s.commit()
>
> On Thursday, August 23, 2018 at 1:02:40 PM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Aug 22, 2018 at 5:41 PM, Alex Rothberg <[email protected]> wrote:
>> > I am using an association model / table to represent a many to many
>> > relationship:
>> >
>> > class Geography(db.Model):
>> >
>> > id =
>> > ...
>> >
>> > class Fund(db.Model):
>> > id =
>> > ...
>> > geography_associations = db.relationship(
>> > lambda: FundGeographyAssociation,
>> > back_populates="fund",
>> > cascade='save-update, merge, delete, delete-orphan'
>> > )
>> >
>> > geographies = db.relationship(
>> > Geography,
>> > backref="fund",
>> > secondary=lambda: FundGeographyAssociation.__table__,
>> > )
>> >
>> > class FundGeographyAssociation(db.Model):
>> > fund_id = db.Column(
>> > UUID, db.ForeignKey(Fund.id), primary_key=True,
>> > )
>> > geography_id = db.Column(
>> > UUID, db.ForeignKey(Geography.id), primary_key=True,
>> > )
>> >
>> > fund = db.relationship(Fund,
>> > back_populates='geography_associations')
>> >
>> >
>> > and then am attempting to update the list of geographies for a Fund
>> > using:
>> > fund.geographies = [????]
>> >
>> >
>> > my issue is what to put in ??? when I only have the pk of the geography
>> > model.
>>
>> it is not a recommended pattern to re-purpose a mapped association
>> class as a "secondary" elsewhere. The ORM does not know that
>> Fund.geography_associations and Fund.geographies refer to the same
>> table and mutations to each of these independently will conflict (see
>>
>> http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object)
>> . The usual pattern is to use an association proxy for
>> Fund.geographies (see
>>
>> http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html#simplifying-association-objects).
>>
>> If you want to add a row having only the id of Geography, the most
>> straightforward approach is to append the association object directly:
>>
>> fund.geography_associations = [FundGeoAssoc(geo_id=1)]
>>
>>
>> >
>> > this works: Geography.query.get(id) however this does not:
>> > Geography(id=id)
>> > as the latter tries to create a new Geography object leading to
>> > conflicts.
>> > The former seems "silly" as it requires an extra query to db to load the
>> > object even though all i need is the geography id to create the
>> > association
>> > object. I tried variation of session.merge with load=False however that
>> > doesn't work as the object is transient.
>> >
>> > --
>> > 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.
>
> --
> 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.
--
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.
