python - 在插入或忽略后获取主键的 ID

Question

我正在使用 sqlite 并有一个如下所示的表:

CREATE TABLE IF NOT EXISTS dirnames (dirnameid INTEGER PRIMARY KEY,
                                     dirname TEXT NOT NULL UNIQUE);
CREATE INDEX IF NOT EXISTS dirnames_idx1 ON dirnames(dirnameid);
CREATE INDEX IF NOT EXISTS dirnames_idx2 ON dirnames(dirname);

我想获取任意目录的dirnameid，所以现在我正在做如下所示的事情:

INSERT or IGNORE INTO dirnames (dirname) VALUE (?)
SELECT dirnameid from dirnames where dirname=?

其中 ? 替换为我的目录名称。

有没有更有效的方法来做到这一点

Answer 1

无法进一步优化此查询，因为它需要在 sqlite 上执行此操作的命令 - 该命令不存在。

但是，有一种方法可以“优化”这一点:您可以尝试使用 LBYL 策略，这样如果目录已经存在，您就可以“保存”往返。

伪代码(因为我不知道你的代码是什么样子):

import sqlite3

#(...)

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

dirname = 'foo'
dirnameid = None

cursor.execute("SELECT dirnameid from dirnames where dirname=?;", (dirname,))
dirnameid = cursor.fetchone()[0]

if not dirnameid:
    cursor.execute("INSERT or IGNORE INTO dirnames (dirname) VALUES (?);", (dirname,))

    # WARNING: The following line should be used if, and ONLY if the access to the sqlite is not concurrent (AKA: Only one connection inserting to the table)
    dirnameid = cursor.lastrowid

    # Otherwise, use this:
    cursor.execute("SELECT dirnameid from dirnames where dirname=?;", (dirname,))
    dirnameid = cursor.fetchone()[0]

connection.close()

遗憾的是，如果这不是在表上工作的唯一程序/线程，则此代码将添加另一个往返。

但我必须警告你，这似乎是一个过早的优化，应该不惜一切代价避免! (https://softwareengineering.stackexchange.com/questions/80084/is-premature-optimization-really-the-root-of-all-evil)