On Oct 20, 2011, at 4:49 PM, Russell Warren wrote:
>> I understand this somewhat and had done that, but am still confused by one
>> thing: in my pure ORM case (for "joe"), how did the identity map get a hold
>> of the auto-increment ids when there was no apparent emitted SQL? It seems
>> there wasn't one done, although I suppose it could have been done behind the
>> scenes and not logged?
> the examples you showed me on pastebin all seemed to involve the ORM going
> out to fetch rows, so when they are fetched via SELECT all the info is there.
>
> But there was no apparent SELECT! I don't think I expressed myself
> properly... below is a dump of the SQL logging for my pastebin example (some
> prefix cruft removed):
>
> BEGIN (implicit)
> INSERT INTO users (name) VALUES (?)
> ('joe',)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> ('jo...@example.com', 1)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> ('jo...@example.com', 1)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> ('jo...@example.com', 1)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> ('jo...@example.com', 1)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> ('jo...@example.com', 1)
> INSERT INTO users (name) VALUES (?)
> ('bob',)
> INSERT INTO addresses (email, "FK_user_id") VALUES (?, ?)
> (('bo...@example.com', 2), ('bo...@example.com', 2), ('bo...@example.com',
> 2), ('bo...@example.com', 2), ('bo...@example.com', 2))
>
> And this is immediately followed by the "for addr in joe.addresses: print
> addr.id, addr" print loop, which prints:
> 1 <Address('jo...@example.com')>
> 2 <Address('jo...@example.com')>
> 3 <Address('jo...@example.com')>
> 4 <Address('jo...@example.com')>
> 5 <Address('jo...@example.com')>
>
The "joe" addresses are there quite simply because you assigned them on line 44:
joeEmails = [Address("joe%...@example.com" % i) for i in range(numEmails)]
joe.addresses = joeEmails
sess.add(joe)
#flush and see an emitted insert statement per email...
sess.flush()
the comment regarding "NO SELECT !" is against the same "joe" object. the
"addresses" collection hasn't changed, no expiration has occurred either,
therefore no SELECT is needed here:
# we can get all Joe's info without a query... BUT THERE WAS NEVER A SELECT
# EMITTED ANYWHERE ABOVE!
for addr in joe.addresses:
print addr.id, addr
>
> Thanks! Although after really chewing on all of this and trying out
> different things, I'm now content to stay in the ORM and keep away from the
> need for that make_detached function (see below). Your pointing out of the
> batch insert behaviour when pre-assigning PKs with ORM inserts is the big win
> here I think. I confirmed this batch insert ORM behaviour with the following
> simple snippet:
>
> numEmails = 5
> joeEmails = [Address("joe%...@example.com" % i) for i in range(numEmails)]
> #Now manually assign some known unique PK ids in advance...
> # - this is Bad code and is only a cheat for this example
> for i, mail in enumerate(joeEmails, 10000):
> mail.id = i
> joe.addresses = joeEmails
> sess.add(joe)
> #flush and see that all addresses are bulk inserted with a single INSERT...
> sess.flush()
>
> So I never need to leave the ORM, and don't even need to do trickery like
> forcing unnatural transient->detached state transitions (although all of this
> has been extremely informative).
wow what a win huh? the ORM is still slower than the straight SQL due to the
internal bookkeeping but glad this one is working OK.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
