Defining CheckConstraint if column value is in list of values

[Michael Howitz]

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.)

Thanks in advance,

Michael Howitz

[Michael Bayer]

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))

)

[Michael Howitz]

Am 01.04.2014 um 14:58 schrieb Michael Bayer <mik...@zzzcomputing.com>:
> On Apr 1, 2014, at 8:44 AM, Michael Howitz <icem...@gmail.com> 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 · m...@gocept.com · 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