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

[Lie Ryan]

On 12/25/2011 01:57 AM, Monte Milanuk wrote:
> Lie Ryan<lie.1296<at>  gmail.com>  writes:
>>
>> Be careful that in multithreaded program, each thread should have their
>> own cursors, or otherwise another thread could possibly do another
>> insert before you can query the lastrowid.
>>
>
> okay, this touches on something that had been worrying me a bit... whether
> another insert could hit before I queried for the lastrowid, regardless of how I
> did it.  So you're saying that as long as I have the one cursor open, the
> lastrowid it gets will be the lastrowid from its operations, regardless of other
> commits or transactions that may have happened in the meantime?

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.

>> In general, despite the superficial similarities, most database engine
>> wrappers have their own ways of doing stuffs. Generally, you need a
>> full-blown ORM to smooth out the differences.
>>
>
> So... what would be considered a 'full-blown' ORM?  SQLobject or SQLalchemy...
> or something else?

Most database engine thin-wrappers conforms to the Python Database API 
Specification (PEP249), including sqlite3; despite that these wrappers 
all conforms to a common API, you still need to be familiar with each 
database engine to do a lot of common stuffs and -- except on trivial 
cases -- code written for one PEP249-conforming database engine 
generally cannot be ported to another PEP249-conforming database engine 
without modification. Almost all ORM that supports multiple DB engine 
backends should be able to abstract the differences.