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.
--
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.
