Err why would I do #3? This is a just a simple reproduction of my actual problem, in which the tables are obviously more fleshed out, and I would actually want to enforce that the value referenced in the foreign key column actually existed in the foreign table. Am I missing something?
I'm fine with doing #1 or #2 but just wanted to make sure that was the only way. On Friday, April 13, 2018 at 2:51:14 PM UTC-7, Mike Bayer wrote: > > On Fri, Apr 13, 2018 at 5:11 PM, Ashu Goel <soxf...@gmail.com > <javascript:>> wrote: > > 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? > > > here are your choices: > > 1. put the two tables on the same MetaData object > > 2. use the column object in the ForeignKey, not the string > > 3. don't use ForeignKey. > > Looks like you want to use #3 because the ForeignKey construct here is > completely useless without it being able to find the other Table, it's > not buying you anything. > > > > > > > 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+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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.
