0

Hello I am using SQLAlchemy and Flask-SQLAlchemy, and when I execute an insert with

camera = Camera(name=name, project_id=project.id, url=url)
db.session.add(camera)
db.session.commit()

And fails the insert because fail any constraint like unique name, the following insert Camera's id is jumped one over the previous.

+---------------------+---------------------+----+----------+------------------------+------------+
| created_at          | updated_at          | id | name     | url                    | project_id |
+---------------------+---------------------+----+----------+------------------------+------------+
| 2022-05-11 13:52:21 | 2022-05-11 13:54:44 |  1 | Camera 1 | rtsp://localhost:8554/ |          1 |
| 2022-05-11 13:52:21 | 2022-05-11 13:55:12 |  3 | Camera 2 | rtsp://localhost:8554/ |          1 |
| 2022-05-11 13:52:21 | 2022-05-11 13:55:15 |  4 | Camera 3 | rtsp://localhost:8554/ |          1 |
+---------------------+---------------------+----+----------+------------------------+------------+

As you can see I did one wrong insert between the first and second records.

Why did this happened? How can I solved it?

Tlaloc-ES
  • 4,825
  • 7
  • 38
  • 84
  • Do a check if it exists first, auto increments still increment if you need to do a rollback I believe. – Peter May 11 '22 at 12:10
  • It may depend on the actual db, but generally once an id is allocated it won't be re-used, even if the transaction is rolled back. It's not a problem to be solved, it's expected behaviour. – snakecharmerb May 11 '22 at 12:10
  • 1
    Why do you want to 'solve' it? In what sense is it a problem? The only characteristic of the id that you should really be relying on is that it is unique in the table - but not (for example) that it is contiguous. – Paul May 11 '22 at 12:32

0 Answers0