How to increment multiple rows using SqlAlchemy

Question

How to update multiple records with incrementing some field (in my case id)?
I missed one record and the whole table shifted.

How to do that in one transaction or one query? Or what is the fastest way to do that?

Tried something like below, but it is too slow.

rows = session.query(Table).all()
for row in rows:
    row.id = row.id + 1
    session.commit()

Also tried to use something like, but it's not working for me:

session.query(Table)\
    .filter(Table.id == row.id)\
    .update({'id': row.id + 1})

Example of data I have:

 ID   Value 
  1       A
  2       B
  3       C
               < -- D is missing here
  4       E
  5       F

Here, if it would be alphabet D should have ID=4, then I need to increment E and F.

It is not something complicated when you have 5 records, but problem occurs when you have millions or billions of records with this issue.

Answer 1

To be able to insert a document inside sequence,
I manually created new_id column and added it to Table model

new_object_id = 4

session.query(Table) \
    .filter(Table.id < new_object_id)\
    .update({Table.new_id: Table.id})

session.query(Table) \
    .filter(Table.id >= new_object_id)\
    .update({Table.new_id: Table.id + 1})

After these actions, you should be able to see a "hole" in id-sequence.

Then, in separate queries, manually renamed columns id -> old_id and new_id -> id.
Updated newly "created" id column with all attributes (primary key, unique, etc.).
Dropped old_id column.

And finally, inserted the required row.

Total time - few minutes, instead of few days.

Found solution that works for me by myself.
If anybody knows how to optimize manual steps - you are welcome to add comments or edit this answer.