Python sqlalchemy.testing.schema.Table() Examples

def test_not_an_executable(self):
        for obj in (
            Table("foo", MetaData(), Column("x", Integer)),
            Column("x", Integer),
            tsa.and_(True),
            tsa.and_(True).compile(),
            column("foo"),
            column("foo").compile(),
            MetaData(),
            Integer(),
            tsa.Index(name="foo"),
            tsa.UniqueConstraint("x"),
        ):
            with testing.db.connect() as conn:
                assert_raises_message(
                    tsa.exc.ObjectNotExecutableError,
                    "Not an executable object",
                    conn.execute,
                    obj,
                ) 

def test_create_drop_constructor_bound(self):
        for bind in (testing.db, testing.db.connect()):
            try:
                for args in (([bind], {}), ([], {"bind": bind})):
                    metadata = MetaData(*args[0], **args[1])
                    table = Table(
                        "test_table", metadata, Column("foo", Integer)
                    )
                    assert metadata.bind is table.bind is bind
                    metadata.create_all()
                    is_true(inspect(bind).has_table(table.name))
                    metadata.drop_all()
                    table.create()
                    table.drop()
                    is_false(inspect(bind).has_table(table.name))
            finally:
                if isinstance(bind, engine.Connection):
                    bind.close() 

def define_tables(cls, metadata):
        Table(
            "users",
            metadata,
            Column("user_id", INT, primary_key=True, autoincrement=False),
            Column("user_name", VARCHAR(20)),
            test_needs_acid=True,
        )
        Table(
            "users_autoinc",
            metadata,
            Column(
                "user_id", INT, primary_key=True, test_needs_autoincrement=True
            ),
            Column("user_name", VARCHAR(20)),
            test_needs_acid=True,
        ) 

def test_include_columns_indexes(self):
        m = self.metadata

        t1 = Table("t1", m, Column("a", sa.Integer), Column("b", sa.Integer))
        sa.Index("foobar", t1.c.a, t1.c.b)
        sa.Index("bat", t1.c.a)
        m.create_all()
        m2 = MetaData(testing.db)
        t2 = Table("t1", m2, autoload=True)
        assert len(t2.indexes) == 2

        m2 = MetaData(testing.db)
        t2 = Table("t1", m2, autoload=True, include_columns=["a"])
        assert len(t2.indexes) == 1

        m2 = MetaData(testing.db)
        t2 = Table("t1", m2, autoload=True, include_columns=["a", "b"])
        assert len(t2.indexes) == 2 

def test_sequence_not_duped(self):
        engine, buf = self._engine_fixture()
        metadata = MetaData()
        t = Table(
            "testtable",
            metadata,
            Column(
                "pk", Integer, Sequence("testtable_pk_seq"), primary_key=True,
            ),
        )

        t.create(engine)
        t.drop(engine)

        eq_(re.findall(r"CREATE (\w+)", buf.getvalue()), ["SEQUENCE", "TABLE"])

        eq_(re.findall(r"DROP (\w+)", buf.getvalue()), ["TABLE", "SEQUENCE"]) 

def test_unknown_types(self):
        """Test the handling of unknown types for the given dialect.

        sqlite is skipped because it has special rules for unknown types using
        'affinity types' - this feature is tested in that dialect's test spec.
        """
        meta = self.metadata
        t = Table("test", meta, Column("foo", sa.DateTime))

        ischema_names = testing.db.dialect.ischema_names
        t.create()
        testing.db.dialect.ischema_names = {}
        try:
            m2 = MetaData(testing.db)

            with testing.expect_warnings("Did not recognize type"):
                t3 = Table("test", m2, autoload_with=testing.db)
                is_(t3.c.foo.type.__class__, sa.types.NullType)

        finally:
            testing.db.dialect.ischema_names = ischema_names 

