python-2.7 - python 2.7 + flask-sqlalchemy,flask marshmallow + DB 关系抛出错误外键约束和主连接
问题描述
我现有的数据库数据集已经包含这些表:owners table that has ownerid as primary_key
另一个表 owndnis,其 primary_key 是相同的 ownerid
另一个表,其 primary_key 也与所有者的 ownerid 相同
我想定义一个看起来像这样的关系
拥有 {owndnis} 和 {application_parameters} 的所有者
我的模型和路线文件内容如下
model.py
from marshmallow import fields
from flask import jsonify
class owners(db.Model):
__tablename__ = 'owners'
ownerid = db.Column('ownerid',db.String(60), nullable=False)
name = db.Column('ownerdomainname', db.String(60),primary_key=True, nullable=False)
spownerid = db.Column('spownerid', db.String(60))
ownerid = db.Column(db.String(), db.ForeignKey('owndnis.ownerid'))
dnis = db.relationship("owndnis", uselist=False, backref="owners")
# ownerid = db.Column(db.String(), db.ForeignKey('application_parameters.ownerid'))
# app_params = db.relationship("application_parameters", backref="owners")
class owndnis(db.Model):
__tablename__ = 'owndnis'
ownerid = db.Column('ownerid',db.String(60),primary_key=True)
dnisstart = db.Column('dnisstart', db.String(20), nullable=False)
dnisend = db.Column('dnisend', db.String(20))
class application_parameters(db.Model):
__tablename__ = 'application_parameters'
ownerid = db.Column('ownerid',db.String(60),primary_key=True)
applicationid = db.Column('applicationid', db.String(60), nullable=False)
key = db.Column('key', db.String(128), nullable=False)
value = db.Column('value', db.String(1024), nullable=False)
###### SCHEMAS #####
class owndnis_schema(ma.ModelSchema):
dnisstart = fields.String()
dnisend = fields.String()
class app_params_schema(ma.ModelSchema):
key = fields.String()
value = fields.String()
class owners_schema(ma.ModelSchema):
ownerid = fields.String()
ownerdomainname = fields.String()
spownerid = fields.String()
ownerdescription = fields.String()
dnis = fields.Nested(owndnis_schema)
app_params = fields.Nested(app_params_schema)
routes.py
---------
from model import owners, owndnis, application_parameters,owners_schema,owndnis_schema, app_params_schema
@mod.route('/api/sp/<spdomainname>', methods=['GET'])
def findSp(spdomainname):
ownerArr = []
owner = owners.query.get(spdomainname)
owner_schema = owners_schema()
if owner:
owners_sm_result = owner_schema.dump(owner).data
return jsonify({'owner': owners_sm_result})
I get the output like this
{
"owner": {
"spownerid": "SYSTEM",
"ownerid": "NEWSP~ZryOZB9BGb",
"dnis": {
"dnisend": "199999",
"dnisstart": "100000"
}
}
}
If I uncomment the commented lines in model.py(owners) to include another table that has foreign key same as owndnis table
but I get this run time error
File "/home/holly/python_ws/new_project_blue/blue/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2383, in _determine_joins
"specify a 'primaryjoin' expression." % self.prop
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship owners.dnis - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
[pid: 18308|app: 0|req: 1/1] 10.133.0.31 () {34 vars in 620 bytes} [Tue May 14 07:22:14 2019] GET /api/sp/NEW-SP => generated 0 bytes in 25 msecs (HTTP/1.1 500) 0 headers in 0 bytes (0 switches on core 0)
The requirement is to have the output like this
I get the output like this
{
"owner": {
"spownerid": "SYSTEM",
"ownerid": "NEWSP~ZryOZB9BGb",
"dnis": {
"dnisend": "199999",
"dnisstart": "100000"
},
"app_params": {
"key":"xxxxx",
"value":"yyyy"
}
}
}
解决方案
我会密切关注文档中的关系模式:
https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html
owners
举个例子,假设您想要和owndnis
...之间建立一对一的关系。
一对一
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child = relationship("Child", uselist=False, back_populates="parent")
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship("Parent", back_populates="child")
未经测试但遵循此模式,在您的情况下owners
视为父母:
# Treated as Parent of One to One
class owners(db.Model):
__tablename__ = 'owners'
ownerid = db.Column('ownerid', db.String(60), primary_key=True) <--- changed primary key
name = db.Column('ownerdomainname', db.String(60), nullable=False)
spownerid = db.Column('spownerid', db.String(60))
dnis = db.relationship("owndnis", uselist=False, back_populates="owners") <--- note change
# child = relationship("Child", uselist=False, back_populates="parent")
# Treated as Child of One to One
class owndnis(db.Model):
__tablename__ = 'owndnis'
ownerid = db.Column('ownerid', db.String(60),
primary_key=True, db.ForeignKey('owners.ownerid')) <-- both a PK and FK
dnisstart = db.Column('dnisstart', db.String(20), nullable=False)
dnisend = db.Column('dnisend', db.String(20))
owner = relationship("owners", back_populates="owndnis") <--- added
# parent = relationship("Parent", back_populates="child")
我使用过back_populates
,但根据文档:
与往常一样,relationship.backref 和 backref() 函数可以用来代替 relationship.back_populates 方法;要在 backref 上指定 uselist,请使用 backref() 函数:
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child", backref=backref("parent", uselist=False))
推荐阅读
- sql - 从组中强制转换的 SQL 查询
- swift - 调用下标 [Swift] 中没有完全匹配
- pytorch - Open3D-ML 和 pytorch
- regex - 在 hive 上第 n 次出现分隔符之前获取所有内容
- reactjs - react-window 元素类型无效:期望字符串(用于内置组件)或类/函数(用于复合组件)但得到:对象
- kotlin - 在 kotlin 中创建 gradle 插件时如何解决 kotlin 库中的冲突
- c++ - 在 C++ 中除以变量与除以 const 时性能下降
- python - 如何直接在 GPU 上或在另一个张量的设备上创建张量?
- c++ - 具有虚函数的静态多态性
- manim - Manim v0.2.0 self.play() 方法