I'm using MySQL and want to use a combined primary-key where the
second column is autogenerated while the first part is an assigned
value. This is how my Table-definition looks like:
table_a = Table('table_a', metadata,
Column('assigned_id', Integer(), primary_key=True,
autoincrement=False),
Column('id', Integer(), primary_key=True, autoincrement=True),
mysql_engine='InnoDB'
)
However, the MySQL-Dialect is not able to generate this table:
OperationalError: (OperationalError) (1075, 'Incorrect table
definition; there can be only one auto column and it must be defined
as a key')
The DDL generated looks like this:
CREATE TABLE table_a (
assigned_id INTEGER NOT NULL,
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (assigned_id, id)
)ENGINE=InnoDB
What's missing here is the explicit key for the autoincremented
column.
The correct code has to look like this:
CREATE TABLE table_a (
assigned_id INTEGER NOT NULL,
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (assigned_id, id),
KEY key_autoinc(id)
)ENGINE=InnoDB
This error only occurs if the autoincremented column is not the first
column of the primary key and innodb is used as storage engine. I
tried to work around this problem by explicitly setting autoincrement
to False and altering the table with an DDL.execute_at()-call. That
worked for the DDL but afterwards the generated id was not propagated
to dependent relations (the docs say autoincrement is only used during
DDL generation but that's not what I experienced). Since this solution
is not very elegant and didn't work for me due to the broken id
propagation, I looked into the MySQL-Dialect of SQLA itself.
I've now fixed the problem locally by modifying the MySQL-Dialect that
comes with SQLA. I copied the visit_create_table method from
DDLCompiler ton the MySQLDDLCompiler class and modified it so that a
separate Key is appended if an autoincremented column exists that is
not the first PK column)
To reproduce the error:
import logging
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
table_a = Table('table_a', metadata,
Column('assigned_id', Integer(), primary_key=True,
autoincrement=False),
Column('id', Integer(), primary_key=True, autoincrement=True),
mysql_engine='InnoDB'
)
# Models
class ItemA(object):
def __init__(self, name):
self.name = name
# Mapping
mapper(ItemA, table_a)
# Init engine
engine = create_engine('mysql://localhost/sqltest', connect_args=
{'user':'root', 'passwd':''}, echo=True)
engine_logger = sqlalchemy.log.instance_logger(engine)
engine_logger.setLevel(logging.DEBUG)
metadata.drop_all(engine)
metadata.create_all(engine)
session_factory = scoped_session(sessionmaker(bind=engine))
session = session_factory()
itemA1 = ItemA(name = 'ItemA1')
session.add(itemA1)
session.commit()
In my modified method, I store the auto_incremented column in a local
variable ("auto_inc_column") and add the KEY-Part if this variable is
set. Most of the code is copy/pasted from the 0.6-version of the
DDLCompiler class, so this fix is probably not very elegant but it
works for me.
Here is the modified version that I currently use:
def visit_create_table(self, create):
table = create.element
preparer = self.dialect.identifier_preparer
text = "\n" + " ".join(['CREATE'] + \
table._prefixes + \
['TABLE',
preparer.format_table(table),
"("])
separator = "\n"
# if only one primary key, specify it along with the column
first_pk = False
auto_inc_column = None
for column in table.columns:
text += separator
separator = ", \n"
text += "\t" + self.get_column_specification(column,
first_pk=column.primary_key and not first_pk)
if column.primary_key and column.autoincrement and
first_pk:
auto_inc_column = column
if column.primary_key:
first_pk = True
const = " ".join(self.process(constraint) for constraint
in column.constraints)
if const:
text += " " + const
# On some DB order is significant: visit PK first, then the
# other constraints (engine.ReflectionTest.testbasic failed on
FB2)
if table.primary_key:
text += ", \n\t" + self.process(table.primary_key)
const = ", \n\t".join(
self.process(constraint) for constraint in
table.constraints
if constraint is not table.primary_key
and constraint.inline_ddl
and (not self.dialect.supports_alter or not
getattr(constraint, 'use_alter', False))
)
if const:
text += ", \n\t" + const
# append KEY for autoincrement column if necessary
if auto_inc_column is not None:
text += ", \n\t" + "KEY `idx_autoinc_%s`(`%s`)" %
(auto_inc_column.name, self.preparer.format_column(auto_inc_column))
text += "\n)%s\n\n" % self.post_create_table(table)
return text
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
