Python sqlalchemy.CheckConstraint() Examples

def _version_table_schema(metadata):
    # NOTE: When modifying this schema, update the ASSET_DB_VERSION value
    return sa.Table(
        'version_info',
        metadata,
        sa.Column(
            'id',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ),
        sa.Column(
            'version',
            sa.Integer,
            unique=True,
            nullable=False,
        ),
        # This constraint ensures a single entry in this table
        sa.CheckConstraint('id <= 1'),
    ) 

def test_noindexes_table(metadata):
    simple_items = Table("simple_items", metadata, Column("number", INTEGER), CheckConstraint("number > 2"))
    simple_items.indexes.add(Index("idx_number", simple_items.c.number))

    assert (
        generate_code(metadata, noindexes=True)
        == """\
# coding: utf-8
from sqlalchemy import CheckConstraint, Column, Integer, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('number', Integer),
    CheckConstraint('number > 2')
)
"""
    ) 

def test_to_metadata_ok(self):
        m = MetaData()

        t = Table("tbl", m, Column("a", Integer), Column("b", Integer))

        t2 = Table("t2", m, Column("a", Integer), Column("b", Integer))

        UniqueConstraint(t.c.a)
        CheckConstraint(t.c.a > 5)
        ForeignKeyConstraint([t.c.a], [t2.c.a])
        PrimaryKeyConstraint(t.c.a)

        m2 = MetaData()

        t3 = t.to_metadata(m2)

        eq_(len(t3.constraints), 4)

        for c in t3.constraints:
            assert c.table is t3 

def test_schema_boolean(metadata):
    Table(
        "simple_items", metadata, Column("bool1", INTEGER), CheckConstraint("testschema.simple_items.bool1 IN (0, 1)"), schema="testschema"
    )

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Boolean, Column, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('bool1', Boolean),
    schema='testschema'
)
"""
    ) 

def test_auto_append_constraint(self):
        m = MetaData()

        t = Table("tbl", m, Column("a", Integer), Column("b", Integer))

        t2 = Table("t2", m, Column("a", Integer), Column("b", Integer))

        for c in (
            UniqueConstraint(t.c.a),
            CheckConstraint(t.c.a > 5),
            ForeignKeyConstraint([t.c.a], [t2.c.a]),
            PrimaryKeyConstraint(t.c.a),
        ):
            assert c in t.constraints
            t.append_constraint(c)
            assert c in t.constraints

        c = Index("foo", t.c.a)
        assert c in t.indexes 

def test_enum_detection(metadata):
    Table("simple_items", metadata, Column("enum", VARCHAR(255)), CheckConstraint(r"simple_items.enum IN ('A', '\'B', 'C')"))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, Enum, MetaData, Table

metadata = MetaData()


t_simple_items = Table(
    'simple_items', metadata,
    Column('enum', Enum('A', "\\\\'B", 'C'))
)
"""
    ) 

def render_constraint(self, constraint):
        def render_fk_options(*opts):
            opts = [repr(opt) for opt in opts]
            for attr in "ondelete", "onupdate", "deferrable", "initially", "match":
                value = getattr(constraint, attr, None)
                if value:
                    opts.append("{0}={1!r}".format(attr, value))

            return ", ".join(opts)

        if isinstance(constraint, ForeignKey):
            remote_column = "{0}.{1}".format(constraint.column.table.fullname, constraint.column.name)
            return "ForeignKey({0})".format(render_fk_options(remote_column))
        elif isinstance(constraint, ForeignKeyConstraint):
            local_columns = _get_column_names(constraint)
            remote_columns = ["{0}.{1}".format(fk.column.table.fullname, fk.column.name) for fk in constraint.elements]
            return "ForeignKeyConstraint({0})".format(render_fk_options(local_columns, remote_columns))
        elif isinstance(constraint, CheckConstraint):
            return "CheckConstraint({0!r})".format(self._get_compiled_expression(constraint.sqltext))
        elif isinstance(constraint, UniqueConstraint):
            columns = [repr(col.name) for col in constraint.columns]
            return "UniqueConstraint({0})".format(", ".join(columns)) 

