时间戳

首页 > 解决方案 > 在 Flask 中使用外键创建堆栈

python - 在 Flask 中使用外键创建堆栈

问题描述

我有一个表客户和表覆盖率。一个客户可以有很多覆盖范围，所以我尝试创建一个一对多的关联。每个类表都在单独的模型 customer.py 和 coverage.py 中创建，它们都在同一个目录模型中：

1-客户模型：

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'

id = Column(Integer, primary_key=True)
name = Column(Integer, unique=True)
networkList = Column(String(255), unique=False)
coverages = relationship('Coverage', backref='customer_fk')
def __init__(self, name=None, networkList=None):
    self.name = name
    self.networkList = networkList

def __repr__(self):
    return "<Customer(name='%s', networkList='%s')>" % (self.name, self.networkList)

2- 覆盖模型：

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Coverage(Base):
__tablename__ = 'coverage'

id = Column(Integer, primary_key=True)
alias = Column(String(5), unique=False)
cov = Column(Integer, unique=False)
customer_fk = Column(Integer, ForeignKey('customer.id'))

def __init__(self, alias=None, cov=None, customer_fk=None):
    self.alias = alias
    self.cov = cov
    self.customer_fk = customer_fk

def __repr__(self):
    return "<Coverage(alias='%s', cov='%s', customer_fk='%s')>" % (self.alias, self.cov, self.customer_fk)

3-主程序（app.py）：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from models.agreements import Base, Agreements
from models.customer import Base, Customer
from models.country import Base, Country
from models.coverage import Base, Coverage
from database.agreementDetails import AgreementDetails
app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URI'] = 
'mysql://root:Walid78!@192.168.43.201/steeringiot'
app.config['SQLALCHEMY_DATABASE_URI'] = 
'mysql://root:Walid78!@localhost/steeringiot'
db = SQLAlchemy(app)
@app.before_first_request
def setup():
# Recreate database each time for demo
Base.metadata.drop_all(bind=db.engine)
Base.metadata.create_all(bind=db.engine)

# define the json file to parse
# jsonurl = 
#'/home/walid/devops/steeringiot/static/data/tiers_VFGroup.json'
#
# # Start Parsing the agreement details and commit into database 
# steeringiot:
# agreementDetails = AgreementDetails()
# agrs = agreementDetails.getAgreementsDetails(jsonurl)
# db.session.add(Agreements(agrs[0], agrs[1], agrs[2], agrs[3], 
# agrs[4], agrs[5], agrs[6], agrs[7], agrs[8]))
db.session.commit()
db.session.remove()
@app.route('/')
def root():
agreements = db.session.query(Agreements).all()
return u"<br>".join([u"{0}: {0}".format(agreement.agreementId) for 
agreement in agreements])
if __name__ == '__main__':
app.run('127.0.0.1', 5000, debug=True)

4- 我收到以下错误的堆栈： sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'coverage.customer_fk' could not find table 'customer' 用于生成目标列 'id' 的外键

和表客户，覆盖范围没有被创建......

谢谢你们的帮助！！

标签： pythonflasksqlalchemy

解决方案

未经测试。

1-客户模型：

from flask import current_app as app
from sqlalchemy.orm import relationship

db = app.db


class Customer(db.Model):
    __tablename__ = 'customer'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Integer, unique=True)
    networkList = db.Column(db.String(255), unique=False)
    coverages = db.relationship('Coverage', backref='customer_fk')


    def __init__(self, name=None, networkList=None):
        self.name = name
        self.networkList = networkList


    def __repr__(self):
        return "<Customer(name='%s', networkList='%s')>" % (self.name, self.networkList)

2- 覆盖模型：

from flask import current_app as app

db = app.db


class Coverage(db.Model):
    __tablename__ = 'coverage'

    id = db.Column(db.Integer, primary_key=True)
    alias = db.Column(db.String(5), unique=False)
    cov = db.Column(db.Integer, unique=False)
    customer_fk = db.Column(db.Integer, db.ForeignKey('customer.id'))


    def __init__(self, alias=None, cov=None, customer_fk=None):
        self.alias = alias
        self.cov = cov
        self.customer_fk = customer_fk


    def __repr__(self):
        return "<Coverage(alias='%s', cov='%s', customer_fk='%s')>" % (self.alias, self.cov, self.customer_fk)

3-主程序（app.py）：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
   app.config['SQLALCHEMY_DATABASE_URI'] = 
'mysql://root:[yourpassword]@localhost/steeringiot'

db = SQLAlchemy(app)
app.db = db

with app.app_context():
    from models.agreements import Agreements
    from models.customer import Customer
    from models.country import Country
    from models.coverage import Coverage


@app.before_first_request
def setup():
    # Recreate database each time for demo
    db.drop_all()
    db.create_all()


@app.route('/')
def root():
    agreements = db.session.query(Agreements).all()
    return u"<br>".join([u"{0}: {0}".format(agreement.agreementId) for 
                         agreement in agreements])


if __name__ == '__main__':
    app.run('127.0.0.1', 5000, debug=True)

使用理由app.app_context()：https ://stackoverflow.com/a/44532790/2644759

推荐阅读