Setting SQLAlchemy autoincrement start value

Question

The autoincrement argument in SQLAlchemy seems to be only True and False, but I want to set the pre-defined value aid = 1001, the via autoincrement aid = 1002 when the next insert is done.

In SQL, can be changed like:

ALTER TABLE article AUTO_INCREMENT = 1001;

I'm using MySQL and I have tried following, but it doesn't work:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Article(Base):
    __tablename__ = 'article'
    aid = Column(INTEGER(unsigned=True, zerofill=True), 
                autoincrement=1001, primary_key=True)

So, how can I get that? Thanks in advance!

Answer 1

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;")
)

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

You can do it using the mysql_auto_increment table create option. There are mysql_engine and mysql_default_charset options too, which might be also handy:

article = Table(
    'article', metadata,
    Column('aid', INTEGER(unsigned=True, zerofill=True), primary_key=True),
    mysql_engine='InnoDB',
    mysql_default_charset='utf8',
    mysql_auto_increment='1001',
)

The above will generate:

CREATE TABLE article (
    aid INTEGER UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (aid)
)ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

Answer 6

If your database supports Identity columns^*, the starting value can be set like this:

import sqlalchemy as sa

tbl = sa.Table(
    't10494033',
    sa.MetaData(),
    sa.Column('id', sa.Integer, sa.Identity(start=200, always=True), primary_key=True),
)

Resulting in this DDL output:

CREATE TABLE t10494033 (
        id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 200), 
        PRIMARY KEY (id)
)

Identity(..) is ignored if the backend does not support it.

---

^* PostgreSQL 10+, Oracle 12+ and MSSQL, according to the linked documentation above.