Python sqlalchemy.schema.PrimaryKeyConstraint() Examples

def test_pk_always_flips_nullable(self):
        m = MetaData()

        t1 = Table("t1", m, Column("x", Integer), PrimaryKeyConstraint("x"))

        t2 = Table("t2", m, Column("x", Integer, primary_key=True))

        eq_(list(t1.primary_key), [t1.c.x])

        eq_(list(t2.primary_key), [t2.c.x])

        assert t1.c.x.primary_key
        assert t2.c.x.primary_key

        assert not t2.c.x.nullable
        assert not t1.c.x.nullable 

def _mysql_drop_constraint(element, compiler, **kw):
    """Redefine SQLAlchemy's drop constraint to
    raise errors for invalid constraint type."""

    constraint = element.element
    if isinstance(constraint, (schema.ForeignKeyConstraint,
                               schema.PrimaryKeyConstraint,
                               schema.UniqueConstraint)
                  ):
        return compiler.visit_drop_constraint(element, **kw)
    elif isinstance(constraint, schema.CheckConstraint):
        # note that SQLAlchemy as of 1.2 does not yet support
        # DROP CONSTRAINT for MySQL/MariaDB, so we implement fully
        # here.
        return "ALTER TABLE %s DROP CONSTRAINT %s" % \
            (compiler.preparer.format_table(constraint.table),
             compiler.preparer.format_constraint(constraint))
    else:
        raise NotImplementedError(
            "No generic 'DROP CONSTRAINT' in MySQL - "
            "please specify constraint type") 

def drop_constraint(self, const):
        if not const.name:
            raise ValueError("Constraint must have a name")
        try:
            const = self.named_constraints.pop(const.name)
        except KeyError:
            if _is_type_bound(const):
                # type-bound constraints are only included in the new
                # table via their type object in any case, so ignore the
                # drop_constraint() that comes here via the
                # Operations.implementation_for(alter_column)
                return
            raise ValueError("No such constraint: '%s'" % const.name)
        else:
            if isinstance(const, PrimaryKeyConstraint):
                for col in const.columns:
                    self.columns[col.name].primary_key = False 

def add_column(operations, operation):
    table_name = operation.table_name
    column = operation.column
    schema = operation.schema

    t = operations.schema_obj.table(table_name, column, schema=schema)
    operations.impl.add_column(
        table_name,
        column,
        schema=schema
    )
    for constraint in t.constraints:
        if not isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            operations.impl.add_constraint(constraint)
    for index in t.indexes:
        operations.impl.create_index(index) 

def test_table_cls_attribute_return_none(self):
        from sqlalchemy.schema import Column, PrimaryKeyConstraint

        class AutoTable(object):
            @declared_attr
            def __tablename__(cls):
                return cls.__name__

            @classmethod
            def __table_cls__(cls, *arg, **kw):
                for obj in arg[1:]:
                    if (
                        isinstance(obj, Column) and obj.primary_key
                    ) or isinstance(obj, PrimaryKeyConstraint):
                        return Table(*arg, **kw)

                return None

        class Person(AutoTable, Base):
            id = Column(Integer, primary_key=True)

        class Employee(Person):
            employee_name = Column(String)

        is_(inspect(Employee).local_table, Person.__table__) 

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_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_composite_pk_constraint_maintains_order_explicit(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column("a", Integer),
            Column("b", Integer, autoincrement=True),
            schema.PrimaryKeyConstraint("a", "b"),
        )
        self.assert_compile(
            schema.CreateTable(t),
            "CREATE TABLE t ("
            "a INTEGER NOT NULL, "
            "b INTEGER NOT NULL, "
            "PRIMARY KEY (a, b))",
        ) 

def add_column(operations, operation):
    table_name = operation.table_name
    column = operation.column
    schema = operation.schema

    t = operations.schema_obj.table(table_name, column, schema=schema)
    operations.impl.add_column(
        table_name,
        column,
        schema=schema
    )
    for constraint in t.constraints:
        if not isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            operations.impl.add_constraint(constraint)
    for index in t.indexes:
        operations.impl.create_index(index) 

def generic_constraint(self, name, table_name, type_, schema=None, **kw):
        t = self.table(table_name, schema=schema)
        types = {
            'foreignkey': lambda name: sa_schema.ForeignKeyConstraint(
                [], [], name=name),
            'primary': sa_schema.PrimaryKeyConstraint,
            'unique': sa_schema.UniqueConstraint,
            'check': lambda name: sa_schema.CheckConstraint("", name=name),
            None: sa_schema.Constraint
        }
        try:
            const = types[type_]
        except KeyError:
            raise TypeError("'type' can be one of %s" %
                            ", ".join(sorted(repr(x) for x in types)))
        else:
            const = const(name=name)
            t.append_constraint(const)
            return const 

