On Mar 14, 2014, at 1:28 AM, Yunlong Mao <thrb...@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).
-- 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.
from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, autoincrement=True, nullable=False, unique=True, index=True, info={"mysql_autoincrement": True} ) coreid = Column(String(32), primary_key=True) class Role(Base): __tablename__ = 'role' id = Column(Integer, autoincrement=True, nullable=False, unique=True, index=True, info={"mysql_autoincrement": True} ) coreid = Column(String(32), primary_key=True) u_r_association = Table('user_role', Base.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) ) e = create_engine("mysql://scott:tiger@localhost/test", echo=True) """ 1. the autoincrement is invalid, i can't find it after create sql by sqlachemy. A: please read http://docs.sqlalchemy.org/en/rel_0_9/dialects/mysql.html#auto-increment-behavior. "SQLAlchemy will automatically set AUTO_INCREMENT on the first Integer **primary key** " column. this column is not a primary key. MySQL will also not allow this syntax. However, to achieve this DDL as a building block for perhaps some variant of this, we have to add a custom compilation rule using CreateColumn: http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html?highlight=createcolumn#sqlalchemy.schema.CreateColumn. "autoincrement" is always true, so we can't use that by itself so we'll use our own flag in info. """ from sqlalchemy.ext.compiler import compiles from sqlalchemy.schema import CreateColumn @compiles(CreateColumn, "mysql") def add_autoinc(element, compiler, **kw): text = compiler.visit_create_column(element, **kw) if "mysql_autoincrement" in element.element.info: text += " AUTO_INCREMENT" return text from sqlalchemy.schema import CreateTable print(CreateTable(User.__table__).compile(e)) """ 2. how can i set the autoincrement with a initialize value. A: use ALTER TABLE: http://stackoverflow.com/questions/970597/change-auto-increment-starting-number we use DDL for this: http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html?#sqlalchemy.schema.DDL """ 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. A: We can use conditional DDL here: 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) """this fails because AUTO_INCREMENT must be a primary key. """ Base.metadata.create_all(e)