9

I want to get the ID of the last inserted record after inserting in postgresql using SQLAlchemy. Here is code,

insert_record = {list of data}
result = connection.execute(tbl_example.insert().returning(tbl_example.c.id), insert_record)
print result.id

The inserting does fine but I can't seem to get the ID of the last inserted, I get the following error,

AttributeError: 'ResultProxy' object has no attribute 'id'

Where is the return ID located in the returning object?

Cœur
  • 37,241
  • 25
  • 195
  • 267
rksh
  • 3,920
  • 10
  • 49
  • 68

1 Answers1

10

It's located directly in returning object. There is example from documentation:

stmt = table.update().\
          where(table.c.data == 'value').\
          values(status='X').\
          returning(table.c.server_flag,
                    table.c.updated_timestamp)

for server_flag, updated_timestamp in connection.execute(stmt):
    print(server_flag, updated_timestamp)

Also, ResultProxy supports accessing via position (as in example) and via name. So, you can use something like row_id = row['id'] where:

for row in connection.execute(stmt):
    row_id = row['id']
    row_id = row.id
    row_id = row[0]
    print(row_id)

Note that not all database backends support this feature. From the documentation:

Note that not all databases/DBAPIs support RETURNING. For those backends with no support, an exception is raised upon compilation and/or execution.

André C. Andersen
  • 8,955
  • 3
  • 53
  • 79
ShabashP
  • 578
  • 2
  • 10
  • 2
    having something like ```row_id = result['id']``` gives me an error like this ```TypeError: 'ResultProxy' object has no attribute '__getitem__'``` – rksh Sep 30 '16 at 09:18
  • This answer is correct, well documented and has a good explanation. It should be set as the correct answer, and should not be downvoted. @rksh, you need to iterate over what `execute(stmt)` returns first. Something like `for result in connection.execute(stmt): row_id = result['id']`. You can also use `row_id = result.id`. – André C. Andersen Jul 06 '19 at 15:32
  • 1
    The only confusion I have with this answer is about the `update()` part. I would expect something with `insert()` like [here](https://stackoverflow.com/a/57758827/5770014) (still +1 on my part) – minus one Jan 20 '22 at 00:55