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 [email protected].
To post to this group, send email to [email protected].
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)
