CODEWITHSUNDEEP
pythonpostgresqlsqlalchemysequenceauto-increment
dopatraman
dopatraman

Reputation: 13908

Primary id sequence not incrementing

I have a table set up like this:

USERS_TABLE = \
        Table("users", META_DATA,
              Column("id", Integer, Sequence("user_id_seq"), primary_key=True),
              Column("first_name", String(255)),
              Column("last_name", String(255))
             )

I am able to insert a single row like this:

INS_EXPRESSION = insert(USERS_TABLE)
SEQ = Sequence('user_id_seq')
INS_EXPRESSION.values(first_name="Foo", last_name="Bar")

But, if I try a second insert, I get this error:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, null, null).
 [SQL: 'INSERT INTO users DEFAULT VALUES']

How do I make the user_id_seq sequence auto-increment?

Upvotes: 2

Views: 1884

Answers (1)

Azat Ibrakov
Azat Ibrakov

Reputation: 10971

Problem is that current version of Table object definition gives us next CREATE TABLE statement

CREATE TABLE users (
    id INTEGER NOT NULL, 
    first_name VARCHAR(255), 
    last_name VARCHAR(255), 
    PRIMARY KEY (id)
)

while it probably should be

  1. this

    CREATE TABLE users (
    id INTEGER DEFAULT nextval('user_id_seq') NOT NULL, 
    first_name VARCHAR(255), 
    last_name VARCHAR(255), 
    PRIMARY KEY (id)
)

  2. or simply this 

    CREATE TABLE users (
    id SERIAL NOT NULL, 
    first_name VARCHAR(255), 
    last_name VARCHAR(255), 
    PRIMARY KEY (id)
)

So for each statement solutions are:

  1. Following this recipe we can set server_default to sequence's nextval like

    from sqlalchemy import (MetaData, Table, Column,
                        Integer, String, Sequence,
                        create_engine,
                        insert)


db_uri = 'postgresql://username:password@hostname:5432/database'
engine = create_engine(db_uri)
META_DATA = MetaData(bind=engine)
user_id_seq = Sequence("user_id_seq", metadata=META_DATA)
USERS_TABLE = Table("users", META_DATA,
                    Column("id", Integer, user_id_seq,
                           server_default=user_id_seq.next_value(),
                           primary_key=True),
                    Column("first_name", String(255)),
                    Column("last_name", String(255)))
USERS_TABLE.create(checkfirst=True)
INS_EXPRESSION = insert(USERS_TABLE).values(first_name="Foo", last_name="Bar")
engine.execute(INS_EXPRESSION)
engine.execute('INSERT INTO users DEFAULT VALUES')

  2. If using Sequence is not crucial than we can remove it and just set autoincrement flag to True (it will also work with Sequence removed without setting flag because

    ... SQLAlchemy will automatically set the first Integer PK column that's not marked as a FK as autoincrement=True...

    but it will be better with it since it is more explicit):

    from sqlalchemy import MetaData, Table, Column, Integer, String, create_engine, insert

db_uri = 'postgresql://username:password@hostname:5432/database'
engine = create_engine(db_uri)
META_DATA = MetaData(bind=engine)

USERS_TABLE = Table("users", META_DATA,
                    Column("id", Integer,
                           autoincrement=True,
                           primary_key=True),
                    Column("first_name", String(255)),
                    Column("last_name", String(255)))
USERS_TABLE.create(checkfirst=True)

INS_EXPRESSION = insert(USERS_TABLE).values(first_name="Foo", last_name="Bar")
engine.execute(INS_EXPRESSION)
engine.execute('INSERT INTO users DEFAULT VALUES')

I will prefer the second one since using Sequence looks like YAGNI.

P. S.

Why are you using UPPER_CASE'd names by the way? These objects doesn't look like constants to me since you can mutate them freely (PEP-8)

Upvotes: 2

Related Questions