def test_add_check_constraint_already_named_from_schema(self):
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint("im a constraint", name="cc1")
        Table("t", m1, Column("x"), ck)

        context = op_fixture(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )

        op.create_table("some_table", Column("x", Integer, ck))
        context.assert_(
            "CREATE TABLE some_table "
            "(x INTEGER CONSTRAINT ck_t_cc1 CHECK (im a constraint))"
        ) 

def test_check_constraint_copy(self):
        def r(x):
            return x

        c = CheckConstraint(
            "foo bar",
            name="name",
            initially=True,
            deferrable=True,
            _create_rule=r,
        )
        c2 = c.copy()
        eq_(c2.name, "name")
        eq_(str(c2.sqltext), "foo bar")
        eq_(c2.initially, True)
        eq_(c2.deferrable, True)
        assert c2._create_rule is r 

def test_rename_col_boolean(self):
        impl = self._boolean_fixture()
        impl.alter_column("tname", "flag", name="bflag")
        new_table = self._assert_impl(
            impl,
            ddl_contains="CHECK (bflag IN (0, 1)",
            colnames=["id", "flag"],
        )
        eq_(new_table.c.flag.name, "bflag")
        eq_(
            len(
                [
                    const
                    for const in new_table.constraints
                    if isinstance(const, CheckConstraint)
                ]
            ),
            1,
        ) 

def test_rename_col_enum(self):
        impl = self._enum_fixture()
        impl.alter_column("tname", "thing", name="thang")
        new_table = self._assert_impl(
            impl,
            ddl_contains="CHECK (thang IN ('a', 'b', 'c')",
            colnames=["id", "thing"],
        )
        eq_(new_table.c.thing.name, "thang")
        eq_(
            len(
                [
                    const
                    for const in new_table.constraints
                    if isinstance(const, CheckConstraint)
                ]
            ),
            1,
        ) 

def test_render_ck_constraint_external(self):
        t, t2 = self._constraint_create_fixture()

        constraint = CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        self.assert_compile(
            schema.AddConstraint(constraint),
            "ALTER TABLE tbl ADD CONSTRAINT my_test_constraint "
            "CHECK (a < b) DEFERRABLE INITIALLY DEFERRED",
        ) 

def test_auto_append_ck_on_col_attach_two(self):
        m = MetaData()

        a = Column("a", Integer)
        b = Column("b", Integer)
        c = Column("c", Integer)
        ck = CheckConstraint(a > b + c)

        t = Table("tbl", m, a)
        assert ck not in t.constraints

        t.append_column(b)
        assert ck not in t.constraints

        t.append_column(c)
        assert ck in t.constraints 

def test_detect_boolean_deleted_constraint_detection(self):
        table_name = 'abc'
        table = Table(table_name, self.meta,
                      Column('id', Integer, primary_key=True),
                      Column('deleted', Boolean(create_constraint=True)))
        ck = [
            const for const in table.constraints if
            isinstance(const, CheckConstraint)][0]

        self.assertTrue(utils._is_deleted_column_constraint(ck))

        self.assertFalse(
            utils._is_deleted_column_constraint(
                CheckConstraint("deleted > 5")
            )
        ) 

def languages_constraint(column_name, languages_column_name) -> sa.Constraint:
    """CHECK CONSTRAINT for a translatable column.

    Checks that all of the languages in the languages column exist as keys in
    the translatable column.

    :param column_name: the name of the translatable column
    :param languages_column_name: the name of the TEXT[] column containing the
                                  languages.
    :return: a SQLAlchemy Constraint to ensure that all the required
             translations are available.
    """
    return sa.CheckConstraint(
        "{} ?& {}".format(column_name, languages_column_name),
        name='all_{}_languages_present_in_{}'.format(
            column_name, languages_column_name
        ),
    ) 

