Python sqlalchemy.schema.ForeignKeyConstraint() Examples

The following are 30 code examples of sqlalchemy.schema.ForeignKeyConstraint(). You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may also want to check out all available functions/classes of the module sqlalchemy.schema , or try the search function .
Example #1
Source File: base.py    From android_universal with MIT License 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #2
Source File: base.py    From Fluid-Designer with GNU General Public License v3.0 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #3
Source File: schemaobj.py    From alembic with MIT License 6 votes vote down vote up 
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
Example #4
Source File: compare.py    From android_universal with MIT License 6 votes vote down vote up 
def _make_foreign_key(params, conn_table):
    tname = params['referred_table']
    if params['referred_schema']:
        tname = "%s.%s" % (params['referred_schema'], tname)

    options = params.get('options', {})

    const = sa_schema.ForeignKeyConstraint(
        [conn_table.c[cname] for cname in params['constrained_columns']],
        ["%s.%s" % (tname, n) for n in params['referred_columns']],
        onupdate=options.get('onupdate'),
        ondelete=options.get('ondelete'),
        deferrable=options.get('deferrable'),
        initially=options.get('initially'),
        name=params['name']
    )
    # needed by 0.7
    conn_table.append_constraint(const)
    return const
Example #5
Source File: compare.py    From jbox with MIT License 6 votes vote down vote up 
def _make_foreign_key(params, conn_table):
    tname = params['referred_table']
    if params['referred_schema']:
        tname = "%s.%s" % (params['referred_schema'], tname)

    options = params.get('options', {})

    const = sa_schema.ForeignKeyConstraint(
        [conn_table.c[cname] for cname in params['constrained_columns']],
        ["%s.%s" % (tname, n) for n in params['referred_columns']],
        onupdate=options.get('onupdate'),
        ondelete=options.get('ondelete'),
        deferrable=options.get('deferrable'),
        initially=options.get('initially'),
        name=params['name']
    )
    # needed by 0.7
    conn_table.append_constraint(const)
    return const
Example #6
Source File: base.py    From pyRevit with GNU General Public License v3.0 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #7
Source File: base.py    From planespotter with MIT License 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #8
Source File: base.py    From stdm with GNU General Public License v2.0 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #9
Source File: mysql.py    From jbox with MIT License 6 votes vote down vote up 
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):
        raise NotImplementedError(
            "MySQL does not support CHECK constraints.")
    else:
        raise NotImplementedError(
            "No generic 'DROP CONSTRAINT' in MySQL - "
            "please specify constraint type")
Example #10
Source File: schemaobj.py    From jbox with MIT License 6 votes vote down vote up 
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
Example #11
Source File: base.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.CheckConstraint):
            if self.dialect._is_mariadb:
                qual = "CONSTRAINT "
            else:
                qual = "CHECK "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % (
            self.preparer.format_table(constraint.table),
            qual,
            const,
        )
Example #12
Source File: test_compiler.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_fk_illegal_sql_phrases(self):
        a = Table("a", MetaData(), Column("q", Integer))
        b = Table("b", MetaData(), Column("p", Integer))

        for kw in ("onupdate", "ondelete", "initially"):
            for phrase in (
                "NOT SQL",
                "INITALLY NOT SQL",
                "FOO RESTRICT",
                "CASCADE WRONG",
                "SET  NULL",
            ):
                const = schema.AddConstraint(
                    schema.ForeignKeyConstraint(
                        [a.c.q], [b.c.p], **{kw: phrase}
                    )
                )
                assert_raises_message(
                    exc.CompileError,
                    r"Unexpected SQL phrase: '%s'" % phrase,
                    const.compile,
                )
Example #13
Source File: mysql.py    From android_universal with MIT License 6 votes vote down vote up 
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")
Example #14
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_foreign_key_constraints_collection(self):
        metadata = MetaData()
        t1 = Table("foo", metadata, Column("a", Integer))
        eq_(t1.foreign_key_constraints, set())

        fk1 = ForeignKey("q.id")
        fk2 = ForeignKey("j.id")
        fk3 = ForeignKeyConstraint(["b", "c"], ["r.x", "r.y"])

        t1.append_column(Column("b", Integer, fk1))
        eq_(t1.foreign_key_constraints, set([fk1.constraint]))

        t1.append_column(Column("c", Integer, fk2))
        eq_(t1.foreign_key_constraints, set([fk1.constraint, fk2.constraint]))

        t1.append_constraint(fk3)
        eq_(
            t1.foreign_key_constraints,
            set([fk1.constraint, fk2.constraint, fk3]),
        )
Example #15
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_invalid_composite_fk_check_columns_notattached(self):
        m = MetaData()
        x = Column("x", Integer)
        y = Column("y", Integer)

        # no error is raised for this one right now.
        # which is a minor bug.
        Table(
            "t1",
            m,
            Column("x", Integer),
            Column("y", Integer),
            ForeignKeyConstraint(["x", "y"], [x, y]),
        )

        Table("t2", m, x)
        Table("t3", m, y)
Example #16
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_constraint_copied_to_proxy_ok(self):
        m = MetaData()
        Table("t1", m, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            m,
            Column("id", Integer, ForeignKey("t1.id"), primary_key=True),
        )

        s = tsa.select([t2]).subquery()
        t2fk = list(t2.c.id.foreign_keys)[0]
        sfk = list(s.c.id.foreign_keys)[0]

        # the two FKs share the ForeignKeyConstraint
        is_(t2fk.constraint, sfk.constraint)

        # but the ForeignKeyConstraint isn't
        # aware of the select's FK
        eq_(t2fk.constraint.elements, [t2fk])