def test_basic_override(self):
        meta = self.metadata
        table = Table(
            "override_test",
            meta,
            Column("col1", sa.Integer, primary_key=True),
            Column("col2", sa.String(20)),
            Column("col3", sa.Numeric),
        )
        table.create()

        meta2 = MetaData(testing.db)
        table = Table(
            "override_test",
            meta2,
            Column("col2", sa.Unicode()),
            Column("col4", sa.String(30)),
            autoload=True,
        )

        self.assert_(isinstance(table.c.col1.type, sa.Integer))
        self.assert_(isinstance(table.c.col2.type, sa.Unicode))
        self.assert_(isinstance(table.c.col4.type, sa.String)) 

def test_pks_not_uniques(self):
        """test that primary key reflection not tripped up by unique
        indexes"""

        with testing.db.begin() as conn:
            conn.exec_driver_sql(
                """
                CREATE TABLE book (
                    id INTEGER NOT NULL,
                    title VARCHAR(100) NOT NULL,
                    series INTEGER,
                    series_id INTEGER,
                    UNIQUE(series, series_id),
                    PRIMARY KEY(id)
                )"""
            )

        book = Table("book", self.metadata, autoload_with=testing.db)
        assert book.primary_key.contains_column(book.c.id)
        assert not book.primary_key.contains_column(book.c.series)
        eq_(len(book.primary_key), 1) 

def test_composite_pks(self):
        """test reflection of a composite primary key"""

        with testing.db.begin() as conn:
            conn.exec_driver_sql(
                """
                CREATE TABLE book (
                    id INTEGER NOT NULL,
                    isbn VARCHAR(50) NOT NULL,
                    title VARCHAR(100) NOT NULL,
                    series INTEGER NOT NULL,
                    series_id INTEGER NOT NULL,
                    UNIQUE(series, series_id),
                    PRIMARY KEY(id, isbn)
                )"""
            )
        book = Table("book", self.metadata, autoload_with=testing.db)
        assert book.primary_key.contains_column(book.c.id)
        assert book.primary_key.contains_column(book.c.isbn)
        assert not book.primary_key.contains_column(book.c.series)
        eq_(len(book.primary_key), 2) 

def test_override_upgrade_pk_flag(self):
        meta = self.metadata
        table = Table(
            "override_test",
            meta,
            Column("col1", sa.Integer),
            Column("col2", sa.String(20)),
            Column("col3", sa.Numeric),
        )
        table.create()

        meta2 = MetaData(testing.db)
        table = Table(
            "override_test",
            meta2,
            Column("col1", sa.Integer, primary_key=True),
            autoload=True,
        )

        eq_(list(table.primary_key), [table.c.col1])
        eq_(table.c.col1.primary_key, True) 

def test_create_drop_err_metadata(self):
        metadata = MetaData()
        Table("test_table", metadata, Column("foo", Integer))
        for meth in [metadata.create_all, metadata.drop_all]:
            assert_raises_message(
                exc.UnboundExecutionError,
                "MetaData object is not bound to an Engine or Connection.",
                meth,
            ) 

def test_override_keys(self):
        """test that columns can be overridden with a 'key',
        and that ForeignKey targeting during reflection still works."""

        meta = self.metadata
        Table(
            "a",
            meta,
            Column("x", sa.Integer, primary_key=True),
            Column("z", sa.Integer),
            test_needs_fk=True,
        )
        Table(
            "b",
            meta,
            Column("y", sa.Integer, sa.ForeignKey("a.x")),
            test_needs_fk=True,
        )
        meta.create_all()
        m2 = MetaData(testing.db)
        a2 = Table(
            "a",
            m2,
            Column("x", sa.Integer, primary_key=True, key="x1"),
            autoload=True,
        )
        b2 = Table("b", m2, autoload=True)
        assert a2.join(b2).onclause.compare(a2.c.x1 == b2.c.y)
        assert b2.c.y.references(a2.c.x1) 

