Setting SQLAlchemy autoincrement start value

问题:

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!

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

回答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.

回答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.