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?
How do I join two tables in Sqlalchemy? Python Flask and SQLAlchemy ORM Now we use the join() and outerjoin() methods. The join() method returns a join object from one table object to another. For example, following use of join() method will automatically result in join based on the foreign key.
One of which is that Flask-SQLAlchemy has its own API. This adds complexity by having its different methods for ORM queries and models separate from the SQLAlchemy API. Another disadvantage is that Flask-SQLAlchemy makes using the database outside of a Flask context difficult.
The sqlalchemy backref is one of the type keywords and it passed as the separate argument parameters which has to be used in the ORM mapping objects. It mainly includes the event listener on the configuration attributes with both directions of the user datas through explicitly handling the database relationships.
Your code has few typos, correcting which will make the whole code work.
Define properly the ForeignKeyConstraint
:
__table_args__
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With