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 CheckCosntraint 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.
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.
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])), )
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
output:
CREATE TABLE a (
id INTEGER NOT NULL,
data VARCHAR,
PRIMARY KEY (id),
CHECK (data IN (1, 2))
)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
