Flutter SQLite查询报错：no such table: month 问题求助

SQLiteLog( 6157): (1) no such table: month in "SELECT * FROM month"

按照Flutter官方文档配置了名为bornmonth的SQLite数据库，但查询数据时出现上述报错，尝试过GeeksforGeeks的教程方案仍未解决。以下是相关代码：

MyDB.dart #

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DBProvider {
  DBProvider._();
  static final DBProvider db = DBProvider._();
}

class database{
  Future<Database> initializedDB() async {
    String path = await getDatabasesPath();
    return openDatabase(
      join(path, 'assets/database/bornmonth.db'),
    );
  }

  // retrieve data
  Future<List<bornmonth>> retrieveData() async {
    final Database db = await initializedDB();
    final List<Map<String, dynamic>> queryResult = await db.query('month');
    return queryResult.map((e) => bornmonth.fromMap(e)).toList();
  }

}

class bornmonth {
  final int id;
  final String month;
  final String job;
  final String behavior;
  final String love;
  final String fate;
  final String main;
  final String beuty;
  final String power;
  final String lying;
  final String sucess;
  final String weakness;

  const bornmonth({
    required this.id,
    required this.month,
    required this.job,
    required this.behavior,
    required this.love,
    required this.fate,
    required this.main,
    required this.beuty,
    required this.lying,
    required this.power,
    required this.sucess,
    required this.weakness

  });

  bornmonth.fromMap(Map<String, dynamic> result)
      : id = result["id"],
        month = result["month"],
        job = result["job"],
        beuty = result["beuty"],
        love = result["love"],
        behavior = result["behavior"],
        fate = result["fate"],
        main = result["main"],
        lying = result["lying"],
        power = result["power"],
        sucess = result["success"],
        weakness = result["weakness"];

  Map<String, Object> toMap() {
    return {
      'id': id,
      'month': month,
      'job': job,
      'beuty': beuty,
      'love': love,
      'behavior': behavior,
      'main': main,
      'fate': fate,
      'lying': lying,
      'power': power,
      'weakness': weakness,
      'sucess': sucess,
    };
  }

}

MainTexts.dart #

import 'package:bornmonth/MyDB.dart';
import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';

class maintexts extends StatelessWidget {
  final String hint;
  const maintexts({super.key, required this.hint});

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      body: texts(hinttext: hint),
    );
  }
}

class texts extends StatefulWidget {
  final String hinttext;
  const texts({super.key, required this.hinttext});

  @override
  State<texts> createState() => _textsState();
}

class _textsState extends State<texts> {
  late database handler = database();
  @override
  Widget build(BuildContext context) {
    return Directionality(
      textDirection: TextDirection.rtl,
      child: Scaffold(
        backgroundColor: Colors.white,
        appBar: AppBar(
          leading: IconButton(
            icon: const Icon(Icons.arrow_back, color: MyColorsSample.primaryDark),
            onPressed: () => Navigator.pop(context),
          ),
          title: Text(widget.hinttext, style: TextStyle(color: MyColorsSample.primaryDark),),
          backgroundColor: Colors.white,
          elevation: 0,
          actions: [
            IconButton(
                onPressed: () {},
                icon: const Icon(
                  Icons.copy,
                  color: Colors.blueAccent,
                )),
            IconButton(
                onPressed: () {},
                icon: const Icon(
                  Icons.share,
                  color: Colors.blueAccent,
                )),

          ],

        ),

        body: FutureBuilder(
          future: handler.retrieveData(),
          builder: (BuildContext context, AsyncSnapshot<List<bornmonth>> snapshot) {
            if(snapshot.hasData) {
              return ListView.builder(
                itemCount: snapshot.data?.length,
                itemBuilder: (BuildContext context, int index) {
                  //read data from table month
                  return Text(snapshot.data![index].love);
                },

              );
            }else {
              return const Center(child: CircularProgressIndicator());
            }
        },

        ),

        ),
      );
  }
}

class MyColorsSample {
  static const Color primary = Color(0xFF12376F);
  static const Color primaryDark = Color(0xFF0C44A3);
}

问题原因及解决办法 #

1. 数据库文件未正确复制到应用可读写目录 #

sqflite无法直接访问assets目录下的数据库文件，必须先将文件复制到getDatabasesPath()返回的应用私有目录。修改initializedDB方法：

import 'dart:io';
import 'package:flutter/services.dart';

Future<Database> initializedDB() async {
  String databasesPath = await getDatabasesPath();
  String path = join(databasesPath, 'bornmonth.db');

  // 检查数据库是否已存在，不存在则从assets复制
  bool exists = await databaseExists(path);
  if (!exists) {
    // 确保父目录存在
    await Directory(dirname(path)).create(recursive: true);
    // 从assets读取并写入目标路径
    ByteData data = await rootBundle.load(join('assets', 'database', 'bornmonth.db'));
    List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    await File(path).writeAsBytes(bytes, flush: true);
  }

  return openDatabase(path);
}

同时在pubspec.yaml中声明assets资源：

flutter:
  assets:
    - assets/database/bornmonth.db

2. 验证数据库文件本身结构 #

用SQLite可视化工具（如DB Browser for SQLite）打开assets/database/bornmonth.db，确认是否存在month表，检查表名拼写是否完全匹配（SQLite默认区分大小写）。

3. 添加数据库初始化回调（可选） #

如果需要确保表结构正确，可在openDatabase中添加版本和回调：

return openDatabase(
  path,
  version: 1,
  onCreate: (db, version) async {
    await db.execute('''
      CREATE TABLE IF NOT EXISTS month (
        id INTEGER PRIMARY KEY,
        month TEXT,
        job TEXT,
        behavior TEXT,
        love TEXT,
        fate TEXT,
        main TEXT,
        beuty TEXT,
        power TEXT,
        lying TEXT,
        success TEXT,
        weakness TEXT
      )
    ''');
  },
);

4. 清理应用缓存重新测试 #

卸载当前应用后重新安装，避免之前创建的空数据库文件干扰。

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