On Sat, Nov 25, 2017 at 6:52 PM, Stephan Hügel <[email protected]> wrote:
>
>
> On Saturday, 25 November 2017 17:04:08 UTC, Mike Bayer wrote:
>>
>> This is the "safer" way to go and after
>> working it up, it does look a little cleaner, sort of, here's a full
>> demo (since I have to make these things run anyway), note on the query
>> side, nothing changes:
>>
>> from sqlalchemy import *
>> from sqlalchemy.orm import *
>> from sqlalchemy.ext.declarative import declarative_base
>>
>> Base = declarative_base()
>>
>> metadata = Base.metadata
>>
>>
>> class Content(Base):
>> __tablename__ = "content"
>> txt = Column(Unicode(100), primary_key=True)
>> widgets_id = Column(Integer, nullable=False)
>> themes_id = Column(Integer, nullable=False)
>> sections_id = Column(Integer, nullable=False)
>>
>> __table_args__ = (
>> ForeignKeyConstraint(
>> ['widgets_id', 'themes_id'],
>> ['wt_assoc.widgets_id', 'wt_assoc.themes_id']),
>> ForeignKeyConstraint(
>> ['sections_id', 'themes_id'],
>> ['ts_assoc.sections_id', 'ts_assoc.themes_id']),
>> )
>>
>> wt = relationship("Wt_Assoc")
>> ts = relationship("Ts_Assoc")
>>
>> section = relationship(
>> "Sections", backref="content",
>> primaryjoin="Sections.id == foreign(Content.sections_id)",
>> viewonly=True)
>>
>>
>> class Ts_Assoc(Base):
>> __tablename__ = 'ts_assoc'
>> themes_id = Column('themes_id', Integer, ForeignKey('themes.id'),
>> primary_key=True)
>> sections_id = Column('sections_id', Integer,
>> ForeignKey('sections.id'), primary_key=True)
>>
>> theme = relationship("Themes")
>> section = relationship("Sections")
>>
>>
>> class Wt_Assoc(Base):
>> __tablename__ = 'wt_assoc'
>>
>> widgets_id = Column('widgets_id', Integer,
>> ForeignKey('widgets.id'), primary_key=True)
>> themes_id = Column('themes_id', Integer, ForeignKey('themes.id'),
>> primary_key=True)
>>
>> widget = relationship("Widgets")
>> theme = relationship("Themes")
>>
>>
>> class Sections(Base):
>> __tablename__ = "sections"
>> id = Column(Integer, primary_key=True)
>> section = Column(Unicode(100), index=False, unique=False)
>> themes = relationship("Themes", secondary="ts_assoc",
>> back_populates="sections", viewonly=True)
>>
>>
>> class Themes(Base):
>> __tablename__ = "themes"
>> id = Column(Integer, primary_key=True)
>> theme = Column(Unicode(100), index=True, unique=True)
>> widgets = relationship("Widgets", secondary="wt_assoc",
>> back_populates="themes", viewonly=True)
>> sections = relationship("Sections", secondary="ts_assoc",
>> back_populates="themes", viewonly=True)
>>
>>
>> class Widgets(Base):
>> __tablename__ = "widgets"
>> id = Column(Integer, primary_key=True)
>> name = Column(Unicode(200), index=True, unique=True)
>> themes = relationship("Themes", secondary="wt_assoc",
>> back_populates="widgets", viewonly=True)
>>
>> e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
>> Base.metadata.drop_all(e)
>> Base.metadata.create_all(e)
>>
>>
>> sess = Session(e)
>>
>> w = Widgets(name='Widget A')
>> s = Sections()
>> t = Themes()
>>
>> wt = Wt_Assoc(widget=w, theme=t)
>> ts = Ts_Assoc(section=s, theme=t)
>>
>> # this is the part that's "cleaner", e.g. we explicitly see Content mapped
>> # to the associations which are themselves entities
>> c = Content(txt="txt", ts=ts, wt=wt)
>>
>> # unit of work eats this right up, no problem
>> sess.add_all([wt, ts, c])
>>
>> sess.commit()
>>
>>
>> # query time - use those viewonly=True relationships
>> w = sess.query(Widgets) \
>> .join(Themes, Widgets.themes) \
>> .join(Sections, Themes.sections) \
>> .filter(Widgets.name == u'Widget A') \
>> .one()
>>
>> print(w.themes[0].sections[0].content[0].txt)
>>
>
> Mike, this is great, and it looks like it should work perfectly, but there's
> something funny going on with the Sections <--> Themes mapping (I think). If
> we modify your last example to insert a second Themes instance, attach the
> Sections instance to it, then attach a second Content instance to that
> combination:
>
> # one widget, one section
> w = Widgets(name="Widget A")
> s = Sections(section="first section")
>
> # two themes
> t = Themes(theme="first theme")
> t2 = Themes(theme="second theme")
>
> # add both themes to Widget A
> wt = Wt_Assoc(widget=w, theme=t)
> wt2 = Wt_Assoc(widget=w, theme=t2)
>
> # add first section to both themes
> ts = Ts_Assoc(section=s, theme=t)
> t2s = Ts_Assoc(section=s, theme=t2)
>
> # add content to section one for both themes
> c = Content(txt="first theme, first section", ts=ts, wt=wt)
> c3 = Content(txt="second theme, first section", ts=t2s, wt=wt2)
>
>
> sess.add_all([wt, ts, c, t2, wt2, t2s, c3])
> sess.commit()
>
> This should work correctly (and inspecting the Content table in a sqlite DB,
> it's correct), but if we query as before:
>
> q = sess.query(Widgets) \
> .join(Themes, Widgets.themes) \
> .join(Sections, Themes.sections) \
> .filter(Widgets.name == u'Widget A') \
> .one()
>
> # should give us content for first theme, first section
> print(q.themes[0].sections[0].content[0].txt)
> # should give us content for second theme, first section
> print(q.themes[1].sections[0].content[0].txt)
>
> We would expect to see:
> "first theme, first section"
> "second theme, first section"
>
> But I get "first theme, first section" x 2, which is confusing -- as I said,
> the rows are correct in the Content table.
> Other stuff I left out before: macOS 10.13.1, SQLA 1.1.15, Python 3.5
> Also, I get an SAWarning when I commit:
>
> relationship 'Content.ts' will copy column ts_assoc.themes_id to column
> content.themes_id, which conflicts with relationship(s): 'Content.wt'
> (copies wt_assoc.themes_id to content.themes_id). Consider applying
> viewonly=True to read-only relationships, or provide a primaryjoin condition
> marking writable columns with the foreign() annotation.
you can get rid of the warning like this:
class Content(Base):
__tablename__ = "content"
txt = Column(Unicode(100), primary_key=True)
widgets_id = Column(Integer, nullable=False)
widget_themes_id = Column(Integer, nullable=False)
sections_id = Column(Integer, nullable=False)
sections_themes_id = Column(Integer, nullable=False)
__table_args__ = (
ForeignKeyConstraint(
['widgets_id', 'widget_themes_id'],
['wt_assoc.widgets_id', 'wt_assoc.themes_id']),
ForeignKeyConstraint(
['sections_id', 'sections_themes_id'],
['ts_assoc.sections_id', 'ts_assoc.themes_id']),
)
or like this:
class Content(Base):
__tablename__ = "content"
txt = Column(Unicode(100), primary_key=True)
widgets_id = Column(Integer, nullable=False)
themes_id = Column(Integer, nullable=False)
sections_id = Column(Integer, nullable=False)
__table_args__ = (
ForeignKeyConstraint(
['widgets_id', 'themes_id'],
['wt_assoc.widgets_id', 'wt_assoc.themes_id']),
ForeignKeyConstraint(
['sections_id', 'themes_id'],
['ts_assoc.sections_id', 'ts_assoc.themes_id']),
)
wt = relationship("Wt_Assoc", foreign_keys=widgets_id)
ts = relationship("Ts_Assoc")
the query so far is correct. there's only one "Section" here, so when you say:
widgets.themes[0].sections
and
widgets.themes[1].sections
those are the same Section object. so you are accessing the
identical content collection twice and you are looking at the [0]
element each time so it's the same thing.
If you wanted Widgets.themes.sections to refer to some kind of object
that had just one Content on it, you need a much more complicated
mapping; some kind of object called a ContentTheme and a
ContentSection, which relate to both a Theme and a Section as they are
related to a specific ontent object.
Seems like it's much easier if you make a Widget.content relationship.
class Widgets(Base):
__tablename__ = "widgets"
id = Column(Integer, primary_key=True)
name = Column(Unicode(200), index=True, unique=True)
themes = relationship("Themes", secondary="wt_assoc",
back_populates="widgets", viewonly=True)
content = relationship(
"Content", primaryjoin="Widgets.id==foreign(Content.widgets_id)",
viewonly=True)
print([c.txt for c in q.content])
[u'first theme, first section', u'second theme, first section']
when you run the query and the related items, just watch the SQL, and
try to figure out exactly what SQL you think each attribute should
emit upon access. that's how you'd need to set up your
relationships() and the mappings they refer towards.
>
> --
> 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.
