如何解决Flask中SQLAlchemy访问SQLite的item表时的‘表不存在’错误？

学习YouTube上的Python Flask课程，使用flask_sqlalchemy操作数据库。完成代码拆分（从单文件夹两文件改为多文件多文件夹结构）后，访问网页的/market路径时出现以下错误：

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: item
[SQL: SELECT item.id AS item_id, item.name AS item_name, item.price AS item_price, item.barcode AS item_barcode, item.description AS item_description
FROM item]

项目结构为名为FlaskMarket的文件夹，包含run.py文件和market子文件夹，具体文件内容如下：

FlaskMarket/run.py #

from market import app

if __name__ == "__main__":
    app.run(debug=True)

FlaskMarket/market/instance/market.db #

包含item表的数据库文件

FlaskMarket/market/templates/base.html #

<!doctype html>
<html lang="en">
   <head>
      <!-- Required meta tags -->
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
      <!-- Bootstrap CSS -->
      <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
      <title>
        {% block title %}

        {% endblock %}
      </title>
   </head>
   <body>
     <nav class="navbar navbar-expand-md navbar-dark bg-dark">
       <a class="navbar-brand" href="#">Flask Market</a>
       <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav">
         <span class="navbar-toggler-icon"></span>
       </button>
       <div class="collapse navbar-collapse" id="navbarNav">
         <ul class="navbar-nav mr-auto">
           <li class="nav-item active">
               <a class="nav-link" href="{{ url_for('home_page') }}">Home <span class="sr-only">(current)</span></a>
           </li>
           <li class="nav-item">
               <a class="nav-link" href="{{ url_for('market_page') }}">Market</a>
           </li>
       </ul>
       <ul class="navbar-nav">
           <li class="nav-item">
               <a class="nav-link" href="#">Login</a>
           </li>
           <li class="nav-item">
               <a class="nav-link" href="#">Register</a>
           </li>
       </ul>
       </div>
     </nav>
       {% block content %}
       {% endblock %}
      <!-- Future Content here -->



      <!-- Optional JavaScript -->
      <!-- jQuery first, then Popper.js, then Bootstrap JS -->
      <script src='https://kit.fontawesome.com/a076d05399.js'></script>
      <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
      <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
      <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
   </body>
   <style>
      body {
        background-color: #212121;
        color: white
      }
   </style>
</html>

FlaskMarket/market/templates/home.html #

{% extends 'base.html' %}
{% block title %}
  Home Page
{% endblock %}

{% block content %}
  This is our content for the Home Page
{% endblock %}

FlaskMarket/market/templates/market.html #

{% extends 'base.html' %}
{% block title %}
  Market Page
{% endblock %}

{% block content %}
  <table class="table table-hover table-dark">
    <thead>
        <tr>
            <!-- Your Columns HERE -->
            <th scope="col">ID</th>
            <th scope="col">Name</th>
            <th scope="col">Barcode</th>
            <th scope="col">Price</th>
            <th scope="col">Options</th>
        </tr>
    </thead>
    <tbody>
        <!-- Your rows inside the table HERE: -->
        {% for item in items %}
          <tr>
            <td>{{ item.id }}</td>
            <td>{{ item.name }}</td>
            <td>{{ item.barcode }}</td>
            <td>${{ item.price }}</td>
            <td>
              <button class="btn btn-outline btn-info">More Info</button>
              <button class="btn btn-outline btn-success">Purchase this Item</button>
            </td>
          </tr>
        {% endfor %}
    </tbody>
  </table>
{% endblock %}

FlaskMarket/market/init.py #

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///market.db'
db = SQLAlchemy(app)

from market import routes

FlaskMarket/market/models.py #

from market import db


class Item(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(length=30), nullable=False, unique=True)
    price = db.Column(db.Integer(), nullable=False)
    barcode = db.Column(db.String(length=12), nullable=False, unique=True)
    description = db.Column(db.String(length=1024), nullable=False, unique=True)

    def __repr__(self):
        return f'Item {self.name}'

FlaskMarket/market/routes.py #

from market import app
from flask import render_template
from market.models import Item

@app.route('/')
@app.route('/home')
def home_page():
    return render_template('home.html')

@app.route('/market')
def market_page():
    items = Item.query.all()
    return render_template('market.html', items=items)

另外，VSCode在FlaskMarket和market文件夹下都生成了__pycache__文件夹，同时instance文件夹（包含数据库）也在两个目录下都出现了。

1. 修正数据库路径配置 #

当前__init__.py中的数据库URI指向FlaskMarket根目录下的market.db，但实际数据库在market/instance/market.db。修改__init__.py的配置：

import os
from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 基于应用实例路径构建数据库URI，确保指向正确的文件
app.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{os.path.join(app.instance_path, "market.db")}'
# 确保instance文件夹存在，避免创建失败
os.makedirs(app.instance_path, exist_ok=True)
db = SQLAlchemy(app)

from market import routes, models

2. 确保Model类被正确加载 #

在__init__.py中添加models的导入，让SQLAlchemy识别Item表的定义，否则无法和数据库中的表建立映射关系。

3. 清理冗余文件 #

删除FlaskMarket根目录下的instance和__pycache__文件夹，只保留market子文件夹内的对应文件，避免Flask读取错误的数据库文件。

4. 重新同步数据库（若需要） #

如果修改路径后仍提示表不存在，执行以下步骤创建/同步表：

1. 设置环境变量：
   • Windows：set FLASK_APP=run.py
   • Linux/macOS：export FLASK_APP=run.py
2. 启动Flask Shell：flask shell
3. 在Shell中执行：

   from market import db
   db.create_all()
   

这会根据models.py中的定义，在目标数据库中创建对应的表。

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