SQLAlchemy joins with composite foreign keys (with flask-sqlalchemy)

Question

I'm trying to understand how to do joins with composite foreign keys on SQLAlchemy and my attempts to do this are failing.

I have the following model classes on my toy model (I'm using Flask-SQLAlchemy, but I'm not sure this has anything to do with the problem):

# coding=utf-8
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
class Asset(db.Model):
 __tablename__ = 'asset'
 user = db.Column('usuario', db.Integer, primary_key=True)
 profile = db.Column('perfil', db.Integer, primary_key=True)
 name = db.Column('nome', db.Unicode(255))
 def __str__(self):
 return u"Asset({}, {}, {})".format(self.user, self.profile, self.name).encode('utf-8')
class Zabumba(db.Model):
 __tablename__ = 'zabumba'
 db.ForeignKeyConstraint(
 ['asset.user', 'asset.profile'],
 ['zabumba.user', 'zabumba.profile']
 )
 user = db.Column('usuario', db.Integer, primary_key=True)
 profile = db.Column('perfil', db.Integer, primary_key=True)
 count = db.Column('qtdade', db.Integer)
 def __str__(self):
 return u"Zabumba({}, {}, {})".format(self.user, self.profile, self.count).encode('utf-8')

I then populated the database with some fake data:

db.drop_all()
db.create_all()
db.session.add(Asset(user=1, profile=1, name=u"Pafúncio"))
db.session.add(Asset(user=1, profile=2, name=u"Skavurska"))
db.session.add(Asset(user=2, profile=1, name=u"Ermengarda"))
db.session.add(Zabumba(user=1, profile=1, count=10))
db.session.add(Zabumba(user=1, profile=2, count=11))
db.session.add(Zabumba(user=2, profile=1, count=12))
db.session.commit()

And tried the following query:

> for asset, zabumba in db.session.query(Zabumba).join(Asset).all():
> print "{:25}\t<---->\t{:25}".format(asset, zabumba)

But SQLAlchemy tells me that it can't find adequate foreign keys for this join:

Traceback (most recent call last):
 File "sqlalchemy_join.py", line 65, in <module>
 for asset, zabumba in db.session.query(Zabumba).join(Asset).all():
 File "/home/calsaverini/.virtualenvs/recsys/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1724, in join
 from_joinpoint=from_joinpoint)
 File "<string>", line 2, in _join
 File "/home/calsaverini/.virtualenvs/recsys/local/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 191, in generate
 fn(self, *args[1:], **kw)
 File "/home/calsaverini/.virtualenvs/recsys/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1858, in _join
 outerjoin, create_aliases, prop)
 File "/home/calsaverini/.virtualenvs/recsys/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1928, in _join_left_to_right
 self._join_to_left(l_info, left, right, onclause, outerjoin)
 File "/home/calsaverini/.virtualenvs/recsys/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2056, in _join_to_left
 "Tried joining to %s, but got: %s" % (right, ae))
sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class '__main__.Asset'>, but got: Can't find any foreign key relationships between 'zabumba' and 'asset'.

I tried a number of other things like: declaring the ForeignKeyConstraint on both tables or inverting the query to db.session.query(Asset).join(Zabumba).all().

What am I doing wrong?

Thanks.

---

P.S.: In my real application code the problem is actually a little more complicated because those tables are on different schemas and I'll be using the bind thing:

app.config['SQLALCHEMY_BINDS'] = {
 'assets': 'mysql+mysqldb://fooserver/assets',
 'zabumbas': 'mysql+mysqldb://fooserver/zabumbas',
}

And then I'll declare the different binds on the tables. How should I declare the ForeignKeyConstraint then?

Answer 1

Your code has few typos, correcting which will make the whole code work.

Define properly the ForeignKeyConstraint:

• it is not to just define it, you have to add it to __table_args__
• definition of columns and refcolumns parameters is reversed (see documentation)
• names of the columns must be names in the database, and not name of ORM attributes

as shown in the following code:

class Zabumba(db.Model):
 __tablename__ = 'zabumba'
 __table_args__ = (
 db.ForeignKeyConstraint(
 ['usuario', 'perfil'],
 ['asset.usuario', 'asset.perfil'],
 ),
 )

construct properly the query by having both classes in the query clause:

 for asset, zabumba in db.session.query(Asset, Zabumba).join(Zabumba).all():
 print "{:25}\t<---->\t{:25}".format(asset, zabumba)