Get id of previously inserted row

[nk9]

I have two tables that I'd like to insert data into. They are joined by a foreign key. I want to put A into the first table, grab its auto-generated id, and then insert AA, AB, and AC into the second table, all referencing A.

Using vanilla sqlite3, I could use either cursor.lastrowid from Python or last_insert_rowid() in SQL. (See SO.) But in sqlite3worker, I can't figure out how to achieve this. Of course, given all the threads, it is absolutely essential that I grab the most recent row id in the same atomic transaction as the INSERT.

I thought maybe I could put both INSERT statements into a single call to execute(), but that's explicitly not possible. I'm supposed to use executescript() for that, but sqlite3worker doesn't implement this method.

So short of forking and implementing executescript(), or writing the thread safety by hand, is there a recommended way to achieve this task? I was kind of expecting it to be obvious, since this seems like a very common use case. Maybe I'm just missing it.

CC @dashawn888

[dashawn888]

I actual need this feature also. This version of sqlite3worker is out of date and not the official one referenced by pypi. https://pypi.org/project/sqlite3worker/ I plan on working on this issue on my fork that I manage at https://github.com/dashawn888/sqlite3worker The reason this is confusing is I was the maintainer of the original one but it appears to no longer be maintained by the org so I forked it. As far as a current recommended way I don't have a sqlite3worker way of doing this. But I plan on implementing executescript and pushing it to my fork and it will be installable by pip. Thanks for the feature request.

…

On Wed, Apr 29, 2020 at 2:44 PM Nick Kocharhook ***@***.***> wrote: I have two tables that I'd like to insert data into. They are joined by a foreign key. I want to put A into the first table, grab its auto-generated id, and then insert AA, AB, and AC into the second table, all referencing A. Using vanilla sqlite3, I could use either cursor.lastrowid from Python or last_insert_rowid() in SQL. (See SO <https://stackoverflow.com/a/6242813/1749551>.) But in sqlite3worker, I can't figure out how to achieve this. Of course, given all the threads, it is absolutely essential that I grab the most recent row id in the same atomic transaction as the INSERT. I thought I could put both INSERT statements into a single call to execute(), but that's explicitly not possible. I'm supposed to use executescript() for that, but sqlite3worker doesn't implement this method. So short of forking and implementing executescript(), or writing the thread safety by hand, is there a recommended way to achieve this task? I was kind of expecting it to be obvious, since this seems like a very common use case. Maybe I'm just missing it. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#13>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAITY5HXT3AZZY3I57NSK6TRPBYQ3ANCNFSM4MT7CIDA> .

[nk9]

Thanks for the super quick reply!

I opened an issue here because it wasn't possible to do so in the forked repo. I think you have to turn that on.

Glad to hear it's not just me who wants to be able to do this. And thanks for your work on this library. For most simple tasks, it's a damn sight easier than implementing my own queue and writer thread. Bonkers that the Python standard sqlite3 library makes this so difficult.

[nk9]

So, I have now spent a little more time looking into this, and I'm wondering if maybe SQLite has changed over the years. It now supports (compile-time) threading options, one of which allows multiple threads to use the database "without restrictions". My Debian install has this set to 1 by default, which is the "go bananas" Serialized mode.

While it might still be useful to employ sqlite3worker if your sqlite3 install is stuck on single-thread mode, I'm now wondering if maybe I've been over-complicating matters.

That said, there could still be a performance benefit to queueing the writes.

[dashawn888]

Yes. The option of using sqlite3 in single thread mode has been around for a while. Different distros had different defaults for which mode it was compiled in. That was the main reason I wrote it was so I could just know that my app was thread safe at the python layer without worrying about the OS. However, compiling it in serialized mode is a legitimate option although I haven't used that option before in production.

…

On Wed, Apr 29, 2020 at 4:07 PM Nick Kocharhook ***@***.***> wrote: So, I have now spent a little more time looking into this, and I'm wondering if maybe SQLite has changed over the years. It now supports (compile-time) threading options <https://www.sqlite.org/threadsafe.html>, one of which allows multiple threads to use the database "without restrictions". My Debian install has this set to 1 by default, which is the "go bananas" Serialized mode. While it might still be useful to employ sqlite3worker if your sqlite3 install is stuck on single-thread mode, I'm now wondering if maybe I've been over-complicating matters. That said, there could still be a performance benefit to queueing the writes. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#13 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAITY5AOIR3FCLFW5663LWLRPCCGRANCNFSM4MT7CIDA> .

[dashawn888]

Yeah. You are probably correct that opening it up in a new repo is probably a good idea. I simple didn't know about the limitations of forks in the past.

…

On Wed, Apr 29, 2020 at 3:41 PM Nick Kocharhook ***@***.***> wrote: Thanks for the super quick reply. I opened an issue here because it wasn't possible to do so in the forked repo. Since it sounds like this repo is abandonware, maybe it makes sense to create a brand new one where you can track your own issues? It's not possible to search forked repos either, it turns out. Glad to hear it's not just me who wants to be able to do this. And thanks for your work on this library. For most simple tasks, it's a damn sight easier than implementing my own queue and writer thread. Bonkers that the Python standard sqlite3 library makes this so difficult. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#13 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAITY5BTPCXZUM7OYUQG5D3RPB7IHANCNFSM4MT7CIDA> .