[Tutor] insert queries into related tables referencing foreign keys using python

[Monte Milanuk]

On 12/24/2011 11:13 PM, Lie Ryan wrote:

> Querying .lastrowid is pretty much safe as long as you don't use a
> single cursor from multiple threads. The .lastrowid attribute belongs to
> a cursor, so write operations from one cursor would not affect the
> .lastrowid of other cursors.
>
> However, note that multiple cursors created from a single connection
> will be able to see each other's changes immediately (as opposed to when
> commited). This might or might not always be desirable.
>
> In sqlite, it is more common to create one **connection** for each
> thread. Creating one connection for each thread prevents concurrency
> problems since each thread will not see uncommitted data from another
> thread.
>
> However, the recommended scenario is to avoid multithreading at all.
> sqlite developers have a strong opinion against multithreading
> (http://www.sqlite.org/faq.html#q6), even though they claimed that
> sqlite is the *embedded* SQL engine with the most concurrency (and it
> does very well in multithreaded scenarios). It is common pattern in
> sqlite-backed applications to have a single thread doing all the writes.
>

Okay... sounds like I should be safe for the most part.  Down the road 
(waaaaay down the road) I had some thoughts of working on an application 
that would in certain situations have multiple users (1-10) and had 
hoped that as long as I kept the sqlite insert/update activity wrapped 
in transactions there wouldn't be much problem with table locks, etc. 
and in this case, confusing lastrowid from one transaction with that 
from another.

By the time I get to where I'm ready/willing/able to write that 
particular app, I might have moved on to an ORM, though.

Thanks,

Monte