On Mar 14, 2014, at 1:28 AM, Yunlong Mao <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
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)
