SQLite在使用executemany时获取id并插入 [英] SQLite get id and insert when using executemany

问题描述

我正在优化我的代码，并减少查询量.这些曾经处于循环中，但我正在尝试重构我的代码以完成这样的操作.如何让第二个查询工作，以便它使用在每一行的第一个查询中输入的 id.假设数据集的顺序也正确.

self.c.executemany("INSERT INTO nodes (node_value, node_group) values (?, (SELECT node_group FROM nodes WHERE node_id = ?)+1)", new_values)#我的问题在这里new_id = self.c.lastrowidconnection_values.append((node_id, new_id))#插入条目self.c.executemany("INSERT INTO connections (parent, child, strength) VALUES (?,?,1)", connection_values)

这些查询曾经是一个 for 循环，但花费的时间太长，所以我试图避免使用 for 循环并单独执行查询.我相信他们可能是将它组合成一个查询的一种方式，但我不确定这将如何完成.

解决方案

您需要一次插入一行或读回 SQLite 的 ID 分配逻辑选择的 rowid；如 SQLite 中的自动增量 中所述，不能保证生成的 ID 将是连续的并尝试在客户端代码中猜测它们是个坏主意.

如果您的程序是单线程的，您可以隐式执行此操作，如下所示:

在表定义中设置 AUTOINCREMENT 关键字.这将保证任何生成的行 ID 将高于当前出现在表中的任何行 ID.

1. 紧接在第一条语句之前，确定表中使用的最高 ROWID.

   oldmax ← Execute("SELECT max(ROWID) from nodes").

2. 像以前一样执行第一次插入.

3. 读回实际使用 select 语句分配的行 ID:

   NewNodes ← Execute("SELECT ROWID FROM nodes WHERE ROWID > ? ORDER BY ROWID ASC", oldmax) .

4. 通过组合来自 new_values 的父 ID 和来自 NewNodes 的子 ID 来构建 connection_values 数组.

5. 像以前一样执行第二次插入.

这可能比您的原始代码快，也可能不快；AUTOINCREMENT 会降低性能，如果不实际进行实验，就无法判断.

如果您的程序从多个线程写入nodes，您需要使用互斥锁保护此算法，因为它根本不适用于多个并发编写器.

I am optimising my code, and reducing the amount of queries. These used to be in a loop but I am trying to restructure my code to be done like this. How do I get the second query working so that it uses the id entered in the first query from each row. Assume that the datasets are in the right order too.

self.c.executemany("INSERT INTO nodes (node_value, node_group) values (?, (SELECT node_group FROM nodes WHERE node_id = ?)+1)", new_values)
#my problem is here
new_id = self.c.lastrowid
connection_values.append((node_id, new_id))
#insert entry
self.c.executemany("INSERT INTO connections (parent, child, strength) VALUES (?,?,1)", connection_values)

These queries used to be a for loop but were taking too long so I am trying to avoid using a for loop and doing the query individually. I believe their might be a way with combining it into one query but I am unsure how this would be done.

解决方案

You will need to either insert rows one at a time or read back the rowids that were picked by SQLite's ID assignment logic; as documented in Autoincrement in SQLite, there is no guarantee that the IDs generated will be consecutive and trying to guess them in client code is a bad idea.

You can do this implicitly if your program is single-threaded as follows:

Set the AUTOINCREMENT keyword in your table definition. This will guarantee that any generated row IDs will be higher than any that appear in the table currently.

1. Immediately before the first statement, determine the highest ROWID in use in the table.

   oldmax ← Execute("SELECT max(ROWID) from nodes").

2. Perform the first insert as before.

3. Read back the row IDs that were actually assigned with a select statement:

   NewNodes ← Execute("SELECT ROWID FROM nodes WHERE ROWID > ? ORDER BY ROWID ASC", oldmax) .

4. Construct the connection_values array by combining the parent ID from new_values and the child ID from NewNodes.

5. Perform the second insert as before.

This may or may not be faster than your original code; AUTOINCREMENT can slow down performance, and without actually doing the experiment there's no way to tell.

If your program is writing to nodes from multiple threads, you'll need to guard this algorithm with a mutex as it will not work at all with multiple concurrent writers.

这篇关于SQLite在使用executemany时获取id并插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！