def test_autoload_replace_foreign_key_ispresent(self):
        """test autoload_replace=False with col plus FK mirroring
        DB-reflected FK skips the reflected FK and installs
        the in-python one only.

        """
        Table("a", self.metadata, Column("id", Integer, primary_key=True))
        Table(
            "b",
            self.metadata,
            Column("id", Integer, primary_key=True),
            Column("a_id", Integer, sa.ForeignKey("a.id")),
        )
        self.metadata.create_all()

        m2 = MetaData()
        b2 = Table("b", m2, Column("a_id", Integer, sa.ForeignKey("a.id")))
        a2 = Table("a", m2, autoload=True, autoload_with=testing.db)
        b2 = Table(
            "b",
            m2,
            extend_existing=True,
            autoload=True,
            autoload_with=testing.db,
            autoload_replace=False,
        )

        assert b2.c.id is not None
        assert b2.c.a_id.references(a2.c.id)
        eq_(len(b2.constraints), 2) 

def test_reflect_all_unreflectable_table(self):
        names = ["rt_%s" % name for name in ("a", "b", "c", "d", "e")]

        for name in names:
            Table(
                name, self.metadata, Column("id", sa.Integer, primary_key=True)
            )
        self.metadata.create_all()

        m = MetaData()

        inspector = sa.engine.reflection.Inspector
        reflect_table = inspector.reflect_table

        def patched(self, table, *arg, **kw):
            if table.name == "rt_c":
                raise sa.exc.UnreflectableTableError("Can't reflect rt_c")
            else:
                return reflect_table(self, table, *arg, **kw)

        with mock.patch.object(inspector, "reflect_table", patched):
            with expect_warnings("Skipping table rt_c: Can't reflect rt_c"):
                m.reflect(bind=testing.db)

            assert_raises_message(
                sa.exc.UnreflectableTableError,
                "Can't reflect rt_c",
                Table,
                "rt_c",
                m,
                autoload_with=testing.db,
            ) 

def test_nonreflected_fk_raises(self):
        """test that a NoReferencedColumnError is raised when reflecting
        a table with an FK to another table which has not included the target
        column in its reflection.

        """

        meta = self.metadata
        Table(
            "a",
            meta,
            Column("x", sa.Integer, primary_key=True),
            Column("z", sa.Integer),
            test_needs_fk=True,
        )
        Table(
            "b",
            meta,
            Column("y", sa.Integer, sa.ForeignKey("a.x")),
            test_needs_fk=True,
        )
        meta.create_all()
        m2 = MetaData(testing.db)
        a2 = Table("a", m2, include_columns=["z"], autoload=True)
        b2 = Table("b", m2, autoload=True)

        assert_raises(sa.exc.NoReferencedColumnError, a2.join, b2) 

def test_autoincrement_col(self):
        """test that 'autoincrement' is reflected according to sqla's policy.

        Don't mark this test as unsupported for any backend !

        """

        meta = self.metadata
        Table(
            "test",
            meta,
            Column("id", sa.Integer, primary_key=True),
            Column("data", sa.String(50)),
            mysql_engine="InnoDB",
        )
        Table(
            "test2",
            meta,
            Column(
                "id", sa.Integer, sa.ForeignKey("test.id"), primary_key=True
            ),
            Column("id2", sa.Integer, primary_key=True),
            Column("data", sa.String(50)),
            mysql_engine="InnoDB",
        )
        meta.create_all()
        m2 = MetaData(testing.db)
        t1a = Table("test", m2, autoload=True)
        assert t1a._autoincrement_column is t1a.c.id

        t2a = Table("test2", m2, autoload=True)
        assert t2a._autoincrement_column is None 

def test_autoload_replace_arg(self):
        Table("t", MetaData(), autoload_replace=False) 

def test_autoload_replace_foreign_key_removed(self):
        """test autoload_replace=False with col minus FK that's in the
        DB means the FK is skipped and doesn't get installed at all.

        """
        Table("a", self.metadata, Column("id", Integer, primary_key=True))
        Table(
            "b",
            self.metadata,
            Column("id", Integer, primary_key=True),
            Column("a_id", Integer, sa.ForeignKey("a.id")),
        )
        self.metadata.create_all()

        m2 = MetaData()
        b2 = Table("b", m2, Column("a_id", Integer))
        a2 = Table("a", m2, autoload=True, autoload_with=testing.db)
        b2 = Table(
            "b",
            m2,
            extend_existing=True,
            autoload=True,
            autoload_with=testing.db,
            autoload_replace=False,
        )

        assert b2.c.id is not None
        assert not b2.c.a_id.references(a2.c.id)
        eq_(len(b2.constraints), 1) 

