PyInstaller打包FastAPI后端时SQLite数据库无法打开的问题

问题背景 #

使用PyInstaller打包FastAPI后端，已解决依赖问题，但运行可执行文件时出现SQLite数据库无法打开的报错，同时项目依赖基于SQLite的Qdrant向量数据库。

数据库处理代码 #

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from backend.database.models.base import Base

import sys, os

if getattr(sys, 'frozen', False):
    application_path = os.path.dirname(sys.executable)
else:
    application_path = os.path.dirname(os.path.abspath(__file__))

SQLITE_DATABASE_URL = "sqlite:///" + os.path.join(application_path, "backend", "database", "aixplora.db")

class Database:

    def __init__(self):
        self.engine = create_engine(SQLITE_DATABASE_URL, echo=True, connect_args={"check_same_thread": False})
        Base.metadata.create_all(self.engine)
        self.sessionmaker = sessionmaker(autocommit=False, autoflush=False, bind=self.engine)

    def get_session(self):
        return self.sessionmaker()

报错信息 #

INFO:     127.0.0.1:55980 - "GET /config/ HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "sqlalchemy/engine/base.py", line 145, in __init__
  File "sqlalchemy/engine/base.py", line 3293, in raw_connection
  File "sqlalchemy/pool/base.py", line 452, in connect
  File "sqlalchemy/pool/base.py", line 1268, in _checkout
  File "sqlalchemy/pool/base.py", line 716, in checkout
  File "sqlalchemy/pool/impl.py", line 168, in _do_get
  File "sqlalchemy/util/langhelpers.py", line 147, in __exit__
  File "sqlalchemy/pool/impl.py", line 166, in _do_get
  File "sqlalchemy/pool/base.py", line 393, in _create_connection
  File "sqlalchemy/pool/base.py", line 678, in __init__
  File "sqlalchemy/pool/base.py", line 902, in __connect
  File "sqlalchemy/util/langhelpers.py", line 147, in __exit__
  File "sqlalchemy/pool/base.py", line 898, in __connect
  File "sqlalchemy/engine/create.py", line 637, in connect
  File "sqlalchemy/engine/default.py", line 616, in connect
sqlite3.OperationalError: unable to open database file

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "uvicorn/protocols/http/httptools_impl.py", line 435, in run_asgi
  File "uvicorn/middleware/proxy_headers.py", line 78, in __call__
  File "fastapi/applications.py", line 276, in __call__
  File "starlette/applications.py", line 122, in __call__
  File "starlette/middleware/errors.py", line 184, in __call__
  File "starlette/middleware/errors.py", line 162, in __call__
  File "starlette/middleware/base.py", line 108, in __call__
  File "backend/main.py", line 38, in posthog_middleware
  File "starlette/middleware/base.py", line 84, in call_next
  File "starlette/middleware/base.py", line 70, in coro
  File "starlette/middleware/cors.py", line 83, in __call__
  File "starlette/middleware/exceptions.py", line 79, in __call__
  File "starlette/middleware/exceptions.py", line 68, in __call__
  File "fastapi/middleware/asyncexitstack.py", line 21, in __call__
  File "fastapi/middleware/asyncexitstack.py", line 18, in __call__
  File "starlette/routing.py", line 718, in __call__
  File "starlette/routing.py", line 276, in handle
  File "starlette/routing.py", line 66, in app
  File "fastapi/routing.py", line 237, in app
  File "fastapi/routing.py", line 165, in run_endpoint_function
  File "starlette/concurrency.py", line 41, in run_in_threadpool
  File "anyio/to_thread.py", line 31, in run_sync
  File "anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
  File "anyio/_backends/_asyncio.py", line 867, in run
  File "backend/main.py", line 70, in get_config
  File "backend/database/database.py", line 21, in __init__
    Base.metadata.create_all(self.engine)
  File "sqlalchemy/sql/schema.py", line 5796, in create_all
  File "sqlalchemy/engine/base.py", line 3243, in _run_ddl_visitor
  File "contextlib.py", line 137, in __enter__
  File "sqlalchemy/engine/base.py", line 3233, in begin
  File "sqlalchemy/engine/base.py", line 3269, in connect
  File "sqlalchemy/engine/base.py", line 147, in __init__
  File "sqlalchemy/engine/base.py", line 2431, in _handle_dbapi_exception_noconnection
  File "sqlalchemy/engine/base.py", line 145, in __init__
  File "sqlalchemy/engine/base.py", line 3293, in raw_connection
  File "sqlalchemy/pool/base.py", line 452, in connect
  File "sqlalchemy/pool/base.py", line 1268, in _checkout
  File "sqlalchemy/pool/base.py", line 716, in checkout
  File "sqlalchemy/pool/impl.py", line 168, in _do_get
  File "sqlalchemy/util/langhelpers.py", line 147, in __exit__
  File "sqlalchemy/pool/impl.py", line 166, in _do_get
  File "sqlalchemy/pool/base.py", line 393, in _create_connection
  File "sqlalchemy/pool/base.py", line 678, in __init__
  File "sqlalchemy/pool/base.py", line 902, in __connect
  File "sqlalchemy/util/langhelpers.py", line 147, in __exit__
  File "sqlalchemy/pool/base.py", line 898, in __connect
  File "sqlalchemy/engine/create.py", line 637, in connect
  File "sqlalchemy/engine/default.py", line 616, in connect
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file

已尝试在spec文件中添加路径：a.datas += Tree('/Users/patrickgerard/Documents/GitHub/aixplora/backend/database/', 'backend/database/')，但问题仍未解决。

解决方案 #

1. 修正打包后的路径逻辑（核心问题） #

如果使用PyInstaller的onefile模式，运行时资源会被解压到临时目录sys._MEIPASS，而非sys.executable所在目录。修改路径获取代码：

if getattr(sys, 'frozen', False):
    # onefile模式优先使用临时资源目录
    application_path = getattr(sys, '_MEIPASS', os.path.dirname(sys.executable))
else:
    application_path = os.path.dirname(os.path.abspath(__file__))

2. 确保数据库目录存在且有写入权限 #

代码尝试创建数据库文件时，若上级目录不存在会直接报错。提前创建目录：

# 构建数据库目录路径并创建
db_dir = os.path.join(application_path, "backend", "database")
os.makedirs(db_dir, exist_ok=True)
# 拼接数据库文件路径
SQLITE_DATABASE_URL = f"sqlite:///{os.path.join(db_dir, 'aixplora.db')}"

3. 检查spec文件的资源配置 #

• 若Tree方式无效，尝试显式指定单个数据库文件：

  a.datas += [('/Users/patrickgerard/Documents/GitHub/aixplora/backend/database/aixplora.db', 'backend/database/aixplora.db')]
  

• 打包时必须使用该spec文件：

  pyinstaller your_app.spec
  

4. 同步Qdrant的存储路径配置 #

Qdrant的SQLite存储路径也需要适配打包后的环境，显式指定路径：

from qdrant_client import QdrantClient

# 构建Qdrant存储目录
qdrant_dir = os.path.join(application_path, "qdrant_storage")
os.makedirs(qdrant_dir, exist_ok=True)
# 初始化Qdrant客户端
client = QdrantClient(path=qdrant_dir)

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