sqlalchemy with autoincrement and foreignkey

[Yunlong Mao]

Hi all,

I have some problem with sqlalchemy and mysql.

"""

class User(db.Model, UserMixin):

    __tablename__ = 'user'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True, index=True)

    coreid = Column(String(32), primary_key=True)

"""

u_r_association = Table('user_role', db.metadata,

    Column('id', Integer, primary_key=True),

    Column('user_id', Integer, ForeignKey('user.id'), nullable=False),

    Column('role_id', Integer, ForeignKey('role.id'), nullable=False)

)

I create model and association table like this, my problems:

1. the autoincrement is invalid, i can't find it after create sql by sqlachemy.

2. how can i set the autoincrement with a initialize value.

3. how can i not generate the real foreignkey in databases, only leave it in model config.

thanks.

[Michael Bayer]

On Mar 14, 2014, at 1:28 AM, Yunlong Mao <thr...@gmail.com> wrote:

Hi all,

I have some problem with sqlalchemy and mysql.

"""

class User(db.Model, UserMixin):

    __tablename__ = 'user'

    id = Column(Integer, autoincrement=True, nullable=False, unique=True, index=True)

    coreid = Column(String(32), primary_key=True)

"""

u_r_association = Table('user_role', db.metadata,

    Column('id', Integer, primary_key=True),

    Column('user_id', Integer, ForeignKey('user.id'), nullable=False),

    Column('role_id', Integer, ForeignKey('role.id'), nullable=False)

)

I create model and association table like this, my problems:

1. the autoincrement is invalid, i can't find it after create sql by sqlachemy.

this is because it is configured incorrectly;  the auto increment column must be a primary key column.     this is not just SQLAlchemy’s behavior but is also a limitation of MySQL.  Note the following exception is returned by the database directly:

CREATE TABLE user (

id INTEGER NOT NULL AUTO_INCREMENT, 

coreid VARCHAR(32) NOT NULL, 

PRIMARY KEY (coreid)

)

 File "build/bdist.macosx-10.4-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler

sqlalchemy.exc.OperationalError: (OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key') '\nCREATE TABLE user (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcoreid VARCHAR(32) NOT NULL, \n\tPRIMARY KEY (coreid)\n)\n\n' ()

see http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html.

The attached script illustrates how to hack SQLAlchemy to render DDL like the above however it isn’t accepted by the database.

2. how can i set the autoincrement with a initialize value.

use ALTER TABLE:

http://stackoverflow.com/questions/970597/change-auto-increment-starting-number

SQLAlchemy provides the DDL construct:  http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html?#sqlalchemy.schema.DDL

e.g.

from sqlalchemy import event, DDL

event.listen(User.__table__, "after_create", DDL("ALTER TABLE user AUTO_INCREMENT = 5"))

3. how can i not generate the real foreignkey in databases, only leave it in model config.

I don’t know why you’d need this as if you just use MyISAM in MySQL, foreign keys are ignored in any case, and you don’t need ForeignKey anyway unless you are looking for joins to generate themselves (which could be handy).

Again, the DDL support allows this: http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html?highlight=createcolumn#controlling-ddl-sequences

from sqlalchemy.schema import AddConstraint, ForeignKeyConstraint

for table in Base.metadata.tables.values():

    for constraint in table.constraints:

        if isinstance(constraint, ForeignKeyConstraint):

            AddConstraint(constraint).execute_if(callable_ = lambda *args: False)

the attached script demonstrates all three techniques (but fails unless you disable the AUTO_INCREMENT hack).

[Yunlong Mao]

Thank you very much. it's very helpful to me.

在 2014年3月14日星期五UTC+8下午11时23分07秒，Michael Bayer写道：