CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
yixuan
yixuan

Reputation: 405

sqlalchemy.exc.IntegrityError: duplicate key value violates unique constraint "user_pkey"

Here is my code sample:

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(MY_DB_URI, echo=False)
Session = sessionmaker(bind=engine)

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

db = Session()
db.add(User(id=1, name='one'))
db.commit()

db.add(User(name='two'))

# Here will raise exception:
# sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation)
# duplicate key value violates unique constraint "user_pkey"
# DETAIL:  Key (id)=(1) already exists.
db.commit()

I have tried several approaches such as Sequence, autoincrement etc, but None of these work. And only one way helps is to add following code between two commit():

db.connection().execute("select nextval('user_id_seq'::regclass"))

It will make code messy and ugly, is there any better approach can solve the problem? Native approach in sqlalchemy is the most ideal.

Upvotes: 1

Views: 4336

Answers (1)

Icebreaker454
Icebreaker454

Reputation: 1081

Do not assign the id=1 to your first User instance.

The DB will only update the sequence user_id_seq if a nextval is called. If you point id=1 manually, the sequence in the DB would still hold its initial value, since SQLA won't call nextval to determine the next value of the sequence.

Either remove id=1, or do a ALTER SEQUENCE user_id_seq RESTART WITH 2 after you have placed an id=1 manually.

Upvotes: 3

Related Questions