SQLAlchemy: CheckConstraint with string function

Question

Trying to use sqlalchemy.schema.CheckConstraint in this way:

themes2tags_table = Table('themes2tags', Base.metadata,
 Column('theme_id', String(32), ForeignKey('tags.id')),
 Column('tag_id', String(32), ForeignKey('tags.id')),
 PrimaryKeyConstraint(['theme_id', 'tag_id']),
 CheckConstraint("substr(theme_id,1,1)='3'"))

(meant many-to-many self-referential relationship over tags table and that themes are tags with predefined prefix, obviously), what ends up with

 ...CheckConstraint("substr(theme_id,1,1)='3'")
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 303, in __new__
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 370, in _init
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 64, in _init_items
 File "build\bdist.win32\egg\sqlalchemy\events.py", line 234, in _set_parent_wi
th_dispatch
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 2133, in _set_parent
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 1909, in _set_parent
 File "build\bdist.win32\egg\sqlalchemy\schema.py", line 1883, in _set_parent
 File "build\bdist.win32\egg\sqlalchemy\sql\expression.py", line 2213, in add
AttributeError: 'list' object has no attribute 'key'

Where am I wrong here? Is it possible to have such a constraint in these circumstances?

Current backend is sqlite, but I consider migrating to Postgres in the future, so looking for a more-less generic receipt.

Upd: sqlalchemy 0.7.5

• Martijn Pieters: What version of SQLAlchemy is this? This is not 0.7.8, in any case.

Answer 1

Well, that was my fault, in fact, based on wrong error message, however. As it turned out, the actual error was on the previous line, i.e. the correct syntax is:

themes2tags_table = Table('themes2tags', Base.metadata,
 Column('theme_id', String(32), ForeignKey('tags.id')),
 Column('tag_id', String(32), ForeignKey('tags.id')),
 PrimaryKeyConstraint(*['theme_id', 'tag_id']),
 CheckConstraint("substr(theme_id,1,1)='3'"))

Pay attention to that asterisk before list definition in PrimaryKeyConstraint.

• Wichert Akkerman: A simpler way to write that would be PrimaryKeyConstraint('theme_id', 'tag_id').