funseiki funseiki - 8 months ago 46
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)
})