Initialize sequence after deleting table, SQLAlchemy , Postresql

Question

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

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
– Joachim Isaksson, Commented Mar 25, 2016 at 9:52
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) postgresql.org/docs/9.5/interactive/sql-truncate.html — joop
– joop, Commented Mar 25, 2016 at 9: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
– Rzozi, Commented Mar 25, 2016 at 9: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
– Rzozi, Commented Mar 25, 2016 at 10:01
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
– univerio, Commented Mar 25, 2016 at 17:08

Danil · Accepted Answer · 2021-06-21 12:37:50Z

12

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()

edited Jun 21, 2021 at 12:37

answered Mar 27, 2016 at 14:57

Danil

5,2611 gold badge42 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Sposito Over a year ago

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: stackoverflow.com/a/5342503/1791525

Et7f3XIV Over a year ago

Can you query the sequence name from sqlachemy ?

Et7f3XIV Over a year ago

in new version you need to wrap with text function

Collectives™ on Stack Overflow

Initialize sequence after deleting table, SQLAlchemy , Postresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related