Thank you very much. it's very helpful to me.
在 2014年3月14日星期五UTC+8下午11时23分07秒，Michael Bayer写道：
>
>
> On Mar 14, 2014, at 1:28 AM, Yunlong Mao <thr...@gmail.com <javascript:>>
> 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).
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
