Hey there, I am trying to setup a multi-schema database, where each schema has it's own metadata and tables, but ForeignKey constraints are allowed to cross schemas. This usually works fine until I start introducing something like alembic revision autogeneration, which uses the sorted_tables feature of the metadata, in which case I get errors because sqlalchemy cannot find the table referenced by the foreign key. I've attached a simple script that showcases my findings, along with the stacktrace below. I understand that a simple fix would be to use actual model object instead of a string to reference the foreign column, but ideally would like to continue using strings in case we ever decide to break up the code into different projects pointing to the same database. Any thoughts?
Traceback (most recent call last): File "simple.py", line 21, in <module> print(metadata_two.sorted_tables) File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3842, in sorted_tables return ddl.sort_tables(sorted(self.tables.values(), key=lambda t: t.key)) File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 1028, in sort_tables tables, filter_fn=_skip_fn, extra_dependencies=extra_dependencies) File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 1095, in sort_tables_and_constraints dependent_on = fkc.referred_table File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 3002, in referred_table return self.elements[0].column.table File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 767, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) File "/Users/ashu/Documents/server/env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 1891, in column tablekey) sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'table_two.one_id' could not find table 'one.table_one' with which to generate a foreign key to target column 'id' Thanks! Ashu -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
import sqlalchemy as sa metadata_one = sa.MetaData(schema="one") table_one = sa.Table( "table_one", metadata_one, sa.Column("id", sa.Text, primary_key=True), schema="table_one", ) metadata_two = sa.MetaData(schema="two") table_two = sa.Table( "table_two", metadata_two, sa.Column("id", sa.Text, primary_key=True), sa.Column("one_id", sa.Text, sa.ForeignKey("one.table_one.id"), nullable=False), schema="two", ) if __name__ == "__main__": print(metadata_two.sorted_tables)