From 832067458dd92dd6543e7f3850bb447a09d895fa Mon Sep 17 00:00:00 2001 From: Chris Davies Date: Thu, 18 Jul 2013 20:38:45 +0000 Subject: When running the alchemy scaffold on MySQL an error is generated on table creation: sqlalchemy.exc.OperationalError: (OperationalError) (1170, "BLOB/TEXT column 'name' used in key specification without a key length") '\nCREATE TABLE models (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tname TEXT, \n\tvalue INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (name)\n)\n\n' () MySQL (and MariaDB) doesn't allow an index of more than 255 characters. After modifying the scaffold and creating a project, the following results: > show create table models; +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | models | CREATE TABLE `models` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text, `value` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `my_index` (`name`(255)) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 | +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ A unique index is created on the name field, constrained to 255 characters. When run on SQLite3: sqlite> .schema models CREATE TABLE models ( id INTEGER NOT NULL, name TEXT, value INTEGER, PRIMARY KEY (id) ); CREATE UNIQUE INDEX my_index ON models (name); the mysql specific constraint is ignored and the table is created with a unique index as desired. --- pyramid/scaffolds/alchemy/+package+/models.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/pyramid/scaffolds/alchemy/+package+/models.py b/pyramid/scaffolds/alchemy/+package+/models.py index aeeb9df64..db1fee832 100644 --- a/pyramid/scaffolds/alchemy/+package+/models.py +++ b/pyramid/scaffolds/alchemy/+package+/models.py @@ -1,5 +1,6 @@ from sqlalchemy import ( Column, + Index, Integer, Text, ) @@ -20,9 +21,11 @@ Base = declarative_base() class MyModel(Base): __tablename__ = 'models' id = Column(Integer, primary_key=True) - name = Column(Text, unique=True) + name = Column(Text) value = Column(Integer) def __init__(self, name, value): self.name = name self.value = value + +Index('my_index', MyModel.name, unique=True, mysql_length=255) -- cgit v1.2.3