SQLAlchemy with MySQL won't auto increment

Question

I'm using MySQL 8.0 and SQLAlchemy. My id column isn't incrementing, 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)

Breaking 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

From this answer, I added the index parameter to my id column and edited the my.ini file to take 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 answers I've found on the topic talk about AUTO_INCREMENT, but the SQLAlchemy docs say that that should be the default here, given that it's an integer primary key without it specified to false. I did try adding autoincrement=True just in case, but when I tried to migrate it alembic told me that no changes were detected.

Can you try using [PyMySQL](https://pypi.org/project/PyMySQL/) instead of MySQL Connector/Python and see if that works any better? — Gord Thompson, Jun 10 '22 at 19:12
You don't needd `index=True` for a primary key. Try removing that. — Barmar, Jun 10 '22 at 19:45
Check the output from `SHOW CREATE TABLE shows` to verify that the `id` column really is `AUTO_INCREMENT` in the database itself. (I'm betting it's not.) — Gord Thompson, Jun 10 '22 at 19:49
@Barmar: I removed the parameter, but received the same error. @GordThompson: You're correct, it wasn't. I've altered it directly in the database and it's solved my issue, so thank you! But does this mean that SQLAlchemy is wrong and it isn't set by default? Since alembic didn't detect any changes when I set `autoincrement=True`, does that mean that for every table I make, I'll have to set `AUTO_INCREMENT` in the database manually? — Superstars111, Jun 10 '22 at 20:24
SqlAlchemy is supposed to do this automatically for the first primary key column. I'm not sure why it's not working for you. — Barmar, Jun 10 '22 at 20:25

score 2 · Answer 1 · answered Jun 11 '22 at 14:25

From comments to the question:

does this mean that SQLAlchemy is wrong and [AUTO_INCREMENT] isn't set by default [for the first integer primary key column]?

No, that is indeed how it works. Specifically, for a model like

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')
    )

(which doesn't explicitly specify autoincrement=) but when alembic upgrade head gets SQLAlchemy to actually create the table SQLAlchemy emits

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

Since alembic didn't detect any changes when I set autoincrement=True, does that mean that for every table I make, I'll have to set AUTO_INCREMENT in the database manually?

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

This is known behaviour, as indicated by the commit message here:

"Note that this flag does not support alteration of a column's "autoincrement" status, as this is not portable across backends."

MySQL does support changing the AUTO_INCREMENT property of a column via ALTER_TABLE, so we could achieve that 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
    )

which renders

ALTER TABLE account MODIFY account_number INTEGER NOT NULL AUTO_INCREMENT

SQLAlchemy with MySQL won't auto increment

1 Answers1