我试图了解如何使用SQLAlchemy上的复合外键进行联接,而我这样做的尝试失败了。
我的玩具模型上有以下模型类(我使用的是Flask-SQLAlchemy,但是我不确定这与问题有关):
# 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')
然后,我用一些假数据填充数据库:
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()
并尝试了以下查询:
> for asset, zabumba in db.session.query(Zabumba).join(Asset).all():
> print "{:25}\t<---->\t{:25}".format(asset, zabumba)
但是SQLAlchemy告诉我,它无法为此联接找到足够的外键:
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'.
我尝试了许多其他操作,例如:在两个表上都声明ForeignKeyConstraint
或将查询转换为db.session.query(Asset).join(Zabumba).all()
。
我究竟做错了什么?
谢谢。
P.S .:在我的实际应用程序代码中,问题实际上稍微复杂一点,因为这些表位于不同的架构上,而我将使用绑定对象:
app.config['SQLALCHEMY_BINDS'] = {
'assets': 'mysql+mysqldb://fooserver/assets',
'zabumbas': 'mysql+mysqldb://fooserver/zabumbas',
}
然后,我将在表上声明不同的绑定。那我该怎么声明ForeignKeyConstraint
呢?
参考方案
您的代码几乎没有错别字,更正这些错误将使整个代码正常工作。
正确定义ForeignKeyConstraint
:
它不仅仅是定义它,您必须将其添加到__table_args__
列和refcolumns参数的定义相反(请参见documentation)
列的名称必须是数据库中的名称,而不是ORM属性的名称
如以下代码所示:
class Zabumba(db.Model):
__tablename__ = 'zabumba'
__table_args__ = (
db.ForeignKeyConstraint(
['usuario', 'perfil'],
['asset.usuario', 'asset.perfil'],
),
)
通过在query子句中同时使用两个类来正确构造查询:
for asset, zabumba in db.session.query(Asset, Zabumba).join(Zabumba).all():
print "{:25}\t<---->\t{:25}".format(asset, zabumba)
sqlalchemy在多列中唯一 - python假设我有一个代表位置的类。位置“属于”客户。位置由Unicode 10个字符代码标识。对于特定客户,“位置代码”在位置之间应该是唯一的。The two below fields in combination should be unique customer_id = Column(Integer,ForeignKey('customers.cus…
用大写字母拆分字符串,但忽略AAA Python Regex - python我的正则表达式:vendor = "MyNameIsJoe. I'mWorkerInAAAinc." ven = re.split(r'(?<=[a-z])[A-Z]|[A-Z](?=[a-z])', vendor) 以大写字母分割字符串,例如:'我的名字是乔。 I'mWorkerInAAAinc”变成…
R'relaimpo'软件包的Python端口 - python我需要计算Lindeman-Merenda-Gold(LMG)分数,以进行回归分析。我发现R语言的relaimpo包下有该文件。不幸的是,我对R没有任何经验。我检查了互联网,但找不到。这个程序包有python端口吗?如果不存在,是否可以通过python使用该包? python参考方案 最近,我遇到了pingouin库。
如何用'-'解析字符串到节点js本地脚本? - python我正在使用本地节点js脚本来处理字符串。我陷入了将'-'字符串解析为本地节点js脚本的问题。render.js:#! /usr/bin/env -S node -r esm let argv = require('yargs') .usage('$0 [string]') .argv; console.log(argv…
将字符串分配给numpy.zeros数组[重复] - pythonThis question already has answers here: Weird behaviour initializing a numpy array of string data (4个答案) …