7

I am using a Postgres database with sqlalchemy in my Flask API, my database models are defined as follow: 

 class Cache1(db.Model):
  __tablename__ = 'Cache1'
  id = db.Column(db.Integer, Sequence('Cache1_id_seq', start=1, increment=1), primary_key=True)
  name = db.Column(db.String(20))

The problem is when i do Cache1.query.delete() in one of my views and then i try to put new records in Cache1, the sequence number does not start from 1. I should always go to Postgres and do ALTER SEQUENCE "Cache1_id_seq" RESTART WITH 1. Can someone show me a way to do this in Sqlalchemy . Thank you in adcance

Rzozi
  • 127
  • 1
  • 2
  • 9
  • Ok, I realize this may be an annoying question, but _why_ do you need to reset the sequence? I'm assuming you're clearing the cache to reload new data, so entries loaded will not necessarily get the same id as they did before the reload anyway. – Joachim Isaksson Mar 25 '16 at 09:52
  • 1
    You could try `TRUNCATE TABLE xxxxx RESTART IDENTITY;` if that is supported by your ORM. (and take care of foreign keys referring to this table) http://www.postgresql.org/docs/9.5/interactive/sql-truncate.html – joop Mar 25 '16 at 09:52
  • @joop i tried also `TRUNCATE TABLE ... ` but after each delete of the cache table i should do it in postgres. I am looking for a way to restart the identity from sqlalchemy. – Rzozi Mar 25 '16 at 09:55
  • @JoachimIsaksson Yes it is true, but in my case each time i need to store only the names of contents that can be cached at that time ( should not keep past records ) – Rzozi Mar 25 '16 at 10:01
  • 1
    `db.session.execute('ALTER SEQUENCE "Cache1_id_seq" RESTART WITH 1')`? There is usually no good reason you would ever need to reset a sequence, though. Usually it's for something unimportant, like cosmetic reasons. – univerio Mar 25 '16 at 17:08

1 Answers1

11

Use the ALTER SEQUENCE command to modify PostgreSQL sequences. There is no built-in SQLAlchemy function for this, use session.execute to execute the SQL.

db.session.execute("ALTER SEQUENCE Cache1_id_seq RESTART WITH 1")
db.session.commit()
Danil
  • 4,781
  • 1
  • 35
  • 50
  • I appreciate this answer but `test_id_seq` is unclear to me. Would be helper to update your answer to use the OP's table name (`Cache1_id_seq`). More details: https://stackoverflow.com/a/5342503/1791525 – Daniel Sposito Jun 18 '21 at 19:44