Python/postgres/psycopg2: getting ID of row just inserted

Question

cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]

And please do not build SQL strings containing values manually. You can (and should!) pass values separately, making it unnecessary to escape and SQL injection impossible:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;"
cursor.execute(sql_string, (hundred_name, hundred_slug, status))
hundred = cursor.fetchone()[0]

See the psycopg docs for more details: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

I ended up here because I had a similar problem, but we're using Postgres-XC, which doesn't yet support the RETURNING ID clause. In that case you can use:

cursor.execute('INSERT INTO ........')
cursor.execute('SELECT LASTVAL()')
lastid = cursor.fetchone()['lastval']

Just in case it was useful for anyone!

Consider a RETURNING clause http://www.postgresql.org/docs/8.3/static/sql-insert.html