Unable to autoincrement SQLAlchemy in MySQL

Question

I'm using MySQL 8.0 and SQLAlchemy. My id column is not increasing and I don't understand why.

SQLAlchemy Model:

class Show(db.Model):
    __tablename__ = "shows"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    type = Column(String)
    status = Column(String)
    episodes = Column(Integer)
    series_entry_id = Column(Integer, ForeignKey("series.id"))
    series_id = Column(Integer, ForeignKey("series.id"))

    lists = relationship("List", secondary=show_list, back_populates="shows")
    recommendations = relationship("Recommendation", backref=backref("shows"))
    user_ratings = relationship("Rating", backref=backref("shows"))
    alt_names = relationship("User", secondary=alt_names, back_populates="alt_show_names")

    series_entry = relationship("Series", foreign_keys=[series_entry_id], uselist=False)
    series = relationship("Series", foreign_keys=[series_id], post_update=True)

Crack code:

show = Show(
        name=new_data["title"]["english"],
        type=new_data["format"],
        status=new_data["status"],
        episodes=new_data["episodes"],
    )

    db.session.add(show)
    db.session.commit()

The original error I received was:

sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 1364 (HY000): 
Field 'id' doesn't have a default value

Based on this answer, I added the index parameter to my id column and edited the my.ini file to get it out of STRICT_TRANS_TABLES mode. The new error is:

sqlalchemy.exc.IntegrityError: (mysql.connector.errors.IntegrityError) 1062 (23000): 
Duplicate entry '0' for key 'shows.PRIMARY'

All the answers I found in this topic refer to AUTO_INCRMENT, but the SQLAlchemy documentation says that this should be the default here because it is an integer primary key and is not specified as false. I did try adding autoincrement=True just in case, but when I try to migrate it, alembic tells me that no changes were detected.

Answer 1

From comments to questions:

No, this is exactly how it works. Specifically, for a model like this

class Account(Base):
    __tablename__ = "account"
    account_number = Column(Integer, primary_key=True)
    customer_name = Column(String(50))

alembic revision --autogenerate will generate

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('account',
    sa.Column('account_number', sa.Integer(), nullable=False),
    sa.Column('customer_name', sa.String(length=50), nullable=True),
    sa.PrimaryKeyConstraint('account_number')
    )

(without explicitly specifying autoincrement=) but when the alembic upgrade head gets SQLAlchemy to actually create the table

issued by SQLAlchemy

CREATE TABLE account (
        account_number INTEGER NOT NULL AUTO_INCREMENT, 
        customer_name VARCHAR(50), 
        PRIMARY KEY (account_number)
)

No. As shown above, Alembic correctly handles AUTO_INCRMENT when the table is first created. What it doesn't detect is that an ORM model with an existing table has a column changed from autoincrement=False to autoincrement=True (or vice versa).

This is known behavior, as shown in the commit message here:

"Please note that this flag does not support changing the "auto-increment" status of a column, as this is not portable across backends."

MySQL does support changing the AUTO_INCRMENT attribute of a column via ALTER_TABLE, so we can do it by changing the "empty" upgrade method

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

to

def upgrade():
    op.alter_column(
        'account',
        'account_number',
        existing_type=sa.Integer(),
        existing_nullable=False,
        autoincrement=True
    )

Rendering

ALTER TABLE account MODIFY account_number INTEGER NOT NULL AUTO_INCREMENT