> > they were flushed ! the primary key is always fetched when the ORM does > an INSERT. >
I know it *does* happen, just not *how*. It seems that some of my confusion was mostly based entirely on a peculiarity of the sqlite dialect. When I switch to Postgres it is perfectly clear that the ids come back on INSERT through the use of INSERT...RETURNING addresses.id. Sqlite logging has no such clarity in where they come from, so it presume it is a fundamental feature of sqlite's automatic ROWID ( http://www.sqlite.org/autoinc.html). ie: what I considered as "magic" option B, although I don't see it in the sqlite3 docs either. The prefetch of PKs for postrges is great. But when I investigate the other path and follow flush()'s lead of efficiently retrieving PK ids on INSERT (to achieve ORM integration with a minimal set of queries) I still hit two dead ends. It turns out that can't do it in batch with PostgreSQL (outside of the great pre-fetch solution) or with SQLite. With PostgreSQL, I can add a returning clause to the insert to try and get the ids back at the same time as the insert. Unfortunately, this does not work since when you insert multiple rows, the returned list only has the first element populated and the rest are None: new_mail_count = 3 new_emails = ["newjoe%[email protected]" % i for i in range(new_mail_count)] ins_values = [dict(FK_user_id = joe.id, email = addr) for addr in new_emails] insert = Address.__table__.insert(returning = [Address.__table__.c.id, Address.__table__.c.email]) ret = sess.execute(insert, ins_values).fetchall() and if you look at ret, it is: [(13, u'[email protected]'), (None, None), (None, None)] None? I don't know if this is a misunderstanding by me, a limitation of RETURNING, or a bug. For SQLite I'm back to the original problem of not knowing how to get the ids, and there is no "returning" clause there. Argh. I think I need to quit while I'm ahead. The pre-fetch for Postgres solves a lot for me. I still can't bulk insert (without post-query) in SQLite, but I'll live with that or switch to client-side PK generation or something. The rest is mostly just me trying to understand all of this (with a dab of over-optimization) and my frustration will pass. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
