时间戳

首页 > 解决方案 > 原子替换 SQLite 数据库的内容，最好用 SQL 或 python3

python-3.x - 原子替换 SQLite 数据库的内容，最好用 SQL 或 python3

问题描述

我有一个 python 脚本，它支持--edit-the-database在脚本的 SQLite 数据库的转储上调用用户首选编辑器的选项。此选项旨在促进对脚本的其他选项不提供访问权限的数据库部分的快速访问，特别是在开发此脚本期间。

一旦脚本转储数据库的内容，启动编辑器并验证修改的内容仍然有效，那么它需要替换现有的数据库内容。

首先，它通过执行此 SQL（使用 python 的 sqlite 模块）删除所有现有内容：

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ('table', 'index', 'trigger');
PRAGMA writable_schema = 0;
VACUUM;

然后它使用 sqlite 模块的executescript()方法加载新内容：

cursor.executescript(sql_slurped_from_user_modified_dump)

问题是这两个操作（删除现有内容，加载新内​​容）不是原子执行的：CTRL-C在错误的时刻按下，数据库内容已经丢失。

如果我尝试在事务中执行这两个代码块，则会收到错误消息：

Error: cannot VACUUM from within a transaction

如果我保留交易但删除交易，VACUUM那么我会收到错误消息：

Error: table first_table already exists

我有一个丑陋的解决方法：在调用编辑器之前，脚本将转储文件复制到安全位置，向用户写入警告消息：

WARNING: if anything goes wrong then a backup of the database 
    can be found in /some/path

并且，如果脚本继续并完成加载新内容，则它会删除转储的副本。但这太丑了！

我可以使用DROP TABLE而不是DELETE FROM sqlite_master ...，但如果我试图允许以这种方式修改数据库，那么我允许表列表本身可能会更改。即，如果用户将此添加到转储中：

CREATE TABLE t3 (n INT);

然后是这样的硬编码列表DROP：

BEGIN TRANSACTION
DROP TABLE t1;
DROP TABLE t2;
DROP INDEX ...
...
cursor.executescript(sql_slurped_from_user_modified_dump)
...
END TRANSACTION;

不会第二次工作（因为它不会删除表 t3）。

我可以使用文件系统原子操作（例如：将修改后的转储加载到新的数据库文件中；将新文件硬链接到旧文件），但这需要脚本关闭其数据库连接并在之后重新打开它，这是出于某种原因超出这个问题的范围，我宁愿不做。

有没有人有更好的想法来原子地替换表列表不可预测的数据库的全部内容？

标签： python-3.xsqlite

解决方案

万一谷歌把你带到这里......

我设法用这样的伪代码完成了任务的前半部分（删除单个事务中的现有内容）：

-- Make the order in which tables are dropped irrelevant. Unfortunately, this
-- cannot be done just around the table dropping because it doesn't work inside
-- transactions.
PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;

indexes =  (SELECT name 
            FROM sqlite_master 
            WHERE type = 'index' AND 
                  name NOT LIKE 'sqlite_autoindex_%';)
triggers = (SELECT name 
            FROM sqlite_master 
            WHERE type = 'trigger';)
tables =   (SELECT name 
            FROM sqlite_master
            WHERE type = 'table';)

for thing in indexes+triggers+tables:
    DROP thing;

在这一点上，我认为后半部分（在同一事务中加载新内容）将是这样的：

cursor.executescript(sql_slurped_from_user_modified_dump)

END TRANSACTION;
-- Reinstate foreign key constraints.
PRAGMA foreign_keys = 1;

不幸的是，在两个块的中间按 CTRL-C 会导致任何空数据库。原因？cursor.executescript()在运行提供的 SQL之前立即 COMMIT 执行。这将上面的代码变成了两个事务！

这不是我第一次被这个模块的隐藏事务管理所吸引，但这次我有动力尝试使用apsw模块。这个转换非常简单。代码的后半部分现在如下所示：

cursor.execute(sql_slurped_from_user_modified_dump)

END TRANSACTION;
-- Reinstate foreign key constraints.
PRAGMA foreign_keys = 1;

它完美无缺！

推荐阅读