funseiki funseiki - 1 month ago 7
Python Question

SQLAlchemy Classical Relationship with No Foreign Key

I'm trying to relate two tables that have multiple 'secondary' tables. Instead of the declarative syntax, it's necessary that I use the classical. Here is a simplified schema:

class Apple:
def __init__(self, id=None, name=None):
# ...

class Recipe:
def __init__(self, id=None, appleId=None, name=None):
# ...

class Blog:
def __init__(self, id=None, name=None, recipeId=None, bloggerId=None):
# ...

class Blogger:
def __init__(self, name)
# ...

appleTable = Table('Apple', metadata, Column('id', Integer, primary_key=True), Column('name', String(256)))
recipeTable = Table('Recipe', metadata, Column('id', Integer, primary_key=True), Column('name', String(256)), Column('appleId', Integer, ForeignKey('Apple.id')))
blogTable = Table('Blog', metadata, Column('id', Integer, primary_key=True), Column('name', String(256)), Column('recipeId', Integer, ForeignKey('Recipe.id')), Column('bloggerId', Integer, ForeignKey('Blogger.id')) )
bloggerTable = Table('Blogger', metadata, Column('id', Integer, primary_key=True), Column('name', String(256)))


# call mapper on all tables/classes
# ... #

# Relate 'Apple' to 'Blogger' using 'Recipe' and 'Blog' as intermediates
Apple.appleBloggers = relationship(Blogger, secondary=..., primaryjoin=..., secondaryjoin=...)


What relationship would I need to place into the
appleBloggers
attribute of
Apple
in order to retrieve all bloggers who've blogged about apple recipes?

Here is what I've tried:

Apple.appleBloggers = relationship(Blogger,
secondary=join(Recipe, Blog, Recipe.id==Blog.recipeId),
primaryjoin= Apple.id == Recipe.appleId,
secondaryjoin= Blog.bloggerId == Blogger.id)


But whenever I do the following:

apple = Apple(name="RedDelicious")
session.add(apple)
session.commit()
print(apple.appleBloggers)


I get the error:

File ".../.pyenv/versions/2.7.10/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1425, in __str__
return str(self.parent.class_.__name__) + "." + self.key
File ".../.pyenv/versions/2.7.10/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 840, in __getattr__
return self._fallback_getattr(key)
File ".../.pyenv/versions/2.7.10/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 818, in _fallback_getattr
raise AttributeError(key)
AttributeError: parent

Answer

You're mixing declarative and classical mappings. Assigning a relationship like that only works for declarative. The proper way to do this in classical mapping is:

mapper(Apple, appleTable, properties={
    "appleBloggers": relationship(Blogger,
                                  secondary=recipeTable.join(blogTable, recipeTable.c.id == blogTable.c.recipeId),
                                  primaryjoin=appleTable.c.id == recipeTable.c.appleId,
                                  secondaryjoin=blogTable.c.bloggerId == bloggerTable.c.id)
})