Am 01.04.2014 um 14:58 schrieb Michael Bayer <[email protected]>: > On Apr 1, 2014, at 8:44 AM, Michael Howitz <[email protected]> wrote: > >> Hi, >> >> I want to define a CheckConstraint like this: my_column in ('a', 'b') >> In Alembic I can write: op.create_check_constraint('ck_name', 'table_name', >> sqlalchemy.sql.column('my_column').in_('a', 'b')) >> Using SQLAlchemy I have the problem that the constraint must be defined when >> defining the column (It was ignored when adding the constraint directly to >> the mapped class.): >> The naive approach does not work, because col is not defined in the >> constraint call: >> >> class MyModel(DeclarativeBase): >> col = sqlalchemy.String(CheckConstraint(col.in_('a', 'b'))) >> >> When I try to use sqlalchemy.sql.column("col").in_("a", "b") is rendered as >> "col in (:param1, :param2)". >> >> What is the consistent way to define a CheckConstraint for a list of values? >> Or at least get a fully complied expression with quoted parameters filled in? >> (I do not want to use an Enum because changing the values of the Enum is >> hard.) > > not sure what CheckConstraint inside of sqlalchemy.String is, assuming that’s > a typo. Plus in_() requires a list of arguments, and you can’t refer to > “col” as itself like that, so assuming this is not a real example.
Right it was one of the tries which did not work. Eventually I wrote the whole check constraint as pure SQL, but I thought there must be a solution using SQLAlchemy. > The bound parameters in the CHECK constraint should be rendering as literals, > are you on a recent SQLAlchemy 0.8 or 0.9? this was fixed around version > 0.8.3 or so. I’m using version 0.9. > test case: > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class A(Base): > __tablename__ = 'a' > > id = Column(Integer, primary_key=True) > data = Column(String) > __table_args__ = (CheckConstraint(data.in_([1, 2])), ) Thanks for this test case: __table_args__ is the trick I was missing. Yours sincerely, -- Michael Howitz · [email protected] · software developer gocept gmbh & co. kg · Forsterstraße 29 · 06112 Halle (Saale) · Germany http://gocept.com · Tel +49 345 1229889-8 Python, Pyramid, Plone, Zope · consulting, development, hosting, operations
signature.asc
Description: Message signed with OpenPGP using GPGMail
