Flask+SQLAlchemy运行报SQLALCHEMY_DATABASE_URI相关错误如何解决

你在main.py和model.py两个文件中分别创建了独立的Flask应用实例与SQLAlchemy实例：

• 你仅在main.py的app实例中配置了SQLALCHEMY_DATABASE_URI
• model.py中绑定模型的db实例对应的是该文件内初始化的无数据库配置的app实例
• 调用model.Doc.query时触发的是无配置的db实例，因此抛出找不到数据库URI的报错

1. 修改model.py代码 #

删除文件内独立初始化的Flask app实例，调整为延迟绑定扩展的模式，修改后代码如下：

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Column, Integer, String, Date, ForeignKey
from flask_marshmallow import Marshmallow

# 仅初始化扩展，不绑定应用实例，后续在主文件中绑定
db = SQLAlchemy()
ma = Marshmallow()


class Aud(db.Model):
    __tablename__ = 'aud'
    __table_args__ = {'schema': 'cirsx'}
    aud_num = Column(Integer, primary_key=True)
    aud_name = Column(String, nullable=False, unique=True)
    aud_desc = Column(String)


class AudSchema(ma.Schema):
    class Meta:
        fields = ('aud_num', 'aud_name', 'aud_desc')


class DocTyp(db.Model):
    __tablename__ = 'doctyp'
    __table_args__ = {'schema': 'cirsx'}
    doctyp_num = Column(Integer, primary_key=True)
    doctyp_name = Column(String, nullable=False, unique=True)
    doctyp_desc = Column(String)


class DocTypSchema(ma.Schema):
    class Meta:
        fields = ('doctyp_num', 'doctyp_name', 'doctyp_desc')


class Doc(db.Model):
    __tablename__ = 'doc'
    __table_args__ = {'schema': 'cirsx'}
    doc_num = Column(Integer, primary_key=True)
    # 修正外键写法，需指定 schema.表名.字段名
    doctyp_num = Column(Integer, ForeignKey('cirsx.doctyp.doctyp_num'))
    aud_num = Column(Integer, ForeignKey('cirsx.aud.aud_num'))
    doc_path = Column(String, nullable=False)
    title = Column(String, nullable=False)
    author = Column(String)
    keywords = Column(String)
    pub_dt = Column(Date)
    doc_abs = Column(String)
    doc_txt = Column(String)


class DocSchema(ma.Schema):
    class Meta:
        fields = ('doc_num',
                  'doctyp_num',
                  'aud_num',
                  'doc_path',
                  'title',
                  'author',
                  'keywords',
                  'pub_dt',
                  'doc_abs',
                  'doc_txt')


aud_schema = AudSchema()
aud_schemas = AudSchema(many=True)
doctyp_schema = DocTypSchema()
doctyp_schemas = DocTypSchema(many=True)
doc_schema = DocSchema()
doc_schemas = DocSchema(many=True)

2. 修改main.py代码 #

移除重复初始化的SQLAlchemy实例，将配置好的app绑定到model中定义的扩展，修改后代码如下：

import psycopg2
import model
import os
from flask import Flask, jsonify, request

uname = os.environ['uname']
pas = os.environ['pas']
url = os.environ['url']
port = os.environ['port']
dbase = os.environ['dbase']


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://' + uname + ':' + pas + '@' + url + ':' + port + '/' + dbase
# 给model中的扩展绑定当前app
model.db.init_app(app)
model.ma.init_app(app)


@app.route('/test')
def test():
    tst = model.Doc.query.filter_by(doc_num=1).first()
    # 用定义好的schema序列化模型对象，否则jsonify会报错
    return jsonify(model.doc_schema.dump(tst))


if __name__ == '__main__':
   app.run()

附加优化建议 #

可以额外添加app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False关闭不必要的对象修改追踪，减少性能开销。

内容的提问来源于stack exchange，提问作者Brad Davison