CODEWITHSUNDEEP
mysqlsqlalchemyauto-increment
anthony.theocharis
anthony.theocharis

Reputation: 171

Set AUTO_INCREMENT using SqlAlchemy with MySQL on Columns with non-primary keys?

I can't figure out how to set AUTO_INCREMENT on a UNIQUE column using SqlAlchemy 0.6.0 with MySQL 5.

I know this can be done in MySQL, but I don't want to have to distribute extra .sql scripts in order to set up the databases for my application. I want SqlAlchemy to build the schema from within Python.

As far as I have found so far, there are two ways that a column can become an auto-incrementing type column in SqlAlchemy:

  1. It is the first sqlalchemy.types.Integer column in the table that has primary_key=True and does not have autoincrement=False set in its definition.
    • With MSSQL this adds an INDEX(m,n) property to the column.
    • With Postgres this causes the SERIAL column type to be used.
    • With MySQL adds the AUTO_INCREMENT property to the column.
  2. It is an sqlalchemy.types.Integer column, and is instantiated with an sqlalchemy.schema.Sequence object as an argument.
    • With MSSQL this adds an INDEX(m,n) property to the column.
    • With Postgres this causes the SERIAL column type to be used.
    • With MySQL this seems to get ignored.

So I can't use #1 because the column I want to auto_increment isn't in the primary key (nor should it be). And I can't use #2 because It doesn't work with MySQL.

Basically, my code to define the table looks like the following:

from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Sequence
from sqlalchemy.types import Integer, Unicode
Base = declarative_base()

class Person(Base):
    __tablename__ = "person"
    id = Column(Integer, nullable=False, unique=True)
    first_name = Column(Unicode(100), nullable=False, primary_key=True)
    last_name = Column(Unicode(100), nullable=False, primary_key=True)

And produces this SQL to create the table:

CREATE TABLE person (
    id INTEGER NOT NULL, 
    first_name VARCHAR(100) NOT NULL, 
    last_name VARCHAR(100) NOT NULL, 
    PRIMARY KEY (first_name, last_name), 
    UNIQUE (id)
)

What can I do to get it to produce the following SQL?

CREATE TABLE person (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    first_name VARCHAR(100) NOT NULL, 
    last_name VARCHAR(100) NOT NULL, 
    PRIMARY KEY (first_name, last_name), 
    UNIQUE (id)
)

Upvotes: 17

Views: 11533

Answers (2)

prinzdezibel
prinzdezibel

Reputation: 11147

Excerpt from http://www.mail-archive.com/[email protected]/msg19744.html

Your best bet for now is to issue an ALTER TABLE that applies the AUTO_INCREMENT, if MySQL supports that.

from sqlalchemy.schema import DDL
DDL("ALTER TABLE person ...", on='mysql').execute_at('after-create', 
    person_table)[..]

Upvotes: 6

defines
defines

Reputation: 10524

You might be interested in I need to auto_increment a field in MySQL that is not primary key

I'm not familiar with sqlalchemy, but I do know this can be accomplished in MySQL. You must first define the id column as the PRIMARY KEY and set it AUTO_INCREMENT. Then you can ALTER TABLE and DROP PRIMARY KEY, replacing it with an ADD UNIQUE KEY. This will preserve the AUTO_INCREMENT functionality on that column. You can then create the PRIMARY KEY on the other columns.

To break it down, your first step is to create the table thusly:

CREATE TABLE person (
    id INTEGER NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

Next modify the id key:

ALTER TABLE person DROP PRIMARY KEY, ADD UNIQUE KEY(id);

Finally, add your PRIMARY KEY on the other columns:

ALTER TABLE person ADD PRIMARY KEY(first_name, last_name);

Upvotes: 4

Related Questions