def test_override_composite_fk(self):
        """Test double-remove of composite foreign key, when replaced."""

        metadata = self.metadata

        Table(
            "a",
            metadata,
            Column("x", sa.Integer, primary_key=True),
            Column("y", sa.Integer, primary_key=True),
        )

        Table(
            "b",
            metadata,
            Column("x", sa.Integer, primary_key=True),
            Column("y", sa.Integer, primary_key=True),
            sa.ForeignKeyConstraint(["x", "y"], ["a.x", "a.y"]),
        )

        metadata.create_all()

        meta2 = MetaData()

        c1 = Column("x", sa.Integer, primary_key=True)
        c2 = Column("y", sa.Integer, primary_key=True)
        f1 = sa.ForeignKeyConstraint(["x", "y"], ["a.x", "a.y"])
        b1 = Table(
            "b", meta2, c1, c2, f1, autoload=True, autoload_with=testing.db
        )

        assert b1.c.x is c1
        assert b1.c.y is c2
        assert f1 in b1.constraints
        assert len(b1.constraints) == 2 

def test_implicit_execution(self):
        metadata = MetaData()
        table = Table(
            "test_table",
            metadata,
            Column("foo", Integer),
            test_needs_acid=True,
        )
        conn = testing.db.connect()
        metadata.create_all(bind=conn)
        try:
            trans = conn.begin()
            metadata.bind = conn
            t = table.insert()
            assert t.bind is conn
            table.insert().execute(foo=5)
            table.insert().execute(foo=6)
            table.insert().execute(foo=7)
            trans.rollback()
            metadata.bind = None
            assert (
                conn.exec_driver_sql(
                    "select count(*) from test_table"
                ).scalar()
                == 0
            )
        finally:
            metadata.drop_all(bind=conn) 

def test_resolve_fks_false_table(self):
        meta = self.metadata
        Table(
            "t1",
            meta,
            Column("id", sa.Integer, primary_key=True),
            Column("t2id", sa.Integer, sa.ForeignKey("t2.id")),
            test_needs_fk=True,
        )
        Table(
            "t2",
            meta,
            Column("id", sa.Integer, primary_key=True),
            test_needs_fk=True,
        )
        meta.create_all()
        meta2 = MetaData()
        t1 = Table("t1", meta2, resolve_fks=False, autoload_with=testing.db)
        in_("t1", meta2.tables)
        not_in_("t2", meta2.tables)

        assert_raises(
            sa.exc.NoReferencedTableError,
            lambda: list(t1.c.t2id.foreign_keys)[0].column,
        )

        t2 = Table("t2", meta2, autoload_with=testing.db)

        # now it resolves
        is_true(t1.c.t2id.references(t2.c.id)) 

def test_create_drop_err_table(self):
        metadata = MetaData()
        table = Table("test_table", metadata, Column("foo", Integer))

        for meth in [table.create, table.drop]:
            assert_raises_message(
                exc.UnboundExecutionError,
                (
                    "Table object 'test_table' is not bound to an Engine or "
                    "Connection."
                ),
                meth,
            ) 

def test_create_drop_explicit(self):
        metadata = MetaData()
        table = Table("test_table", metadata, Column("foo", Integer))
        for bind in (testing.db, testing.db.connect()):
            for args in [([], {"bind": bind}), ([bind], {})]:
                metadata.create_all(*args[0], **args[1])
                is_true(inspect(bind).has_table(table.name))
                metadata.drop_all(*args[0], **args[1])
                table.create(*args[0], **args[1])
                table.drop(*args[0], **args[1])
                is_false(inspect(bind).has_table(table.name)) 

