Setting SQLAlchemy autoincrement start value

I couldn't get the other answers to work using mysql and flask-migrate so I did the following inside a migration file.

from app import db
db.engine.execute("ALTER TABLE myDB.myTable AUTO_INCREMENT = 2000;")

Be warned that if you regenerated your migration files this will get overwritten.

I know this is an old question but I recently had to figure this out and none of the available answer were quite what I needed. The solution I found relied on Sequence in SQLAlchemy. For whatever reason, I could not get it to work when I called the Sequence constructor within the Column constructor as has been referenced above. As a note, I am using PostgreSQL.

For your answer I would have put it as such:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Sequence, Column, Integer

import os
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Sequence, Integer, create_engine
Base = declarative_base()

def connection():
    engine = create_engine(f"postgresql://postgres:{os.getenv('PGPASSWORD')}@localhost:{os.getenv('PGPORT')}/test")
    return engine

engine = connection()

class Article(Base):
    __tablename__ = 'article'
    seq = Sequence('article_aid_seq', start=1001)
    aid = Column('aid', Integer, seq, server_default=seq.next_value(), primary_key=True)

Base.metadata.create_all(engine)

This then can be called in PostgreSQL with:

insert into article (aid) values (DEFAULT);
select * from article;

 aid  
------
 1001
(1 row)

Hope this helps someone as it took me a while

According to the docs:

autoincrement – This flag may be set to False to indicate an integer primary key column that should not be considered to be the “autoincrement” column, that is the integer primary key column which generates values implicitly upon INSERT and whose value is usually returned via the DBAPI cursor.lastrowid attribute. It defaults to True to satisfy the common use case of a table with a single integer primary key column.

So, autoincrement is only a flag to let SQLAlchemy know whether it's the primary key you want to increment.

What you're trying to do is to create a custom autoincrement sequence.

So, your example, I think, should look something like:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Sequence

Base = declarative_base()

class Article(Base):
    __tablename__ = 'article'
    aid = Column(INTEGER(unsigned=True, zerofill=True), 
                 Sequence('article_aid_seq', start=1001, increment=1),   
                 primary_key=True)

Note, I don't know whether you're using PostgreSQL or not, so you should make note of the following if you are:

The Sequence object also implements special functionality to accommodate Postgresql’s SERIAL datatype. The SERIAL type in PG automatically generates a sequence that is used implicitly during inserts. This means that if a Table object defines a Sequence on its primary key column so that it works with Oracle and Firebird, the Sequence would get in the way of the “implicit” sequence that PG would normally use. For this use case, add the flag optional=True to the Sequence object - this indicates that the Sequence should only be used if the database provides no other option for generating primary key identifiers.

You can achieve this by using DDLEvents. This will allow you to run additional SQL statements just after the CREATE TABLE ran. Look at the examples in the link, but I am guessing your code will look similar to below:

from sqlalchemy import event
from sqlalchemy import DDL
event.listen(
    Article.__table__,
    "after_create",
    DDL("ALTER TABLE %(table)s AUTO_INCREMENT = 1001;")
)