def languages_column(column_name) -> sa.Column:
    """A TEXT[] column of length > 0.

    Return an ARRAY(TEXT, as_tuple=True) column.

    :param column_name: the name of the column
    :returns: a SQLAlchemy Column for a non-null ARRAY(TEXT, as_tuple=True)
              type.
    """
    return sa.Column(
        pg.ARRAY(pg.TEXT, as_tuple=True),
        sa.CheckConstraint(
            'COALESCE(ARRAY_LENGTH({}, 1), 0) > 0'.format(column_name)
        ),
        nullable=False,
        default=['English'],
    ) 

def json_column(column_name: str, *, default=None) -> sa.Column:
    """A JSONB column.

    Return a column of type JSONB for use in models. Use this for entries like

        <language>: <text>

    :param column_name: the name of the column
    :param default: the column default (default value None, meaning no column
                    default)
    :return: a SQLAlchemy Column for a non-null JSONB type.
    """
    return sa.Column(
        pg.json.JSONB,
        sa.CheckConstraint(
            "{} @> '{{}}'".format(column_name),
            name='{}_valid_json_check'.format(column_name),
        ),
        nullable=False,
        server_default=default,
    ) 

def test_check_constraint_reflection(self):
        m1 = self.metadata
        Table(
            "x",
            m1,
            Column("q", Integer),
            sa.CheckConstraint("q > 10", name="ck1"),
        )
        m1.create_all()
        m2 = MetaData(testing.db)
        t2 = Table("x", m2, autoload=True)

        ck = [
            const
            for const in t2.constraints
            if isinstance(const, sa.CheckConstraint)
        ][0]

        eq_regex(ck.sqltext.text, r"[\(`]*q[\)`]* > 10")
        eq_(ck.name, "ck1") 

def test_render_check_constraint_renamed(self):
        """test that constraints from autogenerate render with
        the naming convention name explicitly.  These names should
        be frozen into the migration scripts so that they remain
        the same if the application's naming convention changes.

        However, op.create_table() and others need to be careful that
        these don't double up when the "%(constraint_name)s" token is
        used.

        """
        m1 = MetaData(
            naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"}
        )
        ck = CheckConstraint("im a constraint", name="cc1")
        Table("t", m1, Column("x"), ck)

        eq_ignore_whitespace(
            autogenerate.render._render_check_constraint(
                ck, self.autogen_context
            ),
            "sa.CheckConstraint(!U'im a constraint', name=op.f('ck_t_cc1'))",
        ) 

def test_deferrable_column_check(self):
        t = Table(
            "tbl",
            MetaData(),
            Column("a", Integer),
            Column(
                "b",
                Integer,
                CheckConstraint(
                    "a < b", deferrable=True, initially="DEFERRED"
                ),
            ),
        )

        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl (a INTEGER, b INTEGER CHECK (a < b) "
            "DEFERRABLE INITIALLY DEFERRED)",
        ) 

def test_render_ck_constraint_inline(self):
        t, t2 = self._constraint_create_fixture()

        CheckConstraint(
            "a < b",
            name="my_test_constraint",
            deferrable=True,
            initially="DEFERRED",
            table=t,
        )

        # before we create an AddConstraint,
        # the CONSTRAINT comes out inline
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl ("
            "a INTEGER, "
            "b INTEGER, "
            "CONSTRAINT my_test_constraint CHECK (a < b) "
            "DEFERRABLE INITIALLY DEFERRED"
            ")",
        ) 

def test_rename_col_sql_ck(self):
        impl = self._sql_ck_fixture()

        impl.alter_column("tname", "email", name="emol")
        new_table = self._assert_impl(
            impl,
            ddl_contains="CHECK (emol LIKE '%@%')",
            colnames=["id", "email"],
        )
        eq_(
            len(
                [
                    c
                    for c in new_table.constraints
                    if isinstance(c, CheckConstraint)
                ]
            ),
            1,
        )

        eq_(new_table.c.email.name, "emol") 

