From 7f25fcadafe1b227c27b223696ce3bcc88e47ec7 Mon Sep 17 00:00:00 2001 From: Sumit Khanna Date: Mon, 4 Oct 2021 11:14:38 -0400 Subject: [PATCH] Reflect table can reflect table with no columns The :meth:`_engine.Inspector.reflect_table` method now supports reflecting tables that do not have user defined columns. This allows :meth:`_schema.MetaData.reflect` to properly complete reflection on databases that contain such tables. Currently, only PostgreSQL is known to support such a construct among the common database backends. Fixes: #3247 Closes: #7118 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7118 Pull-request-sha: cb8ce01957e9a1453290a7c2728af8c60ef55fa1 Change-Id: I906cebe17d13554d79086b92f3e1e51ffba3e818 --- doc/build/changelog/unreleased_14/3247.rst | 9 +++ lib/sqlalchemy/engine/reflection.py | 5 +- lib/sqlalchemy/testing/requirements.py | 8 +++ .../testing/suite/test_reflection.py | 56 +++++++++++++++++++ test/requirements.py | 5 ++ 5 files changed, 81 insertions(+), 2 deletions(-) create mode 100644 doc/build/changelog/unreleased_14/3247.rst diff --git a/doc/build/changelog/unreleased_14/3247.rst b/doc/build/changelog/unreleased_14/3247.rst new file mode 100644 index 0000000000..4ddb457f7c --- /dev/null +++ b/doc/build/changelog/unreleased_14/3247.rst @@ -0,0 +1,9 @@ +.. change:: + :tags: engine, bug, postgresql + :tickets: 3247 + + The :meth:`_engine.Inspector.reflect_table` method now supports reflecting + tables that do not have user defined columns. This allows + :meth:`_schema.MetaData.reflect` to properly complete reflection on + databases that contain such tables. Currently, only PostgreSQL is known + to support such a construct among the common database backends. diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index 715781b7a3..113aa8ea06 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -784,8 +784,9 @@ class Inspector(object): cols_by_orig_name, ) - if not found_table: - raise exc.NoSuchTableError(table.name) + # NOTE: support tables/views with no columns + if not found_table and not self.has_table(table_name, schema): + raise exc.NoSuchTableError(table_name) self._reflect_pk( table_name, schema, table, cols_by_orig_name, exclude_columns diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py index a546e1febc..f8b5dd6062 100644 --- a/lib/sqlalchemy/testing/requirements.py +++ b/lib/sqlalchemy/testing/requirements.py @@ -589,8 +589,16 @@ class SuiteRequirements(Requirements): @property def table_reflection(self): + """target database has general support for table reflection""" return exclusions.open() + @property + def reflect_tables_no_columns(self): + """target database supports creation and reflection of tables with no + columns, or at least tables that seem to have no columns.""" + + return exclusions.closed() + @property def comment_reflection(self): return exclusions.closed() diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py index 916d74db31..88189c2d95 100644 --- a/lib/sqlalchemy/testing/suite/test_reflection.py +++ b/lib/sqlalchemy/testing/suite/test_reflection.py @@ -1063,6 +1063,61 @@ class ComponentReflectionTest(fixtures.TablesTest): assert id_.get("autoincrement", True) +class TableNoColumnsTest(fixtures.TestBase): + __requires__ = ("reflect_tables_no_columns",) + __backend__ = True + + @testing.fixture + def table_no_columns(self, connection, metadata): + Table("empty", metadata) + metadata.create_all(connection) + + @testing.fixture + def view_no_columns(self, connection, metadata): + Table("empty", metadata) + metadata.create_all(connection) + + Table("empty", metadata) + event.listen( + metadata, + "after_create", + DDL("CREATE VIEW empty_v AS SELECT * FROM empty"), + ) + + # for transactional DDL the transaction is rolled back before this + # drop statement is invoked + event.listen( + metadata, "before_drop", DDL("DROP VIEW IF EXISTS empty_v") + ) + metadata.create_all(connection) + + @testing.requires.reflect_tables_no_columns + def test_reflect_table_no_columns(self, connection, table_no_columns): + t2 = Table("empty", MetaData(), autoload_with=connection) + eq_(list(t2.c), []) + + @testing.requires.reflect_tables_no_columns + def test_get_columns_table_no_columns(self, connection, table_no_columns): + eq_(inspect(connection).get_columns("empty"), []) + + @testing.requires.reflect_tables_no_columns + def test_reflect_incl_table_no_columns(self, connection, table_no_columns): + m = MetaData() + m.reflect(connection) + assert set(m.tables).intersection(["empty"]) + + @testing.requires.views + @testing.requires.reflect_tables_no_columns + def test_reflect_view_no_columns(self, connection, view_no_columns): + t2 = Table("empty_v", MetaData(), autoload_with=connection) + eq_(list(t2.c), []) + + @testing.requires.views + @testing.requires.reflect_tables_no_columns + def test_get_columns_view_no_columns(self, connection, view_no_columns): + eq_(inspect(connection).get_columns("empty_v"), []) + + class ComponentReflectionTestExtra(fixtures.TestBase): __backend__ = True @@ -1641,6 +1696,7 @@ class CompositeKeyReflectionTest(fixtures.TablesTest): __all__ = ( "ComponentReflectionTest", "ComponentReflectionTestExtra", + "TableNoColumnsTest", "QuotedNameArgumentTest", "HasTableTest", "HasIndexTest", diff --git a/test/requirements.py b/test/requirements.py index 721bb8ba37..7efd6cbd50 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -1805,3 +1805,8 @@ class DefaultRequirements(SuiteRequirements): @property def autoincrement_without_sequence(self): return skip_if("oracle") + + @property + def reflect_tables_no_columns(self): + # so far sqlite, mariadb, mysql don't support this + return only_on(["postgresql"]) -- 2.47.3