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

What version of SQLAlchemy is this? This is not 0.7.8, in any case. — Martijn Pieters, Jul 31 '12 at 09:03

score 1 · Answer 1 · answered Aug 01 '12 at 07:48

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.

A simpler way to write that would be PrimaryKeyConstraint('theme_id', 'tag_id'). — Wichert Akkerman, Aug 01 '12 at 08:01

SQLAlchemy: CheckConstraint with string function

1 Answers1