如何使用Flask-SQLAlchemy连接已有SQLite数据库并读取数据？

问题背景 #

我先完成了Flask基础应用开发，网页正常输出It Works。但在跟进数据库相关教程时，遇到了一系列问题：

• 首次运行代码时抛出sqlite3.OperationalError: no such table:，代码生成的instance文件夹内的Monsters.db为空，未创建目标表
• 手动替换为存有数据的Monsters.db后，又出现'list' object has no attribute 'Name'错误
• 尝试调用SQLAlchemy.create_all()，但生成的数据库仍为空，网页无内容输出

错误代码如下：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
#from sqlalchemy.sql import text

app=Flask(__name__)

db_name='Monsters.db'

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_name

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

# this variable, db, will be used for all SQLAlchemy commands
db = SQLAlchemy(app)

class Creature(db.Model):
    __tablename__ = 'Creatures'
    ID = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.Text)
    AC = db.Column(db.Integer)
    HP = db.Column(db.Integer)
    
@app.route('/')
def index():
    try:
        Creatures = Creature.query.order_by(Creature.Name).all()
        print(Creatures)
        sock_text = '<ul>'
        
        for Monster in Creatures:
            sock_text += '<li>' + Creatures.Name + '</li>'
        sock_text += '</ul>'
        return sock_text
    
    except Exception as e:
        
        # e holds description of the error
        error_text = "<p>The error:<br>" + str(e) + "</p>"
        hed = '<h1>Something is off.</h1>'
        
        return hed + error_text

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

解决方法与最终代码 #

通过查阅文档得知，读取已有数据的数据库表时，需要使用reflect()方法映射已存在的表结构，而非通过定义模型类创建新表。调整代码后成功实现功能，最终代码如下：

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base 

db_name='Monsters.db'
engine=create_engine('sqlite:///' + db_name, echo=False)

Base=declarative_base()
Base.metadata.reflect(engine)

class Creatures(Base):
    __table__ = Base.metadata.tables['Creatures']
    
from sqlalchemy.orm import scoped_session, sessionmaker

from flask import Flask
    
app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_name
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
@app.route('/')
def index():
    try:
        
        db_session=scoped_session(sessionmaker(bind=engine))
        Creature=db_session.query(Creatures.ID, Creatures.Name, Creatures.HP, Creatures.AC)
        
        sock_text = '<ul>'
        
        for Monster in Creature:
            sock_text += '<li>' + Monster.Name + '</li>'
        sock_text += '</ul>'
        
        return sock_text
    
    except Exception as e:
        
        # e holds description of the error
        error_text = "<p>The error:<br>" + str(e) + "</p>"
        hed = '<h1>Something is off.</h1>'
        
        return hed + error_text

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

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