creating blank field and receving the INTEGER PRIMARY KEY with sqlite, python

I am using sqlite with python. When i insert into table A i need to feed it an ID from table B. So what i wanted to do is insert default data into B, grab the id (which is auto increment) and use it in table A. Whats the best way receive the key from the table i just inserted into?

From stackoverflow

• Check out sqlite3_last_insert_rowid() -- it's probably what you're looking for:

  Each entry in an SQLite table has a unique 64-bit signed integer key called the "rowid". The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

  This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. If no successful INSERTs have ever occurred on that database connection, zero is returned.

  Hope it helps! (More info on ROWID is available here and here.)

  Charles Duffy : That will certainly work with APSW, but I'm not sure that the DB-API compliant Python module (pysqlite) provides access to that call.

  Christian Nunciato : In Python it looks like it's wrapped by cursor.lastrowid, yes.

• As Christian said, sqlite3_last_insert_rowid() is what you want... but that's the C level API, and you're using the Python DB-API bindings for SQLite.

  It looks like the cursor method lastrowid will do what you want (search for 'lastrowid' in the documentation for more information). Insert your row with cursor.execute( ... ), then do something like lastid = cursor.lastrowid to check the last ID inserted.

  That you say you need "an" ID worries me, though... it doesn't matter which ID you have? Unless you are using the data just inserted into B for something, in which case you need that row ID, your database structure is seriously screwed up if you just need any old row ID for table B.

  Charles Duffy : One thing which could probably be more clear -- lastrowid gives you the row ID, *not* the autoincrementing primary key; an extra query should be run to retrieve that key.

  Charles Duffy : Actually, that last objection I had was wrong; see http://www.sqlite.org/autoinc.html

  kquinn : Yeah, SQLite is a quirky little database... it's edge cases like this that really make me appreciate features like `INSERT... RETURNING` in Postgres.

• Simply use:

  SELECT last_insert_rowid();
  

  However, if you have multiple connections writing to the database, you might not get back the key that you expect.