1

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.

  • Python3.6
  • SQLAlchemy1.3
  • PostgreSQL10
yixuan
  • 375
  • 2
  • 17

1 Answers1

3

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.

Icebreaker454
  • 1,031
  • 7
  • 12
  • Specify id manually is necessary. So I wonder whether there is a 'setting' in sqlalchemy could do this automatically. I want to try to avoid revising sequence everywhere. – yixuan Apr 09 '21 at 11:04
  • I don't think there is such a setting in SQLA. Your best bet is to alter sequence after doing an "extraordinary" insert. See this SO question for reference as well - https://stackoverflow.com/questions/12338923/sqlalchemy-and-postgresql-how-to-set-primary-key-manually-without-conflicts-in – Icebreaker454 Apr 09 '21 at 11:21