On Oct 21, 2011, at 11:05 AM, Russell Warren wrote: > 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.
its cursor.lastrowid: http://docs.python.org/library/sqlite3.html#sqlite3.Cursor.lastrowid this is part of the DBAPI. It's not used with psycopg2 (psycopg2 returns the row's OID which is mostly useless). > 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. that's correct, I mentioned this in my original email > > 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: that's correct > > None? I don't know if this is a misunderstanding by me, a limitation of > RETURNING, or a bug. its a limitation of PG's client library and/or psycopg2. If I recall correctly I really tried to get an answer from them on this one and i think they were pretty much against it. > > For SQLite I'm back to the original problem of not knowing how to get the > ids, and there is no "returning" clause there. you can get one at a time, and that's it. the DBAPI does not support getting back autogen ids with executemany(), nor does RETURNING work reliably in that scenario. -- 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.
