使用 Python 检查然后更新 sqlite 中的行 [英] Examining then updating rows in sqlite with Python

问题描述

我决定不喜欢 Mendeley 提供的自动归档和重命名，但是我发现它会跟踪 sqlite 数据库中的所有内容，我可以轻松地从 Python 中读取和修改这些内容.

I've decided that I'm not fond of the automatic filing and renaming provided by Mendeley, but have found that it keeps track of everything in an sqlite database that I can easily read and modify from Python.

我的问题是，如果我要迭代包含文件路径和哈希的表的行，这些文件路径和哈希用作数据库中其他地方使用的标识符，那么用于将路径更新为的适当模式是什么我继续前进，检查文件是否需要重命名，然后在移动文件时更新数据库中的行.

My question is, if I'm going to iterate over the rows of a table containing file paths and hashes that are serving as identifiers that are used elsewhere in the database, what is an appropriate pattern to use to update the paths as I move along, check if the file needs renaming, and then want to update the row in the db as I move the file.

我开始组合一些 Python 来执行此操作，但在我看来，我可能需要预先进行查询以获取我稍后将要遍历的所有行，然后为以下内容执行 REPLACE 或 UPDATE 语句我决定移动的文件.

I started putting together some Python to do this, but it seems to me that I probably need to do a query upfront to get all the rows I'm going to later iterate through, and then subsequently do REPLACE or UPDATE statements for files I've decided to move.

我不熟悉 sqlite3 模块的内部结构，但我认为在迭代用于原始选择的游标时执行 REPLACE/UPDATE 是一个坏主意.

I'm not familiar with the internals of the sqlite3 module, but I presume it would be a bad idea to do REPLACE/UPDATEs while iterating on the cursor used for the original select.

是否有另一种不需要使用 REPLACE/UPDATE 返回的方法?我确实需要从其他表进行查询，使用文件哈希来获取其他一些元数据，以便构建重命名.

Is there another way to do this that doesn't require going back with REPLACE/UPDATEs? I do need to do queries from other tables, using the file hash to get some other metadata in order to construct the rename.

推荐答案

我认为除了通过 SQL -- UPDATE 或 INSERT OR REPLACE 之外，没有其他方法可以修改表中的行.(很容易认为 sqlite3.Row 对象可能允许赋值，并写回它代表的行，但不允许.)

I don't think there's a way to modify the rows in the table other than via SQL -- UPDATE, or INSERT OR REPLACE. (It's tempting to think that the sqlite3.Row object might allow assignment, and write back to the row it represents, but no.)

至于在原始 SELECT 使用了一个出色的游标时执行 UPDATE(通过第二个游标)——你问这是否是一个坏主意——我不知道你是否担心正确性或性能(正确性:游标会混淆并且不会迭代它应该只迭代一次的所有行；性能:游标确实迭代它应该只迭代一次的所有行，但有一堆昂贵的额外查询)?

As for doing UPDATEs (via a 2nd cursor) while there's an outstanding cursor used for the original SELECT -- you ask if this is a bad idea -- I don't know if you're worried about correctness or performance (correctness: the cursor gets confused and doesn't iterate all the rows it should exactly once; performance: the cursor does iterate all the rows it should exactly once but there are a bunch of expensive extra queries)?

WRT 正确性，这似乎工作正常——我只是做了一个快速测试，我创建了两个游标，c1 和 c2，然后在 c1 上执行了一个 SELECT 命令，然后在 c2 上执行了一个影响相同行的 UPDATE 命令，然后查看了 c1.fetchall() 的结果.它仍然包含所有正确的行，尽管第一行中的数据已经过时(可能是通过初始查询获取的)并且后面行中的数据已更新(可能仅在更新后使用 fetchall 获取).

WRT correctness, this seems to work fine -- I just did a quick test where I created two cursors, c1 and c2, then on c1 executed a SELECT command, then on c2 executed an UPDATE command affecting the same rows, then looked at the result of c1.fetchall(). It still contained all the right rows, though the data in the first row was stale (probably fetched with the initial query) and the data in the later rows was updated (probably fetched only with the fetchall after the update).

(此外，如果 c2 上的 UPDATE 影响 c1 上的 SELECT 将返回哪些记录，则将不再提取此类记录，但显然已提取的任何内容都已提取，并且 cursor.execute() 似乎再次立即获取第一条记录，因此使用与上面相同的命令顺序并使用 UPDATE 命令使原始 SELECT 什么也找不到，后面的 c1.fetchall() 仍然返回一条记录.)

(Also, if the UPDATE on c2 affects which records would be returned by the SELECT on c1, such records will no longer be fetched, but obviously anything already fetched was already fetched, and again the cursor.execute() seems to fetch the first record immediately, so using the same command order as above and with an UPDATE command that would make the original SELECT find nothing, the later c1.fetchall() still returns one record.)

至于性能，我不知道——从上一段来看，在我看来它必须重复查询，这可能会或可能不会很昂贵，具体取决于查询.(它可能不是字面上的重复；也许这项工作无论如何都要推迟到实际获取.)

As for performance, I don't know -- from the preceding paragraph it looks to me like it has to repeat the query, which may or may not be expensive depending on the query. (It may not be literally repeating it; perhaps this work is deferred till the actual fetch anyway.)

小故事:我认为没有 UPDATE 或 REPLACE 就没有其他方法可以做到这一点，而且我认为即使在迭代原始游标时执行这些 UPDATE 也可以正常工作.

Short story: I don't think there's another way to do this without UPDATE or REPLACE, and I think it will work ok to do those UPDATEs even while iterating on the original cursor.

这篇关于使用 Python 检查然后更新 sqlite 中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！