def add_column(operations, operation):
    table_name = operation.table_name
    column = operation.column
    schema = operation.schema
    kw = operation.kw

    t = operations.schema_obj.table(table_name, column, schema=schema)
    operations.impl.add_column(table_name, column, schema=schema, **kw)

    for constraint in t.constraints:
        if not isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            operations.impl.add_constraint(constraint)
    for index in t.indexes:
        operations.impl.create_index(index)

    with_comment = (
        sqla_compat._dialect_supports_comments(operations.impl.dialect)
        and not operations.impl.dialect.inline_comments
    )
    comment = sqla_compat._comment_attribute(column)
    if comment and with_comment:
        operations.impl.create_column_comment(column) 

def drop_constraint(self, const):
        if not const.name:
            raise ValueError("Constraint must have a name")
        try:
            const = self.named_constraints.pop(const.name)
        except KeyError:
            if _is_type_bound(const):
                # type-bound constraints are only included in the new
                # table via their type object in any case, so ignore the
                # drop_constraint() that comes here via the
                # Operations.implementation_for(alter_column)
                return
            raise ValueError("No such constraint: '%s'" % const.name)
        else:
            if isinstance(const, PrimaryKeyConstraint):
                for col in const.columns:
                    self.columns[col.name].primary_key = False 

def test_single_string_illegal_autoinc(self):
        t = Table("t", MetaData(), Column("a", String, autoincrement=True))
        pk = PrimaryKeyConstraint(t.c.a)
        t.append_constraint(pk)

        assert_raises_message(
            exc.ArgumentError,
            "Column type VARCHAR on column 't.a'",
            lambda: pk._autoincrement_column,
        ) 

def primary_key_constraint(self, name, table_name, cols, schema=None):
        m = self.metadata()
        columns = [sa_schema.Column(n, NULLTYPE) for n in cols]
        t = sa_schema.Table(
            table_name, m,
            *columns,
            schema=schema)
        p = sa_schema.PrimaryKeyConstraint(
            *[t.c[n] for n in cols], name=name)
        t.append_constraint(p)
        return p 

def add_constraint(self, const):
        if not const.name:
            raise ValueError("Constraint must have a name")
        if isinstance(const, sql_schema.PrimaryKeyConstraint):
            if self.table.primary_key in self.unnamed_constraints:
                self.unnamed_constraints.remove(self.table.primary_key)

        self.named_constraints[const.name] = const 

def add_constraint(self, const):
        if not const.name:
            raise ValueError("Constraint must have a name")
        if isinstance(const, sql_schema.PrimaryKeyConstraint):
            if self.table.primary_key in self.unnamed_constraints:
                self.unnamed_constraints.remove(self.table.primary_key)

        self.named_constraints[const.name] = const 

def primary_key_constraint(self, name, table_name, cols, schema=None):
        m = self.metadata()
        columns = [sa_schema.Column(n, NULLTYPE) for n in cols]
        t = sa_schema.Table(
            table_name, m,
            *columns,
            schema=schema)
        p = sa_schema.PrimaryKeyConstraint(
            *[t.c[n] for n in cols], name=name)
        t.append_constraint(p)
        return p 

def test_dialect_options_are_copied(self):
        with self._fixture():
            t1 = Table(
                "t",
                MetaData(),
                Column(
                    "foo",
                    Integer,
                    copydialectoptionstest_some_column_arg="a2",
                ),
                Column("bar", Integer),
                PrimaryKeyConstraint(
                    "foo", copydialectoptionstest_some_pk_arg="a3"
                ),
                UniqueConstraint(
                    "bar", copydialectoptionstest_some_uq_arg="a5"
                ),
                copydialectoptionstest_some_table_arg="a1",
            )
            Index(
                "idx", t1.c.foo, copydialectoptionstest_some_index_arg="a4",
            )

            self.check_dialect_options_(t1)

            m2 = MetaData()
            t2 = t1.to_metadata(m2)  # make a copy
            self.check_dialect_options_(t2) 

