python - SQLite get id and insert when using executemany -

i optimising code, , reducing amount of queries. these used in loop trying restructure code done this. how second query working uses id entered in first query each row. assume datasets in right order too.

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

these queries used loop taking long trying avoid using loop , doing query individually. believe might way combining 1 query unsure how done.

you need either insert rows 1 @ time or read rowids picked sqlite's id assignment logic; documented in autoincrement in sqlite, there no guarantee ids generated consecutive , trying guess them in client code bad idea.

you can implicitly if program single-threaded follows:

set autoincrement keyword in table definition. guarantee generated row ids higher appear in table currently.

1. immediately before first statement, determine highest rowid in use in table.

   oldmax ← execute("select max(rowid) nodes").

2. perform first insert before.

3. read row ids assigned select statement:

   newnodes ← execute("select rowid nodes rowid > ? order rowid asc", oldmax) .

4. construct connection_values array combining parent id new_values , child id newnodes.

5. perform second insert before.

this may or may not faster original code; autoincrement can slow down performance, , without doing experiment there's no way tell.

if program writing nodes multiple threads, you'll need guard algorithm mutex not work @ multiple concurrent writers.