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]

Post by Yunlong Mao
Hi all,
I have some problem with sqlalchemy and mysql.
"""
__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)
)
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.

Post by Yunlong Mao
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"))

Post by Yunlong Mao
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写道

Post by Yunlong Mao
Hi all,
I have some problem with sqlalchemy and mysql.
"""
__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)
)
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
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.
http://stackoverflow.com/questions/970597/change-auto-increment-starting-number
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).
http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html?highlight=createcolumn#controlling-ddl-sequences
from sqlalchemy.schema import AddConstraint, 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).