def test_drop_constraint_mariadb(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        dialect.server_version_info = (10, 1, 1, "MariaDB")
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CONSTRAINT `%s`"
            % (table_name, constraint_name),
            dialect=dialect,
        ) 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # op.create_table('spatial_ref_sys',
    # sa.Column('srid', sa.INTEGER(), autoincrement=False, nullable=False),
    # sa.Column('auth_name', sa.VARCHAR(length=256), autoincrement=False, nullable=True),
    # sa.Column('auth_srid', sa.INTEGER(), autoincrement=False, nullable=True),
    # sa.Column('srtext', sa.VARCHAR(length=2048), autoincrement=False, nullable=True),
    # sa.Column('proj4text', sa.VARCHAR(length=2048), autoincrement=False, nullable=True),
    # sa.CheckConstraint(u'(srid > 0) AND (srid <= 998999)', name=u'spatial_ref_sys_srid_check'),
    # sa.PrimaryKeyConstraint('srid', name=u'spatial_ref_sys_pkey')
    # )
    op.drop_table('resource_tags')
    op.drop_table('tag')
    # ### end Alembic commands ### 

def test_column_level_ck_name(self):
        t = Table(
            "tbl",
            MetaData(),
            Column(
                "a",
                Integer,
                CheckConstraint("a > 5", name="ck_a_greater_five"),
            ),
        )
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE tbl (a INTEGER CONSTRAINT "
            "ck_a_greater_five CHECK (a > 5))",
        ) 

def test_drop_constraint_mysql(self):
        m = MetaData()
        table_name = "testtbl"
        constraint_name = "constraint"
        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
        Table(table_name, m, Column("data", String(255)), constraint)
        dialect = mysql.dialect()
        self.assert_compile(
            schema.DropConstraint(constraint),
            "ALTER TABLE %s DROP CHECK `%s`" % (table_name, constraint_name),
            dialect=dialect,
        ) 

def test_auto_append_lowercase_table(self):
        from sqlalchemy import table, column

        t = table("t", column("a"))
        t2 = table("t2", column("a"))
        for c in (
            UniqueConstraint(t.c.a),
            CheckConstraint(t.c.a > 5),
            ForeignKeyConstraint([t.c.a], [t2.c.a]),
            PrimaryKeyConstraint(t.c.a),
            Index("foo", t.c.a),
        ):
            assert True 

def _is_deleted_column_constraint(constraint):
    # NOTE(boris-42): There is no other way to check is CheckConstraint
    #                 associated with deleted column.
    if not isinstance(constraint, CheckConstraint):
        return False
    sqltext = str(constraint.sqltext)
    # NOTE(zzzeek): SQLite never reflected CHECK contraints here
    # in any case until version 1.1.   Safe to assume that any CHECK
    # that's talking about the value of "deleted in (something)" is
    # the boolean constraint we're looking to get rid of.
    return bool(re.match(r".*deleted in \(.*\)", sqltext, re.I)) 

def test_change_name_retain_metadata(self):
        meta = MetaData()

        table = Table(
            "mytable",
            meta,
            Column("myid", Integer, primary_key=True),
            Column("name", String(40), nullable=True),
            Column(
                "description", String(30), CheckConstraint("description='hi'")
            ),
            UniqueConstraint("name"),
            schema="myschema",
        )

        table2 = table.to_metadata(table.metadata, name="newtable")
        table3 = table.to_metadata(
            table.metadata, schema="newschema", name="newtable"
        )

        assert table.metadata is table2.metadata
        assert table.metadata is table3.metadata
        eq_(
            (table.name, table2.name, table3.name),
            ("mytable", "newtable", "newtable"),
        )
        eq_(
            (table.key, table2.key, table3.key),
            ("myschema.mytable", "myschema.newtable", "newschema.newtable"),
        ) 

def __table_args__(self):
        return (
            pg.ExcludeConstraint(
                (sa.cast(self.the_survey_node_id, pg.TEXT), '='),
                ('bucket', '&&')
            ),
            sa.CheckConstraint('NOT isempty(bucket)'),
            sa.ForeignKeyConstraint(
                ['id', 'the_survey_node_id'],
                ['bucket.id', 'bucket.sub_survey_parent_survey_node_id'],
                onupdate='CASCADE', ondelete='CASCADE'
            ),
        )