Debian GCP服务器上SQLAlchemy SQLite数据库的正确路径字符串是什么？

问题背景 #

开发机使用Arch系统，需要将SQLite数据库存储在/home/$USER/projectname/directory/目录下。以下代码在本地正常运行，但部署到Debian GCP服务器后失效：

engine = create_engine('sqlite:///directory/users.sqlite', echo=True)

尝试替换$USER为路径字符串仍无效：

engine = create_engine('sqlite:///home/$USER/projectname/directory/users.sqlite', echo=True)

仅以下代码可运行，但会在错误目录创建数据库：

engine = create_engine('sqlite:///users.sqlite', echo=True)

报错信息 #

Traceback (most recent call last):
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1267, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
    with util.safe_reraise():
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 167, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 615, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: unable to open database file

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

Traceback (most recent call last):
  File "/home/user/project/trade/db.py", line 35, in <module>
    base.metadata.create_all(engine)
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 5792, in create_all
    bind._run_ddl_visitor(
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3238, in _run_ddl_visitor
    with self.begin() as conn:
  File "/usr/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3228, in begin
    with self.connect() as conn:
         ^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3264, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 147, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2426, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 3288, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 452, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 1267, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 169, in _do_get
    with util.safe_reraise():
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/impl.py", line 167, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
    self.__connect()
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
    with util.safe_reraise():
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 147, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/create.py", line 637, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/project/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 615, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: https://sqlalche.me/e/20/e3q8)

解决方案 #

问题根源 #

1. 环境变量未解析：Python字符串中的$USER不会被系统自动解析为实际用户名，会被当作路径的一部分，导致路径无效。
2. 相对路径不一致：开发机与服务器的Python脚本工作目录不同，本地的相对路径directory/users.sqlite在服务器上指向的不是目标目录。
3. 目录权限/存在性：目标目录可能不存在，或Python进程没有读写权限。

正确路径写法 #

方法1：硬编码实际用户名

将$USER替换为服务器上的真实用户名（例如user），使用绝对路径：

engine = create_engine('sqlite:////home/user/projectname/directory/users.sqlite', echo=True)

注意：SQLite的URL格式为sqlite:///（三个斜杠）加绝对路径，Linux绝对路径以/开头，因此最终是四个斜杠（sqlite:////...）。

方法2：动态获取用户目录

使用Python的os模块动态获取当前用户主目录，避免硬编码，跨平台兼容：

import os
from sqlalchemy import create_engine

user_home = os.path.expanduser("~")
db_path = os.path.join(user_home, "projectname", "directory", "users.sqlite")
engine = create_engine(f'sqlite:///{db_path}', echo=True)

额外检查项 #

• 确认目标目录存在，不存在则创建：

  mkdir -p /home/user/projectname/directory/
  

• 确保Python进程对该目录有读写权限：

  chown user:user /home/user/projectname/directory/
  chmod 755 /home/user/projectname/directory/
  

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