def test_add_new_arguments_participating_no_existing(self):
        with self._fixture():
            PrimaryKeyConstraint.argument_for("participating", "xyzqpr", False)

            pk = PrimaryKeyConstraint("a", "b", "c", participating_xyzqpr=True)

            eq_(pk.kwargs["participating_xyzqpr"], True)

            pk = PrimaryKeyConstraint("a", "b", "c")
            eq_(pk.dialect_options["participating"]["xyzqpr"], False) 

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 test_implicit_autoinc_but_fks(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table("t2", MetaData(), Column("a", Integer, ForeignKey("t1.id")))
        pk = PrimaryKeyConstraint(t2.c.a)
        t2.append_constraint(pk)
        is_(pk._autoincrement_column, None) 

def test_single_integer_server_default(self):
        # new as of 1.1; now that we have three states for autoincrement,
        # if the user puts autoincrement=True with a server_default, trust
        # them on it
        t = Table(
            "t",
            MetaData(),
            Column(
                "a", Integer, autoincrement=True, server_default=func.magic()
            ),
        )
        pk = PrimaryKeyConstraint(t.c.a)
        t.append_constraint(pk)

        is_(pk._autoincrement_column, t.c.a) 

def test_single_integer_default(self):
        t = Table(
            "t",
            MetaData(),
            Column("a", Integer, autoincrement=True, default=lambda: 1),
        )
        pk = PrimaryKeyConstraint(t.c.a)
        t.append_constraint(pk)

        is_(pk._autoincrement_column, t.c.a) 

def test_pk_cols_sets_flags(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column("x", Integer),
            Column("y", Integer),
            Column("z", Integer),
            PrimaryKeyConstraint("x", "y"),
        )
        eq_(t.c.x.primary_key, True)
        eq_(t.c.y.primary_key, True)
        eq_(t.c.z.primary_key, False) 

def primary_key_constraint(self, name, table_name, cols, schema=None):
        m = self.metadata()
        columns = [sa_schema.Column(n, NULLTYPE) for n in cols]
        t = sa_schema.Table(table_name, m, *columns, schema=schema)
        p = sa_schema.PrimaryKeyConstraint(*[t.c[n] for n in cols], name=name)
        t.append_constraint(p)
        return p 

def _mysql_drop_constraint(element, compiler, **kw):
    """Redefine SQLAlchemy's drop constraint to
    raise errors for invalid constraint type."""

    constraint = element.element
    if isinstance(
        constraint,
        (
            schema.ForeignKeyConstraint,
            schema.PrimaryKeyConstraint,
            schema.UniqueConstraint,
        ),
    ):
        return compiler.visit_drop_constraint(element, **kw)
    elif isinstance(constraint, schema.CheckConstraint):
        # note that SQLAlchemy as of 1.2 does not yet support
        # DROP CONSTRAINT for MySQL/MariaDB, so we implement fully
        # here.
        if _is_mariadb(compiler.dialect):
            return "ALTER TABLE %s DROP CONSTRAINT %s" % (
                compiler.preparer.format_table(constraint.table),
                compiler.preparer.format_constraint(constraint),
            )
        else:
            return "ALTER TABLE %s DROP CHECK %s" % (
                compiler.preparer.format_table(constraint.table),
                compiler.preparer.format_constraint(constraint),
            )
    else:
        raise NotImplementedError(
            "No generic 'DROP CONSTRAINT' in MySQL - "
            "please specify constraint type"
        ) 

def visit_column(self, column):
        """Firebird supports 'DROP col' instead of 'DROP COLUMN col' syntax

        Drop primary key and unique constraints if dropped column is referencing it."""
        if column.primary_key:
            if column.table.primary_key.columns.contains_column(column):
                column.table.primary_key.drop()
                # TODO: recreate primary key if it references more than this column

        for index in column.table.indexes:
            # "column in index.columns" causes problems as all
            # column objects compare equal and return a SQL expression
            if column.name in [col.name for col in index.columns]:
                index.drop()
                # TODO: recreate index if it references more than this column

        for cons in column.table.constraints:
            if isinstance(cons,PrimaryKeyConstraint):
                # will be deleted only when the column its on
                # is deleted!
                continue

            should_drop = column.name in cons.columns
            if should_drop:
                self.start_alter_table(column)
                self.append("DROP CONSTRAINT ")
                self.append(self.preparer.format_constraint(cons))
                self.execute()
            # TODO: recreate unique constraint if it refenrences more than this column

        self.start_alter_table(column)
        self.append('DROP %s' % self.preparer.format_column(column))
        self.execute() 

def __init__(self, *cols, **kwargs):
        colnames, table = self._normalize_columns(cols)
        table = kwargs.pop('table', table)
        super(PrimaryKeyConstraint, self).__init__(*colnames, **kwargs)
        if table is not None:
            self._set_parent(table) 

def add_constraint(self, const):
        if not const.name:
            raise ValueError("Constraint must have a name")
        if isinstance(const, sql_schema.PrimaryKeyConstraint):
            if self.table.primary_key in self.unnamed_constraints:
                self.unnamed_constraints.remove(self.table.primary_key)

        self.named_constraints[const.name] = const 

def test_primarykey_constraint_info(self):
        pkc = PrimaryKeyConstraint("a", name="x")
        eq_(pkc.info, {})

        pkc = PrimaryKeyConstraint("a", name="x", info={"foo": "bar"})
        eq_(pkc.info, {"foo": "bar"})