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
– Martijn Pieters, Commented Jul 31, 2012 at 9:03

Alexey Khoroshikh · Accepted Answer · 2012-08-01 07:48:46Z

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.

answered Aug 1, 2012 at 7:48

Alexey Khoroshikh

4354 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wichert Akkerman Over a year ago

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

Collectives™ on Stack Overflow

SQLAlchemy: CheckConstraint with string function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related