Example #17
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_type_propagate_composite_fk_string(self):
        metadata = MetaData()
        Table(
            "a",
            metadata,
            Column("key1", Integer, primary_key=True),
            Column("key2", String(40), primary_key=True),
        )

        b = Table(
            "b",
            metadata,
            Column("a_key1", None),
            Column("a_key2", None),
            Column("id", Integer, primary_key=True),
            ForeignKeyConstraint(["a_key1", "a_key2"], ["a.key1", "a.key2"]),
        )

        assert isinstance(b.c.a_key1.type, Integer)
        assert isinstance(b.c.a_key2.type, String)
Example #18
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_type_propagate_composite_fk_col(self):
        metadata = MetaData()
        a = Table(
            "a",
            metadata,
            Column("key1", Integer, primary_key=True),
            Column("key2", String(40), primary_key=True),
        )

        b = Table(
            "b",
            metadata,
            Column("a_key1", None),
            Column("a_key2", None),
            Column("id", Integer, primary_key=True),
            ForeignKeyConstraint(["a_key1", "a_key2"], [a.c.key1, a.c.key2]),
        )

        assert isinstance(b.c.a_key1.type, Integer)
        assert isinstance(b.c.a_key2.type, String)
Example #19
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
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
Example #20
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
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
Example #21
Source File: schemaobj.py    From android_universal with MIT License 6 votes vote down vote up 
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
Example #22
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_fk_name_schema(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0_name)s_"
                "%(referred_table_name)s_%(referred_column_0_name)s"
            },
            table_schema="foo",
        )
        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("user_id", Integer),
            Column("user_version_id", Integer),
        )
        fk = ForeignKeyConstraint(
            ["user_id", "user_version_id"], ["foo.user.id", "foo.user.version"]
        )
        a1.append_constraint(fk)
        eq_(fk.name, "fk_address_user_id_user_id")
Example #23
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_fk_attrs(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0_name)s_"
                "%(referred_table_name)s_%(referred_column_0_name)s"
            }
        )
        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("user_id", Integer),
            Column("user_version_id", Integer),
        )
        fk = ForeignKeyConstraint(
            ["user_id", "user_version_id"], ["user.id", "user.version"]
        )
        a1.append_constraint(fk)
        eq_(fk.name, "fk_address_user_id_user_id")
Example #24
Source File: test_metadata.py    From sqlalchemy with MIT License 6 votes vote down vote up 
def test_custom(self):
        def key_hash(const, table):
            return "HASH_%s" % table.name

        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(key_hash)s",
                "key_hash": key_hash,
            }
        )
        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("user_id", Integer),
            Column("user_version_id", Integer),
        )
        fk = ForeignKeyConstraint(
            ["user_id", "user_version_id"], ["user.id", "user.version"]
        )
        a1.append_constraint(fk)
        eq_(fk.name, "fk_address_HASH_address")
Example #25
Source File: base.py    From jarvis with GNU General Public License v2.0 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #26
Source File: base.py    From moviegrabber with GNU General Public License v3.0 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
                    (self.preparer.format_table(constraint.table),
                    qual, const)
Example #27
Source File: base.py    From jbox with MIT License 6 votes vote down vote up 
def visit_drop_constraint(self, drop):
        constraint = drop.element
        if isinstance(constraint, sa_schema.ForeignKeyConstraint):
            qual = "FOREIGN KEY "
            const = self.preparer.format_constraint(constraint)
        elif isinstance(constraint, sa_schema.PrimaryKeyConstraint):
            qual = "PRIMARY KEY "
            const = ""
        elif isinstance(constraint, sa_schema.UniqueConstraint):
            qual = "INDEX "
            const = self.preparer.format_constraint(constraint)
        else:
            qual = ""
            const = self.preparer.format_constraint(constraint)
        return "ALTER TABLE %s DROP %s%s" % \
            (self.preparer.format_table(constraint.table),
             qual, const)
Example #28
Source File: common.py    From AnyBlok with Mozilla Public License 2.0 5 votes vote down vote up 
def all_column_name(constraint, table):
    """Define the convention to merge the column keys

    :param constraint:
    :return:
    """
    if isinstance(constraint, ForeignKeyConstraint):
        return '_'.join(constraint.column_keys)
    else:
        return '_'.join(constraint.columns.keys())
Example #29
Source File: test_metadata.py    From sqlalchemy with MIT License 5 votes vote down vote up 
def test_fk_allcols_merged_name(self):
        u1 = self._fixture(
            naming_convention={
                "fk": "fk_%(table_name)s_%(column_0N_name)s_"
                "%(referred_table_name)s_%(referred_column_0N_name)s"
            }
        )

        m1 = u1.metadata
        a1 = Table(
            "address",
            m1,
            Column("id", Integer, primary_key=True),
            Column("UserData", String(30), key="user_data"),
            Column("UserData2", String(30), key="user_data2"),
            Column("UserData3", String(30), key="user_data3"),
        )
        fk = ForeignKeyConstraint(
            ["user_data", "user_data2", "user_data3"],
            ["user.data", "user.data2", "user.data3"],
        )
        a1.append_constraint(fk)
        self.assert_compile(
            schema.AddConstraint(fk),
            "ALTER TABLE address ADD CONSTRAINT "
            '"fk_address_UserDataUserData2UserData3_user_dataData2Data3" '
            'FOREIGN KEY("UserData", "UserData2", "UserData3") '
            'REFERENCES "user" (data, "Data2", "Data3")',
            dialect=default.DefaultDialect(),
        )
Example #30
Source File: sqla_compat.py    From android_universal with MIT License 5 votes vote down vote up 
def _table_for_constraint(constraint):
    if isinstance(constraint, ForeignKeyConstraint):
        return constraint.parent
    else:
        return constraint.table