SQLite3 在 Python 中执行 CREATE TABLE 语句时若未提交事务，可能导致看似“语法错误”的异常；实际主因是外键约束定义位置不合法——SQLite 要求 FOREIGN KEY 子句必须在列定义之后、表级约束区域中声明，且需启用外键支持。
sqlite3 在 python 中执行 `create table` 语句时若未提交事务，可能导致看似“语法错误”的异常；实际主因是外键约束定义位置不合法——sqlite 要求 `foreign key` 子句必须在列定义之后、表级约束区域中声明，且需启用外键支持。
在您提供的代码中，devices 表的建表语句存在两个关键问题，直接导致 sqlite3.OperationalError: near "device_name": syntax error：
- 外键定义位置错误：SQLite 不允许将 FOREIGN KEY (...) REFERENCES ... 作为独立列的属性（即不能跟在 user_id TEXT, 后面直接写），而必须作为表级约束，放在所有列定义之后、右括号 ) 之前，并以 CONSTRAINT 或直接 FOREIGN KEY 形式声明；
- 外键功能默认禁用：SQLite 默认不启用外键约束检查，即使语法正确，外键也不会生效，且某些版本/配置下可能引发解析歧义。
✅ 正确写法如下（已修复语法 + 启用外键 + 显式提交）：
import sqlite3
def create_all_sql():
with sqlite3.connect("data.db", check_same_thread=False) as con:
# ✅ 关键：启用外键支持（必须在建表前执行）
con.execute("PRAGMA foreign_keys = ON")
c = con.cursor()
# users 表（语法本身无误，但建议移除冗余 UNIQUE —— PRIMARY KEY 已隐含唯一性）
c.execute("""
CREATE TABLE IF NOT EXISTS users (
user_id TEXT PRIMARY KEY,
user_name TEXT,
hashed_password BLOB,
hashed_salt BLOB,
device_ver BLOB
)
""")
# devices 表：✅ 修正外键为表级约束，且位置合规
c.execute("""
CREATE TABLE IF NOT EXISTS devices (
device_id BLOB PRIMARY KEY,
user_id TEXT,
device_name TEXT,
device_type TEXT,
north_cord TEXT,
east_cord TEXT,
timestamp TEXT,
-- ✅ 表级 FOREIGN KEY 约束（必须放在这里）
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)
""")
# ✅ 显式提交事务（with 语句会自动 commit，但显式调用更清晰；此处非必需但推荐）
con.commit()
# 执行建表
create_all_sql()? 重要注意事项：
- con.commit() 在 with sqlite3.connect(...) as con: 上下文中并非必需（该上下文会在退出时自动提交），但显式调用可提升可读性与调试可控性；
- con.close() 也不需手动调用，with 语句会自动关闭连接；
- check_same_thread=False 仅在多线程共享连接时需要，生产环境建议为每个线程使用独立连接；
- 若后续插入数据时外键失效，请确认每次连接后都执行 PRAGMA foreign_keys = ON（它不跨连接持久化）；
- SQLite 的 BLOB 类型适合存储加密密钥或二进制设备标识，但请确保写入前已正确序列化（如 bytes 对象）。
通过以上修正，您的数据库结构将语法合规、约束有效，且具备良好的可维护性与兼容性。