def test_cursor_execute(self):
        canary = []

        def tracker(name):
            def go(conn, cursor, statement, parameters, context, executemany):
                canary.append((statement, context))

            return go

        engine = engines.testing_engine()

        t = Table(
            "t",
            self.metadata,
            Column(
                "x",
                testing.db.dialect.sequence_default_column_type,
                Sequence("t_id_seq"),
                primary_key=True,
            ),
            implicit_returning=False,
        )
        self.metadata.create_all(engine)

        with engine.begin() as conn:
            event.listen(
                conn, "before_cursor_execute", tracker("cursor_execute")
            )
            conn.execute(t.insert())

        if testing.requires.supports_lastrowid.enabled:
            # new MariaDB 10.3 supports sequences + lastrowid; only
            # one statement
            assert "INSERT" in canary[0][0]
        else:
            # we see the sequence pre-executed in the first call
            assert "t_id_seq" in canary[0][0]
            assert "INSERT" in canary[1][0]
            # same context
            is_(canary[0][1], canary[1][1]) 

def test_ddl_hastable(self):

        map_ = {
            None: config.test_schema,
            "foo": config.test_schema,
            "bar": None,
        }

        metadata = MetaData()
        Table("t1", metadata, Column("x", Integer))
        Table("t2", metadata, Column("x", Integer), schema="foo")
        Table("t3", metadata, Column("x", Integer), schema="bar")

        with config.db.connect().execution_options(
            schema_translate_map=map_
        ) as conn:
            metadata.create_all(conn)

        insp = inspect(config.db)
        is_true(insp.has_table("t1", schema=config.test_schema))
        is_true(insp.has_table("t2", schema=config.test_schema))
        is_true(insp.has_table("t3", schema=None))

        with config.db.connect().execution_options(
            schema_translate_map=map_
        ) as conn:
            metadata.drop_all(conn)

        insp = inspect(config.db)
        is_false(insp.has_table("t1", schema=config.test_schema))
        is_false(insp.has_table("t2", schema=config.test_schema))
        is_false(insp.has_table("t3", schema=None)) 

def _fixture(self):
        metadata = self.metadata
        Table("t1", metadata, Column("x", Integer), schema=config.test_schema)
        Table("t2", metadata, Column("x", Integer), schema=config.test_schema)
        Table("t3", metadata, Column("x", Integer), schema=None)
        metadata.create_all() 

def test_create_table(self):
        map_ = {
            None: config.test_schema,
            "foo": config.test_schema,
            "bar": None,
        }

        metadata = MetaData()
        t1 = Table("t1", metadata, Column("x", Integer))
        t2 = Table("t2", metadata, Column("x", Integer), schema="foo")
        t3 = Table("t3", metadata, Column("x", Integer), schema="bar")

        with self.sql_execution_asserter(config.db) as asserter:
            with config.db.connect().execution_options(
                schema_translate_map=map_
            ) as conn:

                t1.create(conn)
                t2.create(conn)
                t3.create(conn)

                t3.drop(conn)
                t2.drop(conn)
                t1.drop(conn)

        asserter.assert_(
            CompiledSQL("CREATE TABLE [SCHEMA__none].t1 (x INTEGER)"),
            CompiledSQL("CREATE TABLE [SCHEMA_foo].t2 (x INTEGER)"),
            CompiledSQL("CREATE TABLE [SCHEMA_bar].t3 (x INTEGER)"),
            CompiledSQL("DROP TABLE [SCHEMA_bar].t3"),
            CompiledSQL("DROP TABLE [SCHEMA_foo].t2"),
            CompiledSQL("DROP TABLE [SCHEMA__none].t1"),
        ) 

def define_tables(cls, metadata):
        cls.table = Table(
            "exec_test",
            metadata,
            Column("a", Integer),
            Column("b", Integer),
            test_needs_acid=True,
        ) 

def setup_class(cls):
        global users, metadata
        metadata = MetaData(testing.db)
        users = Table(
            "users",
            metadata,
            Column(
                "user_id", INT, primary_key=True, test_needs_autoincrement=True
            ),
            Column("user_name", VARCHAR(20)),
            Column("extra_data", VARCHAR(20)),
        )
        metadata.create_all() 

def define_tables(cls, metadata):
        Table('test_table', metadata,
              Column('id', Integer, primary_key=True),
              Column('data', String(50))
              )