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?

score 7 · Accepted Answer · answered Dec 04 '14 at 21:41

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)

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

1 Answers1

Linked

Related